SCOPE_IDENTITY() vs. rs.Fields

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

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.

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?

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