Retrieving AutoNumber after SQL INSERT statement

Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Retrieving AutoNumber after SQL INSERT statement

  1. #1
    Ben Timby Guest

    Default Retrieving AutoNumber after SQL INSERT statement

    I am using Access for my development DB, and will be using SQL Server 7.0 for my production DB, I am inserting records into my DB like so:<BR><BR>SQLStatment = "INSERT INTO Table1 (column1, column2) VALUES (data1, data2)"<BR><BR>Conn.Execute(SQLStatement)<BR><BR>H ow do I retrieve the AutoNumber value after this? Any Suggestions would be greatly appreciated. Please email them to me if possible, or at least let me know you responded: &#060;a href="mailto:btimby@netdirect.net>btimby@netdirect .net&#060;/a>

  2. #2
    Chris Willey Guest

    Default RE: Retrieving AutoNumber after SQL INSERT statement

    Because the AutoNumber is not assigned until a record INSERT is committed, you can only get the value by re-querying the database to find out what number it just assigned. To do that, you&#039ll need another way to uniquely identify the record and perform a database query.

  3. #3
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: Retrieving AutoNumber after SQL INSERT statement

    When you add a new record, the Autonumber field for that record<BR>is the highest number in that column. Suppose that the field name is theID then you can write:<BR><BR>SQL = "SELECT max(theID) AS CurrentID FROM Table1"<BR>Set rs = Server.CreateObject("ADODB.Recordset")<BR>rs.Open SQL, Conn,1,3<BR>Response.Write(" theID is: " & rs("CurrentID"))<BR><BR>John Weeflaar<BR>

  4. #4
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: Retrieving AutoNumber after SQL INSERT statement

    Once you upgrade to SQL, there is a SQL server-variable known as @@IDENTITY. You can use this to get the latest identity value. This feature is fully documented in the following article:<BR>http://www.4guysfromrolla.com/webtech/100598-1.shtml<BR><BR>There is also a good example article at ASPToday that talks about a workaround for the lack of @@IDENTITY in Access.<BR>http://www.asptoday.com/default.asp?art=19990510.htm


  5. #5
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: Retrieving AutoNumber after SQL INSERT statement

    Just in case anyone smiles at my ASP style, then I should rewrite it (which is shorter and more readable):<BR><BR>Set rs = Conn.Execute("SELECT max(theID) AS CurrentID FROM Table1")<BR>theID is: &#060;%=rs("CurrentID")&#037;&#062;<BR><BR>John Weeflaar

  6. #6
    Join Date
    Dec 1969
    Posts
    1,315

    Default I forgot the <% ... %>, just ignore it.

    See, I made an error with this new style!<BR>I personally prefer using Response.Write, as it is more<BR>structured and the chance of making errors (by forgetting<BR>the &#060;% ... &#037;&#062;)is less. <BR><BR>John Weeflaar

  7. #7
    Stephen Fisher Guest

    Default RE: Retrieving AutoNumber after SQL INSERT statement

    That gives the max ID but how do you know another insert didn&#039t happen a second after and you end up with the same ID being inserted again? <BR><BR>eg.<BR>Change the autonumber to numeric no duplicates. <BR>strSQL = "Select max(ID) from whatever"<BR>conn.execute (strSQL)<BR>intID = fields(0) + 1<BR>strSQL = "Insert Into Whatever (ID, name) values (intID, "Stephen")<BR>conn.execute (strSQL)<BR><BR>In this example I have no guarentee that this will work because the ID may have incremented. <BR><BR>How is this resolved?<BR><BR>I was thinking of using application variables and using the lock and unlock to increment.<BR>

  8. #8
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: Retrieving AutoNumber after SQL INSERT statement

    Thanks for the idea, Stephen.<BR>To prevent almost simultaneous insert, I will try to seal<BR>the code using BeginTrans and CommitTrans:<BR><BR>Conn.BeginTrans<BR>SQL1 = "INSERT INTO Table1(col1, col2) VALUES(data1, data2) "<BR>Conn.Execute(SQL1)<BR><BR>Set rs = Conn.Execute("SELECT max(theID) AS CurrentID FROM Table1")<BR>Response.Write("theID is: " & rs("CurrentID"))<BR>Conn.CommitTrans<BR><BR>This ensures that another insert only happens after the last line (Response.Write) is executed.<BR>This also applies to your code (use BeginTrans and CommitTrans).<BR>How do you use lock and unlock?<BR><BR>John Weeflaar

  9. #9
    Ben Timby Guest

    Default RE: Retrieving AutoNumber after SQL INSERT statement

    Thank you guys for all your help! I appreciate your time in discussing this matter, I should have no problem doing this now, I think that I am going to use this method, or the Conn.BeginTrans, Conn.CommitTrans, as my main concern was that someone would execute and INSERT just after mine, and throw off the ID number retrieved(by unsing a SELECT immediately after the INSERT) Again, I appreciate all of your responses, and as this is my first posting, you should be seeing alot more from me here (hopefully on the giving end of the advice rather than receiving :-)

  10. #10
    Ben Timby Guest

    Default RE: Retrieving AutoNumber after SQL INSERT statement

    I have tried the following two methods, and getting the same problems, anyone know what my problem could be:<BR><BR>Set Temp = Conn.Execute("SELECT max(ProfileID) AS CurrentID FROM Profiles")<BR>&#037;&#062;theID is: &#060;%=Temp("CurrentID")&#037;&#062;&#060;%<BR><B R>and:<BR><BR>Temp = Conn.Execute("SELECT @@IDENTITY FROM Profiles &#039UserID&#039")<BR>&#037;&#062;theID is: &#060;%=Temp("CurrentID")&#037;&#062;&#060;%<BR><B R>Both of these methods tell me that:<BR><BR>ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application<BR><BR>and refers to the line:<BR><BR>&#037;&#062;theID is: &#060;%=Temp("CurrentID")&#037;&#062;&#060;%<BR><B R><BR>Can anyone help?<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •