ADODB / @@Identity problem.

[MartinSmith]

The following code works fine on my PC (SQL 7.0) but errors when running on another Machine (with SQL 2000) installed.

The tables should be the same on both machines so what else may be causing this error?

Set Conn = Server.CreateObject("ADODB.Connection")
Query = "DECLARE @Email varchar(255) SELECT @Email = (SELECT uemail FROM aamem WHERE ukey = " & IsContactedBy & ") INSERT INTO AAMem (UTitle, UFName, ULastC, UEmail, UCtry, URandomID, UPassword, Visits, UFNameEng, ULastCEng, IsContactedBy) VALUES ('" & UTitle & "','" & UFName & "','" & ULastC & "',@Email,'" & UCtry & "','" & URandomID & "','" & UPassword & "','" & Visits & "','" & UFNameEng & "','" & ULastCEng & "','" & IsContactedBy & "');select @@identity"
Conn.Open(Application("ConnectionString"))
Set RS = Server.CreateObject("ADODB.RecordSet")
Set RS = Conn.Execute (Query).NextRecordset
JustInsertedID = RS(0)

NB the error is "Item cannot be found in the collection corresponding to
the requested name or ordinal"

on the

JustInsertedID = RS(0) line

[Bonker Gudd]

Hi Martin,
Does it help if you do "select @@identity as My_Identity" ?

[MartinSmith]

Thanks, I'll give that a shot.

[Lord_Rat]

Martin.

It seems you have to run the query WITHOUT ASSIGNING IT TO A RECORDSET OBJECT and then run the SELECT @@IDENTITY query after it (not in the same step) similar to:

objConn.Execute "UPDATE something SET somethingelse . ...."

Set objRS = objConn.Execute("SELECT @@IDENTITY")

if not objRS.eof then response.write (or debug.print) objRS.Fields(0)

[vb_dba]

You could also try setting the "Set NoCount" property of the query:

Set Conn = Server.CreateObject("ADODB.Connection")
Query = "[b]Set NoCount On[/b] DECLARE @Email varchar(255) SELECT @Email = (SELECT uemail FROM aamem WHERE ukey = " & IsContactedBy & ") INSERT INTO AAMem (UTitle, UFName, ULastC, UEmail, UCtry, URandomID, UPassword, Visits, UFNameEng, ULastCEng, IsContactedBy) VALUES ('" & UTitle & "','" & UFName & "','" & ULastC & "',@Email,'" & UCtry & "','" & URandomID & "','" & UPassword & "','" & Visits & "','" & UFNameEng & "','" & ULastCEng & "','" & IsContactedBy & "') select @@identity"
Conn.Open(Application("ConnectionString"))
Set RS = Server.CreateObject("ADODB.RecordSet")
Set RS = Conn.Execute (Query)
JustInsertedID = RS(0)

I also removed the semicolon before the select @@identity and the .NextRecordset.

You can try and see if this works.

[MartinSmith]

Thanks all of you, I ended up going with

Query = "SET NOCOUNT ON;DECLARE @Email varchar(255) SELECT @Email = (SELECT uemail FROM aamem WHERE ukey = " & IsContactedBy & ") INSERT INTO AAMem (UTitle, UFName, ULastC, UEmail, UCtry, URandomID, UPassword, Visits, UFNameEng, ULastCEng, IsContactedBy) VALUES ('" & UTitle & "','" & UFName & "','" & ULastC & "',@Email,'" & UCtry & "','" & URandomID & "','" & UPassword & "','" & Visits & "','" & UFNameEng & "','" & ULastCEng & "','" & IsContactedBy & "');SELECT @@IDENTITY AS NewID;"
 
Conn.Open(Application("ConnectionString")) 
Set RS = Server.CreateObject("ADODB.RecordSet") 
Set RS = Conn.Execute (Query) 
JustInsertedID = RS("NewID")

and that seems to have done the trick. The bizarre thing is however that I am using my original technique in 2 other pages throughout the project (following bog standard inserts) and they seem to be performing as expected. I don't have access to the other server to do any testing on it (I am sending my customer the pages and they are putting them up on there and feeding back any problems). But is it possible that in SQL 2000 For some reason the DECLARE @Email varchar(255) may implicitly set no count on or in some other way affect the RecordSet(s) returned?