?
Solved

Can't get the id of the last inserted record

Posted on 2006-05-27
18
Medium Priority
?
367 Views
Last Modified: 2008-02-26
I'm using ASP Classic with MS SQL 2005.  I'm inserting a new record using addNew() and want to get the identifier (the index field) of the last record inserted.  I thought this would be straight forward enough but nothing I try seems to work.  Here's the code... and it's NewOrderID that keeps coming up NULL....

                                Set cnOrder = Server.CreateObject("ADODB.Connection")
            Set cnOrder1 = Server.CreateObject("ADODB.Connection")
            Set rsO = Server.CreateObject("ADODB.Recordset")
            Set rsODetails = Server.CreateObject("ADODB.Recordset")
            
            cnOrder.Open MM_connDBA_STRING
            cnOrder1.Open MM_connDBA_STRING
            
            rsO.Open "Orders",MM_connDBA_STRING,3,2
            rsODetails.Open "OrderDetails",MM_connDBA_STRING,0,3

            sSQL = "SELECT * FROM Orders_Temp WHERE OrderID=" & Session("OrderID")            
            Set rsO1 = cnOrder1.Execute(sSQL)
            
            sSQL = "SELECT * FROM OrderDetails_Temp WHERE OrderID=" & Session("OrderID")
            Set rsODetails1 = cnOrder1.Execute(sSQL)
            
            Do While Not rsO1.EOF
                  rsO.AddNew
                  For Each Field In rsO1.Fields
                        If UCase(Field.Name) <> UCase("OrderID") Then
                              rsO(Field.Name) = rsO1(Field.Name)
                              'Response.Write field.name & "=" & rsO1(Field.Name) & "<br>"
                        End If
                  Next
                  rsO.Update
                  rsO1.MoveNext
            Loop
            
            NewOrderID = rsO("OrderID")

0
Comment
Question by:philwill4u
  • 5
  • 5
  • 3
  • +3
18 Comments
 
LVL 23

Expert Comment

by:basicinstinct
ID: 16774908
0
 
LVL 10

Accepted Solution

by:
aplimedia earned 2000 total points
ID: 16775193
What you need to do is use adOpenKeySet as your curso type. this allows you to create a new record, then before closing the connection go back one more time and get the ID of the record that has just been made.

Rather than working throught your MM_Generated overcode I have inculded and example below.
Kind regards

Aplimedia

strSQL = "SELECT * FROM tblShopCutomers;"
      rsData.Source = strSQL
'      rsData.CursorType = adOpenDynamic
      rsData.CursorType = adOpenKeySet 'This allows to retrieve data after the update, the custmer ID!
      rsData.LockType = adLockPessimistic
      rsData.Open
      rsdata.AddNew
      rsData("q4") = strQ4
      rsData.Update            
      strCustomerID = rsData("ID")
      rsData.Close
      'Set rsData = Nothing
0
 
LVL 25

Expert Comment

by:kevp75
ID: 16775583
the most difficult part about this is what happens if 2 people enter/update a record at the same exact time.  You still will not get the last ID.

Since my project site doesn't get any traffic, I have a datetime field, and select the record sorted Descending by the timestamo
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:aplimedia
ID: 16775615
@kevp75
That is not correct. Using adOpenKeySet is alaways gaurenteed to be the correct ID.

Aplimedia
0
 
LVL 25

Expert Comment

by:kevp75
ID: 16775740
sorry I was always under the impression that you couldn't do a select statement and an insert in the same recordset, using that method.  Doesn't adOpenKeySet make the record inaccessible.....maybe I'm reading this wrong...

"Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible."

from http://www.w3schools.com/ado/prop_rs_cursortype.asp
0
 
LVL 7

Expert Comment

by:chisholmd
ID: 16775785
If your using MS SQL then @@identity returns the last generated ID for this connection.

insert into....
select @@identity
if your using ms access here is a link wiht an example of another method:
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=117

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16776155
You really wrap this code in a stored procedure, but if you insist at least lose cycling through the Recordset object, using multiple connections and try something like this (untested):

Dim cn, rs
Dim SQL, NewOrderID

Set cn = Server.CreateObject("ADODB.Connection")
With cn
      .ConnectionString = MM_connDBA_STRING
      .Open
      ' Replace Col1, Col2, Col3, ... with all the columns except for the IDENTITY column (OrderID)
      SQL = "SET NOCOUNT ON; " & _
                  "Insert OrderDetails (Col1, Col2, Col3, ...) " & _
                  "Select Col1, Col2, Col3, ... From Orders_Temp Where OrderID=" & Session("OrderID") & "; " & _
                  "Select Scope_Identity() as OrderID"
      Set rs = .ExecuteSQL SQL
      NewOrderID = rs.Fields("OrderID").Value
      rs.Close
      Set rs = Nothing
      cn.Close
End With
Set cn = Nothing
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16776165
Also, the following question is past considered abandoned and is in a severe state of decomposition, please attend to it:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21789835.html
0
 

Author Comment

by:philwill4u
ID: 16778838
I've tried the adOpenKeySet solution and it works even with multiple users.  Thanks for all your help.  

With the http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21789835.html ... well there is no solution until MS sort out the problem.  I can't seem to delete/ revoke the question.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 16779416
you can if you go to the support section of the site, and ask to get it deleted/closed
http://www.experts-exchange.com/Community_Support/
0
 
LVL 10

Expert Comment

by:aplimedia
ID: 16780441
Glad to have helped...

Aplimedia
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16781718
>>well there is no solution until MS sort out the problem. <<
That is unlikely to happen.  It is a user error, not a MicroSoft error...
>>I can't seem to delete/ revoke the question.<<
Where there is a will there is a way.  In other words, read the EE Help:

What are my choices?
http://www.experts-exchange.com/Web/Web_Languages/ASP/help.jsp#hi67
0
 

Author Comment

by:philwill4u
ID: 16781967
Thanks but its not a user error.  It is a problem with MS Server 2005 and one that wasn't present in SQL 2000.  I'm not going to go into this in any great detail here as this thread isn't intended to discuss MS Server 2005.  To copy a Db in 2005 you need permissions that you're unlikely to get in a hosted environment.  There are ways around this.... using scripts to generate a copy of new tables, views and content onto another server but there are problems here too... particularly with the recreation of the identity specification - not impossible to recreate but not easy either.

Thanks for the link to the choices... I'll close the issue.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 16782101
I didn't realize microsoft released server 2005???
0
 

Author Comment

by:philwill4u
ID: 16782629
MS SQL Server 2005
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16784816
>>I'll close the issue.<<
Thanks.

>>I didn't realize microsoft released server 2005???<<
Service Pack 1 has already been released, where have you been :)
0
 
LVL 25

Expert Comment

by:kevp75
ID: 16785310
SQL server 2005.   In a very small closet apparently LMAO

i've 2000 across the board, and a demo of SQL 2005, however, i've not found anything about it impressive enough to warrant the upgrade...who know, maybe some day I'll evolve into the Bronze Age, but so far the Dark Ages are still fun   :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16785341
>>maybe some day I'll evolve into the Bronze Age, but so far the Dark Ages are still fun <<
You and I both.  I suspect I will make the transition some time this year, if only to stay in the rat race.  That spinning wheel is going faster and faster ...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month14 days, 4 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question