How to get auto increment field from ADO 1

[cmlimnet]

Hi,
I am created a table with auto incremented field as a primary key in Ms Access table. I am using ASP to insert a record into the table. I wonder how can I get back the auto incremented field from ADO. My code is showed as below:

Set cnn = Server.CreateObject("ADODB.Connection&quot
cnn.Open CONSTR
strsql = "Insert into table1 (firstname, lastname) values ('xxxxx', 'yyyyy')"

cnn.Execute strtemp, , adCmdText + adExecuteNoRecordscnn.Close
set cnn = nothing

 

[zyrag]

i can't think of another way except to Requery the database,

SELECT AutoNum_Field WHERE LastName=... AND FirstName=...

OR open a recordset and do a MoveLast, but may not be true if another user has already inserted another new record

 

[johnwm]

In Jet4 use the SQL @@Identity construct

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[cmlimnet]

I have no idea about the Jet4/SQL @@Identity construct.What is that? Any samples or tutorial link?

 

[cmlimnet]

Finally, I managed to get the answer

Set objConn = Server.CreateObject("ADODB.Connection&quot
objConn.Open strConnect

strSQL = "INSERT INTO table1 (Table1Text) VALUES ('" & i & "')"
objConn.Execute strSQL

strSQL = "SELECT @@Identity"
Set objRS = objConn.Execute(strSQL)

intID = objRS.Fields.Item(0).Value

objConn.Close
Set objConn = Nothing

 

[johnwm]

Isn't it great when a pointer to something helps you learn a whole new area. So much more satisfying than copying and pasting someone else's code!

That's what this forum is all about - may many more follow your great example

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'