Using @@Identity to find the auto id of my newly inserted record

[timtom]

I'm inserting this record in an SQL table with auto incrementing field thing. This bit works fine (after I've declared my variables based on request.form and included connection file etc.)

sSql = "INSERT INTO AM(" &_
"QR, BLAH) VALUES('" & qr & "','" & blah & "','" & "') SELECT @@IDENTITY as NEWID"

It works fine in that it inserts the record but now what? How do I do anything with that @@IDENTITY variable and is it even in the right place? I would ideally like to response.redirect with a querystring based on the Auto_id just made. Please help. Some grammar, someone?
Cheers
Sarah

_{WASN'T FIT ENOUGH FOR THE POLICE FORCE}​

 

[jonscott8]

oConn.Execute sSQL,nRecs
IF nRecs > 0 Then
Set oRS = oConn.Execute("SELECT @@IDENTITY As Ident&quot
If Not oRS.EOF Then
Response.Write oRS.Fields("Ident&quot
ENd IF
ELSE
Response.Write "Unable to Insert"
END IF Jon Hawkins

 

[timtom]

Thanks Jon, but I just can't get it to work. When I try to do anything with IDENT it just tells me "Item cannot be found in the collection corresponding to the requested name or ordinal." That old thing.

I'm running the latest version of SQL server.
I really need to get this to work,
any ideas?

Sarah

_{WASN'T FIT ENOUGH FOR THE POLICE FORCE}​

 

[link9]

Use a stored procedure and assign the @@identity to an output variable --

You can then just access it like any other sproc variable after you call the .execute method of the sproc. Although the point is debateable, it's my opinion that using sprocs is a better way to do things to your database, anyway.

To get access to the sproc, use the ADODB.Command object.

Check out

http://www.devguru.com

ADO2.6 section for more information on how to use the command object.


Paul Prewett

 

[Brench]

Your best bet may be to write a stored procedure to return the @@identity value.


this function does it all for you

Public Function RetFromSP(ByVal objCon as connection, ByVal ProcName As String, ReturnParam As Variant, ClientCursor As Boolean, ParamArray Params() As Variant) As Object
Dim objCMD As Object
Dim intCount As Integer
Dim strTemp As String
Dim objcon As Object
Dim colChunkedFields As New Dictionary
Dim objParam As Object

On Error GoTo ErrorHandler

'Create Command Object
Set objCMD = CreateObject("ADODB.Command&quot

With objCMD


'clear errors
objcon.Errors.Clear

'cursor location
If ClientCursor Then
objcon.CursorLocation = adUseClient
Else
objcon.CursorLocation = adUseServer
End If

.ActiveConnection = objcon

'Set Type to Stored Procedure
.CommandType = adCmdStoredProc

'Set Stored Procedure name
.CommandText = ProcName

'Set Input Paramaters
If Not UBound(Params) = -1 Then
For intCount = 0 To UBound(Params)
If LCase(Params(intCount)) = "null" Then
.Parameters(intCount + 1) = Null
ElseIf IsNull(Params(intCount)) Or IsEmpty(Params(intCount)) Then
.Parameters(intCount + 1) = Null
Else
.Parameters(intCount + 1) = Params(intCount)
End If
Next intCount
End If

' 'Execute Command
.CommandTimeout = mCmdTimeout
.Execute

End With

'outpt params
intCount = 0
For Each objParam In objCMD.Parameters
Select Case objParam.Direction
Case 4
ReturnParam = objParam.Value
Case 2, 3
Params(intCount - 1) = objParam.Value
End Select
intCount = intCount + 1
Next objParam


'get return param
ReturnParam = objCMD.Parameters(0)
Set RetFromSP = objCMD.Parameters(0)

'set errors colection
Set mcolConErrors = objcon.Errors

Set objCMD = Nothing
Set objcon = Nothing

Exit Function

ErrorHandler:

Set objCMD = Nothing
'set errors colection
If objcon Is Nothing Then
Set mcolConErrors = New Collection
Else
Set mcolConErrors = objcon.Errors
End If

Set objcon = Nothing

Err.Raise Err.Number, Err.Source, Err.Description
Resume
End Function

 

[xcata]

Hi,

For jonscott8 example try with oRS(0).

 

[jonscott8]

Sarah, although posting the error message is very helpful, it would be more beneficial if you could post your code.

Because the result set consist of only one field, you can use the ordinal position, ISO the field name, as in xcata's suggestion.

Response.Write oRS.Fields(0).Value

Also, in your initial posting you had:
SELECT @@IDENTITY as NEWID

If you used this code, then the name of the field would not be IDENT, it would be NEWID.

Response.Write oRS.Fields("NEWID&quot.Value Jon Hawkins