SCOPE_IDENTITY（）与rs.Fields [英] 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

，并且：

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

我的问题特别是这样：

我在多用户环境中。用户添加记录后，我需要立即捕获所添加记录的ROWID。我该怎么做？

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.

在第一种情况下，您发出的是 INSERT 语句，然后调用 SCOPE_IDENTITY 。

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.

这篇关于SCOPE_IDENTITY（）与rs.Fields的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！