sql server: how do i get the last record that was inserted?

Question

i am doing this:

With rs
    .AddNew ' create a new record
    ' add values to each field in the record
    .Fields("datapath") = dpath
    .Fields("analysistime") = atime
    .Fields("reporttime") = rtime
    .Fields("lastcalib") = lcalib
    .Fields("analystname") = aname
    .Fields("reportname") = rname
    .Fields("batchstate") = bstate
    .Fields("instrument") = instrument
    .Update ' stores the new record
End With

' get the last id
Set rs = cn.Execute("SELECT SCOPE_IDENTITY()", , adCmdText)

this is not working properly. it is returning NULL

possible duplicate of [scope_identity() question](http://stackoverflow.com/questions/3526851/scope-identity-question) — Will A, Aug 19 '10 at 23:11

score 3 · Accepted Answer · answered Aug 19 '10 at 23:11

3

It's not working because your updates and your second execute are in different scopes. You may want SELECT IDENT_CURRENT('tablename')

answered Aug 19 '10 at 23:11

Matthew

10,244
5
49
104

its a good point, but i dont think its right. i put the scope identity inside the the WITH clause and still getting NULL returned – Alex Gordon Aug 19 '10 at 23:13
it says that scope function requires 0 arguemnts – Alex Gordon Aug 19 '10 at 23:15
1

No, your update is called its own execute. Then you are calling a second execute to get the identity. imho, SCOPE_IDENTITY() is most useful in stored procedures which must happen all at once. Using "with rs" doesn't mean "do all or nothing" in this regard – Matthew Aug 19 '10 at 23:15
SELECT IDENT_CURRENT('tablename') will return the most recent Identity autonum from the table – Matthew Aug 19 '10 at 23:16
Best of luck to you, next time don't duplicate your question and people probably won't vote you down. – Matthew Aug 19 '10 at 23:19
1. i dont care about my score, 2. im here to get my q answered – Alex Gordon Aug 19 '10 at 23:21
Editing or updating your initial question gets it bumped. Therefore there is no reason to duplicate them. Keep in mind though that depending on your DB transaction volume this method may fail. If another record gets inserted into the DB between when you call your INSERT and when you SELECT IDENT then you will have the wrong value. To prevent this I'd suggest you use a stored procedure which returns the SCOPE_IDENTITY – Matthew Aug 19 '10 at 23:24
@Matthew PK: sorry how do i do that? – Alex Gordon Aug 19 '10 at 23:31
1

Stored procedures are a little outside the scope of this question, but the basic idea is that you create a procedure which accepts parameters. They can be executed just like any SQL statements "StoredProcedureName(Parameter1, Parameter2......) Then, inside the stored procedure you add all the steps required for your transaction. This ensures that everything is committed once, simultaneously and in the same scope. I try to use procedures almost at all times for DB transactions. This link may get you started: http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx – Matthew Aug 19 '10 at 23:36

score 1 · Answer 2 · answered Aug 20 '10 at 02:09

1

IDENT_CURRENT is fine in a single user environment.

You're already on the record when you update.

.Update
lTheNewID = .Fields("ThisTableID")

lTheNewID will hold the value of the new record.

answered Aug 20 '10 at 02:09

JeffO

7,957
3
44
53

Unless you're using it in a stored proc, I would avoid it. – JeffO Aug 20 '10 at 17:43
I agree with Jeff O, as stated in my answer. I'd consider using a stored procedure to ensure that all transactions occur; and the resulting ID is what you're looking for. – Matthew Aug 20 '10 at 22:49

sql server: how do i get the last record that was inserted?

2 Answers2