ASP Classic and SQL Server 2008 giving strange response

Question

Hi guys.

Started getting this error it seems since we upgraded to SQL Server 2008.

When inserting into the db and then returning the identity i get a 'Item cannot be found in the collection corresponding to the requested name or ordinal' error.

Here is the code:

SQL = "INSERT INTO PageFeatures(nPageFeatureFlagId,nPageFeatureFeatureId,nPageFeaturePageId) VALUES(" & nTemplateFlagId & "," & nFeatureId & "," & nPageId & "); SELECT SCOPE_IDENTITY() As nPageFeatureId;"
objrs.open SQL,objConn,1,1
 nPageFeatureId = objrs("nPageFeatureId")
objrs.close

The insert is working as the record is in the db. It's not returning the id for some reason. It works fine and returns the id when running in SSMS. But ASP can't see the returned id so some reason.

Any help would be greatly appreciated!

Answer 1

You may have to try moving the recordset on? e.g.

SQL = "INSERT INTO PageFeatures(nPageFeatureFlagId,nPageFeatureFeatureId,nPageFeaturePageId) VALUES(" & nTemplateFlagId & "," & nFeatureId & "," & nPageId & "); SELECT SCOPE_IDENTITY() As nPageFeatureId;"
objrs.open SQL,objConn,1,1
    objrs.NextRecordset
    nPageFeatureId = objrs("nPageFeatureId")
objrs.close

Raj's comment about SQL injection is still relevant. :)

EDIT: Elaboration is that there are two recordsets in play here. The first is an empty one created by the insert statement, the second one is caused by everything after the semi-colon ;. This is the field you want. So initially, your objrs is attached to the first of its collection of recordsets (not rows in a recordset), you move to the NextRecordset - and you can then deal with that how you please.

Answer 2

Sorted:

SQL = "INSERT INTO PageFeatures(nPageFeatureFlagId,nPageFeatureFeatureId,nPageFeaturePageId) VALUES(" & nTemplateFlagId & "," & nFeatureId & "," & nPageId & ");"
objConn.execute(SQL)

Set oReturnValueRS = objConn.Execute("SELECT SCOPE_IDENTITY()")
nPageFeatureId = oReturnValueRS(0).Value
oReturnValueRS.close : set oReturnValueRS = nothing