SCOPE_IDENTITY() vs. rs.Fields

Question

what is the difference in the way these work:

Sql = "INSERT INTO mytable (datapath, analysistime,reporttime, lastcalib,analystname,reportname,batchstate,instrument) " & _
      "VALUES (dpath, atime, rtime,lcalib,aname,rname,bstate,instrument) SELECT SCOPE_IDENTITY()"

Set rs = cn.Execute
Set rs = rs.NextRecordset

and 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
      id=fields.Fields("rowid")  ' ** Answer to Question ***     
End With

my question is specifically this:

i am in a multiuser environment. immediately after the user adds a record, i need to catch the ROWID of the record added. how do i do this?

this is how i open the recordset:

rs.Open "batchinfo", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Answer 1

The different is the way you add the record and get the result back.

In the first case, you are issuing an INSERT statement followed by a call to SCOPE_IDENTITY.

In the second case, you open an updatable cursor, add a record into it and read the newly added record back.

Opening a cursor may be quite a resource-intensive operation (this depends on how do you do it). It also can degrade concurrency.

Answer 2

Your first code example is not legal in SQL Server. What are the names after the VALUES clause supposed to be? I guess they are supposed to be parameters but you cannot pass parameters like that. Is there some reason why you are not using a parameterized stored procedure and parameter objects to pass in parameters?

Answer 3

Most of the time I use the open recordset, add data, update, grab ID method however as Quassnoi said it can be resource intensive. For parts of the application that are called a lot or need to run as fast as possible I tend to use a stored procedure with the new row’s ID as a return parameter.

Here is a code example

Set cmd = New ADODB.Command

With cmd

    .CommandText = "sptblTest_questions_UPSERT"
    .CommandType = adCmdStoredProc
    .ActiveConnection = dbCon

    .Parameters.Append .CreateParameter("@Question_ID", adInteger, adParamInput, 0, Me.txtQuestion_ID)

    .Parameters.Append .CreateParameter("@Section_ID", adInteger, adParamInput, 0, Me.txtSection_ID)

    .Parameters.Append .CreateParameter("@Question_number", adTinyInt, adParamInput, 0, Me.txtQuestion_number)

    .Parameters.Append .CreateParameter("@Question_text", adVarChar, adParamInput, 1000, Me.txtQuestion_text)

    .Parameters.Append .CreateParameter("@Max_score", adSmallInt, adParamInput, 0, Me.txtMax_score)

    .Parameters.Append .CreateParameter("@User", adVarChar, adParamInput, 50, fOSUserName)

    .Parameters.Append .CreateParameter("@Inserted_ID", adInteger, adParamOutput, 0)

    .Execute

    Me.txtQuestion_ID = .Parameters("@Inserted_ID")

End With