Trying to use @@identity

You're better off to use scope_identity() instead of @@identity: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

All you need to do is do your insert then in the same command do:

select scope_identity() as RecordID

This will make it so your recordSet will have a RecordID you can pull from.

Post what code you've got and I can help more.

Here is the code:
 

session.LCID = 1033
set objRecordset = server.CreateObject("ADODB.Recordset")
set objConnection = server.CreateObject("ADODB.Connection")
dim theDateTime
if Request.Form("DateTime") = "Enter Date/Time here" then
	theDateTime = "1-1-1900"
end if
objConnection.ConnectionString = OLEDB_SD

objConnection.Open

'Post the event
sqlStatment = "insert into sdEvents " & _
			"(field1, field2) values " & _
			"('" & Request.Form("field1data") & "', '" & theDateTime & "'); select SCOPE_IDENTITY AS theID;"
			
objRecordSet.ActiveConnection = objConnection
set objRecordSet = objConnection.Execute(sqlStatment)
Response.Write "xx" &  objRecordSet.fields("theID")
Response.end

Select allOpen in new window

expecting the final line to spit out the ID.

session.LCID = 1033
set objRecordset = server.CreateObject("ADODB.Recordset")
set objConnection = server.CreateObject("ADODB.Connection")
dim theDateTime
if Request.Form("DateTime") = "Enter Date/Time here" then
	theDateTime = "1-1-1900"
end if
objConnection.ConnectionString = OLEDB_SD

objConnection.Open

'Post the event
sqlStatment = "insert into sdEvents " & _
			"(eventTitle, eventDate, eventTime, eventAddress, eventAddress2, eventCity, eventState, eventZip, eventComments, theGroup) values " & _
			"('" & Request.Form("txtEventName") & "', '" & theDateTime & "', '" & theDateTime & "', '" & Request.Form("txtAddress") & "', '" & _
			Request.Form("txtAddress2") & "', '" & Request.Form("txtCity") & "', '" & Request.Form("txtState") & "', '" & Request.Form("txtZip") & "', '" & _
			Request.Form("txtComments") & "', " & Request.QueryString("GP") & "); select SCOPE_IDENTITY AS theID;"
			
objRecordSet.ActiveConnection = objConnection
set objRecordSet = objConnection.Execute(sqlStatment)
Response.Write "xx" &  objRecordSet.fields("theID")
Response.end

Select allOpen in new window

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

even you can try this

if you want to return on row basis
USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO

crisco96:  can you tell me where my code is lacking?

Still no joy...

angelIII:  It does work... since I posted this I replace @@indentiy with scope_indentity which gave me an error (column scope_identity doesn't exist).

Switching back to @@identity works.

Thanks

it must be:

"...select SCOPE_IDENTITY() AS theID;"

it's a function, so you need to brackets. please use SCOPE_IDENTITY() ...

Thank you!