[dba-SQLServer] Getting the PK of a SQL Server table

Francisco,

>I also would avoid your coding from the client end to add the record,
instead I'd setup parameters and pass them to a sproc (stored
procedure) and allow the sproc to return a value (scope_identity())

Of course that is true.  Unfortunately my client is now entering the third
week of the database corrupting 6 times per day.  I am doing an emergency
port to SQL Server to get THAT problem stabilized.  I just want my
APPLICATION to run, tweaks can follow.  For now, I have the code shown below
and need to change it, 'cause it don't work no more. 

Dim RS As ADODB.Recordset
    If mlngUserID = 0 Then Exit Function
    Set RS = New ADODB.Recordset
    With RS
        .Open "usystbllwsLog", gcnn, adOpenKeyset, adLockPessimistic
        'build a logout record.
        .AddNew
        !LWSL_IDLWSU = mlngUserID
        !LWSL_FE = CurrentProject.name
        !LWSL_Login = blnLogIn
        !LWSL_WorkstationID = CurrentMachineName()
        mlngLogID = !LWSL_ID
        .Update
        .Close
    End With
    Set RS = Nothing

The code

        mlngLogID = !LWSL_ID

Fails because mlngLogID is a long int which cannot contain a null, and
!LWSL_ID returns a null.  Thus I need an emergency fix that gives me back my
new ID.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco
Tapia
Sent: Sunday, April 01, 2007 1:06 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Getting the PK of a SQL Server table

you always want to use scope_identity(), because you never know if you will
introduce code in the future that may write at the same time when your code
is executing, thus providing you w/ a false @@identity

I also would avoid your coding from the client end to add the record,
instead I'd setup parameters and pass them to a sproc (stored
procedure) and allow the sproc to return a value (scope_identity())