@@IDENTITY

[Steve]

I am running an isert query where I need to return the identity column from
the inserted row. The fields that are being insterted can vary by request.
below is the way I am processing the request:
strSQL = "INSERT tblIndividualC (" & strFields & ") VALUES (" & strValues
& ")"



Set rsData = New ADODB.Recordset
With rsData
.ActiveConnection = SetConnectionString(strDataLocal)
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "SET NOCOUNT ON;" & strSQL & _
";SELECT @@IDENTITY AS ID;SET NOCOUNT OFF"
End With

If Not (rsData.BOF And rsData.EOF) Then
lngIndividualCnt = rsData!id
If lngIndividualCnt = 0 Then
GoSub ProcessExit
End If
Else
GoSub ProcessExit
End If

Is there a better or more efficient way of doing this?

[Bill Vaughn]

This is a typical approach, but it has its problems. The @@identity value
is subject to change if the INSERT fires a trigger. In this case if the trigger
adds another row with an ID column, you're cooked. SS2000 addresses this
problem with a couple of new global variables and a new function.

bv

"Steve" <smillard@pictorial.com> wrote:
>
>I am running an isert query where I need to return the identity column from
>the inserted row. The fields that are being insterted can vary by request.
> below is the way I am processing the request:
>strSQL = "INSERT tblIndividualC (" & strFields & ") VALUES (" & strValues
>& ")"
>
>
>
> Set rsData = New ADODB.Recordset
> With rsData
> .ActiveConnection = SetConnectionString(strDataLocal)
> .CursorLocation = adUseServer
> .CursorType = adOpenKeyset
> .LockType = adLockOptimistic
> .Open "SET NOCOUNT ON;" & strSQL & _
> ";SELECT @@IDENTITY AS ID;SET NOCOUNT OFF"
> End With
>
> If Not (rsData.BOF And rsData.EOF) Then
> lngIndividualCnt = rsData!id
> If lngIndividualCnt = 0 Then
> GoSub ProcessExit
> End If
> Else
> GoSub ProcessExit
> End If
>
>Is there a better or more efficient way of doing this?