@@IDENTITY


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: @@IDENTITY

  1. #1
    Steve Guest

    @@IDENTITY


    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?
    Share on Google+

  2. #2
    Bill Vaughn Guest

    Re: @@IDENTITY


    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?


    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center