How to Pull ID from Last Insert Statement...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to Pull ID from Last Insert Statement...

Author

Topic

kwilliams

194 Posts

Posted - 2004-06-16 : 10:08:15

I'm trying to create a page that does the following:
1) INSERTS data into a DB table view named Ticket_summary
2) Pull the last ID from that specific Insert statement as an interger
3) Insert more data, including the ID from the previous Insert, into another DB table view named Ticket_detail.

I'm having a problem with pulling the ID from the 1st insert. I've looked everywhere for solutions, and have found several suggestions. But none of them have worked. Some of the suggestions I've received are:

1)
sqlNewID = "SELECT max(Ticket) from Ticket_summary_VIEW"
strNewID = objRS("Ticket")

ERROR RESULT:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/HelpDesk/bgscripts/addticket.asp, line 115

2)
sqlNewID = "SELECT Ticket = @@identity FROM Ticket_summary_VIEW"
strNewID = objRS("Ticket")

RESULT:
No error message, but it doesn't display any value in the "strNewID" interger

3)
sqlNewID = "SELECT LAST_insert_ID() AS strNewID FROM Ticket_summary_VIEW"

ERROR RESULT:
Microsoft OLE DB Provider for SQL Server error '80040e14'
'LAST_insert_ID' is not a recognized function name.
/HelpDesk/bgscripts/addticket.asp, line 114

4)
sqlNewTicket = "INSERT INTO Ticket_summary_VIEW(DateTime,Client,Username,Email,Department,Phone,ComputerName,ResponseTime,Status,AssignedTech,ProblemCategory,Summary) VALUES('" & strDateTime & "','" & strClient & "','" & strUsername & "','" & strEmail & "','" & strDept & "','" & strPhone & "','" & strPCName & "','" & strPriority & "','" & strStatus & "','" & strTech & "','" & strCategory & "','" & strSummary & "');SELECT Ticket = @@identity FROM Ticket_summary_VIEW"
strNewID = objRS("Ticket")

ERROR RESULT:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/HelpDesk/bgscripts/addticket.asp, line 97

Can anyone tell me of a simple way to pull the last ID entered (Ticket) from Ticket_summary_VIEW so that I can insert that ID & other data into Ticket_detail_VIEW? Any & all help is appreciated.

P.S. I've included the 3 statements' code for the page at the bottom of this post.

CODE FOR PAGE:

]<%
  '------------------Database Query-----------------------
 sqlNewTicket = "INSERT INTO Ticket_summary_VIEW(DateTime,Client,Username,Email,Department,Phone,ComputerName,ResponseTime,Status,AssignedTech,ProblemCategory,Summary) VALUES('" & strDateTime & "','" & strClient & "','" & strUsername & "','" & strEmail & "','" & strDept & "','" & strPhone & "','" & strPCName & "','" & strPriority & "','" & strStatus & "','" & strTech & "','" & strCategory & "','" & strSummary & "')" 
 strConnectString = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DBNAME; User ID=UID; Password=PWD"
 Set objConn = Server.CreateObject("ADODB.Connection")
 objConn.Open strConnectString

 Set objRS = Server.CreateObject("ADODB.RecordSet")
 objRS.Open sqlNewTicket, objConn
 Set objRS = nothing
 Set objConn = nothing
 Response.Write "<br><br>"
 Response.Write "<strong>1st INSERT STATEMENT: </strong>" & sqlNewTicket
 Response.Write "<br><br>"
 Response.Write "<strong>RESULT: </strong>The 1st insert was successful.<br><br>"
 '------------------End Database Query-------------------
%>
<%

  '------------------Database Query-----------------------
 sqlNewID = "SELECT Ticket = @@identity FROM Ticket_summary_VIEW"
 strConnectString = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DBNAME; User ID=UID; Password=PWD"
 Set objConn = Server.CreateObject("ADODB.Connection")
 objConn.Open strConnectString

 Set objRS = Server.CreateObject("ADODB.RecordSet")
 objRS.Open sqlNewID, objConn
 strNewID = objRS("Ticket")
 Set objRS = nothing
 Set objConn = nothing
 Response.Write "<strong>RESULT: </strong>Pull of ID was successful.<br><br>"
 Response.Write "<strong>Ticket ID: </strong>" & strNewID
 '------------------End Database Query-------------------
%>
<%
  '------------------Database Query-----------------------
 sqlTicketdetail = "INSERT INTO Ticket_detail_VIEW(Ticket,DateTime,DisplayToClient,IncludeInEmail,Detail) VALUES('" & strNewID & "','" & strDateTime & "','checked','checked','" & strDetail & "')"
 strConnectString = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DBNAME; User ID=UID; Password=PWD"
 Set objConn = Server.CreateObject("ADODB.Connection")
 objConn.Open strConnectString

 Set objRS = Server.CreateObject("ADODB.RecordSet")
 objRS.Open sqlTicketdetail, objConn
 Set objRS = nothing
 Set objConn = nothing
 Response.Write "<br><br>"
 Response.Write "<strong>2nd INSERT STATEMENT: </strong>" & sqlTicketdetail
 Response.Write "<br><br>"
 Response.Write "<strong>RESULT: </strong>The 2nd insert was successful."
 '------------------End Database Query-------------------
%>

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 10:17:47

For @@Identity to work it needs to be with the insert itself

ie.

sqlStr = "Insert Into blah Select blah = 'blahblah' Select id = @@identity"

Corey

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-06-16 : 10:21:27

OPtion (1) has a few flaws

A: No name given to max(ticket) - (ie. Must be max(ticket) Ticket from Ticket_summary_VIEW)
B: Could cause Isolation issues (Refer to Transaction properties)

Option(2)

Could cause Isolation issues (Refer to Transaction properties)

Option(3)

Is not a udf or valid column name in the table Ticket_summary_VIEW)

Option(4)

Addressed by Seventh Night

The preferred method to consider - use a stored procedure to perform the insert into both tables or a trigger on the first insert to perform the insert into the second table (if possible with given parameters)

kwilliams

194 Posts

Posted - 2004-06-16 : 10:27:41

Hello ehorn and Seventhnight,

I should have cleared that up. I did infact try putting the SELECT statement at the end of the first Insert like this:

 sqlNewTicket = "INSERT INTO Ticket_summary_VIEW(col1,col2,col3,etc...) VALUES(value1,value2,value3,etc...);SELECT Ticket = @@identity"
ADODB.Recordset error '800a0cc1' 
strNewID = objRS("Ticket")

But I received this error message:
Item cannot be found in the collection corresponding to the requested name or ordinal.

/HelpDesk/bgscripts/addticket.asp, line 97

Any idea why this isn't working?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2004-06-16 : 10:32:37

Hey.

Try doing "SET NOCOUNT ON" before the INSERT statement. It probably thinks that it gets two recordsets, one empty for the insert statement, and one for the select statement.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-06-16 : 10:35:28

Try this:

sqlNewTicket = "SET NOCOUNT ON ; INSERT INTO Ticket_summary_VIEW(col1,col2,col3,etc...) VALUES(value1,value2,value3,etc...) ; SELECT Ticket = @@identity"

EDIT: Sniped :)

kwilliams

194 Posts

Posted - 2004-06-16 : 10:41:48

IT WORKED. Thanks so much for the help from the both of you. The quick response & solution was a big help, and it's greatly appreciated by me. Thank you. Thank you. Thank you.

Kristen
Test

22859 Posts

Posted - 2004-06-16 : 11:54:07

I suggest you use SCOPE_IDENTITY() instead of @@IDENTITY (@@IDENTITY may get changed by triggers etc)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources