Get ID from a DB Field after INSERT
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=12805
Printed Date: 30 March 2026 at 7:50pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Get ID from a DB Field after INSERT
Posted By: d088
Subject: Get ID from a DB Field after INSERT
Date Posted: 02 December 2004 at 2:04pm
|
I am using a form on an ASP page to submit to a database. The database creates the record and assigns the pk_ID field. I need to get the value of that field so that if the user pays online, I can pass it to VeriSign. Any help would be appreciated. Thanks, Ben |
Replies:
Posted By: dj air
Date Posted: 02 December 2004 at 2:13pm
|
what code are you using to add the record. if its not a ADD into query but a Select ...etc.. Connection.Addnew if the above line is used. you can use the following after its updated connection.requery connection.movelast ID = connection("PK_ID") thats not actual code but is a way of doing so you will neded to set the connection.locktype = 3 |
Posted By: d088
Date Posted: 02 December 2004 at 2:17pm
|
Thank you - I will try this out. Here is the code if you are interested: 'INSERT FORM INFORMATION INTO DATABASE If request.form("FName")<>"" Then FName= request.form("FName") Else FName= "None" If request.form("LName")<>"" Then LName=request.form("LName") Else LName="None" If request.form("Company")<>"" Then Company=request.form("Company") Else Company="None" If request.form("Phone")<>"" Then Phone=request.form("Phone") Else Phone="None" Fax="NULL" If request.form("Email")<>"" Then Email=request.form("Email") Else Email="None" If request.form("Address1")<>"" Then Address1=request.form("Address1") Else Address1="None" If request.form("Address2")<>"" Then Address2=request.form("Address2") Else Address2="None" If request.form("City")<>"" Then City=request.form("City") Else City="None" If request.form("State")<>"" Then St=request.form("State") Else St="None" If request.form("Zip")<>"" Then Zip=request.form("Zip") Else Zip="None" If request.form("Cancellation_Agreement")<>"" Then Cancellation_Agreement=request.form("Cancellation_Agre ement") Else Cancellation_Agreement=None If request.form("Amount_Due")<>"" Then Amount_Due=request.form("Amount_Due") Else Amount_Due="None" If request.form("IP")<>"" Then IP=request.form("IP") Else IP="None" 'OPEN DATABASE CONNECTION myDSN="DSN=hp04" set connAuction=server.createobject("adodb.connection") connAuction.open myDSN sql="INSERT INTO Primary_Registrations (FName, LName, Company, Position, Phone, Fax, Email, Address1, Address2, City, St, Zip, Cancellation_Agreement, Amount_Due, IP) VALUES ('"&FName&"', '"&LName&"', '"&Company&"', '"&Position&"', '"&Phone&"', '"&Fax&"', '"&Email&"', '"&Address1&"', '"&Address2&"', '"&City&"', '"&St&"', '"&Zip&"', '"&Cancellation_Agreement&"', '"&Amount_Due&"', '"&IP&"')" connAuction.execute(sql) |
Posted By: dj air
Date Posted: 02 December 2004 at 2:34pm
|
you can't use the code i said with that type of SQL you will nedd to set another query that SELECT TOP 1 Primary_Registrations.PK_ID FROM Primary_Registrations ORDER BY Primary_Registrations DESC; that SQL will give you the last record in the DB. then you can bring in the value |
Posted By: michael
Date Posted: 02 December 2004 at 3:26pm
|
Thus in the unlikely event of two users buying something at the same time, the Select Top1 could return the wrong record. Not sure if you use access but in SQL Server You can put that stuff into a stored procedure with a return type of @@Identity so for example
INSERT INTO table1 (field1,field2,field3)
VALUES (1,12,125) SELECT @@IDENTITY AS 'Identity' would return the last ID. If you are using Access  (not sure if something similar is possible) I would create a GUID in asp and use that as the PK in the table instead of an autonumber.------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: Gullanian
Date Posted: 02 December 2004 at 4:15pm
|
I couldn't figure it out with Access, so I did a select top 1 order by
ID desc immediatly after the input. Works fine as long as no one
does an input in that nano second the script is executing. This seems to be a problem with quite a few scripts that pops up every now and then. |
Posted By: d088
Date Posted: 02 December 2004 at 4:28pm
|
I really appreciate the advice fellas. This should work just fine for the volume that I'll be dealing with. Thanks! |
Posted By: Bunce
Date Posted: 02 December 2004 at 4:35pm
|
Access (Jet) supports @@Identity as well: http://support.microsoft.com/kb/q232144/ http://www.adopenstatic.com/experiments/fastestautonumber. asp Cheers, Andrew ------------- There have been many, many posts made throughout the world... This was one of them. |
Posted By: d088
Date Posted: 03 December 2004 at 1:07pm
|
Well, now I've hit another problem... Any more help would once again be appreciated. <>Microsoft VBScript runtime error '800a000d' Type mismatch: 'sql2' /hp04/test/register_confirm.asp, line 32
sql2="SELECT TOP 1 pk_Reg_ID FROM Primary_Registrations ORDER BY pk_Reg_ID DESC;" connAuction.execute(sql2) Session("reg_ID")=sql2("pk_ID") &nb sp; &nb sp; <!--LINE 32 |
Posted By: Gullanian
Date Posted: 03 December 2004 at 1:48pm
| sql2 is a string variabke, you need to reference the recordset. |
Posted By: d088
Date Posted: 03 December 2004 at 2:57pm
|
Thank you! Here's the final code for anyone that has a similar problem... <% 'INSERT FORM INFORMATION INTO DATABASE If request.form("FName")<>"" Then FName= request.form("FName") Else FName= "None" If request.form("LName")<>"" Then LName=request.form("LName") Else LName="None" If request.form("Company")<>"" Then Company=request.form("Company") Else Company="None" If request.form("Phone")<>"" Then Phone=request.form("Phone") Else Phone="None" Fax="NULL" If request.form("Email")<>"" Then Email=request.form("Email") Else Email="None" If request.form("Address1")<>"" Then Address1=request.form("Address1") Else Address1="None" If request.form("Address2")<>"" Then Address2=request.form("Address2") Else Address2="None" If request.form("City")<>"" Then City=request.form("City") Else City="None" If request.form("State")<>"" Then St=request.form("State") Else St="None" If request.form("Zip")<>"" Then Zip=request.form("Zip") Else Zip="None" If request.form("Cancellation_Agreement")<>"" Then Cancellation_Agreement=request.form("Cancellation_Agre ement") Else Cancellation_Agreement=None If request.form("Amount_Due")<>"" Then Amount_Due=request.form("Amount_Due") Else Amount_Due="None" If request.form("IP")<>"" Then IP=request.form("IP") Else IP="None" myDSN="DSN=hp04" set connAuction=server.createobject("adodb.connection") connAuction.open myDSN sql="INSERT INTO Primary_Registrations (FName, LName, Company, Position, Phone, Email, Address1, Address2, City, St, Zip, Cancellation_Agreement, Amount_Due, IP) VALUES ('"&FName&"', '"&LName&"', '"&Company&"', '"&Position&"', '"&Phone&"', '"&Email&"', '"&Address1&"', '"&Address2&"', '"&City&"', '"&St&"', '"&Zip&"', '"&Cancellation_Agreement&"', '"&Amount_Due& ;"', '"&IP&"')" connAuction.execute(sql) connAuction.close %> <% myDSN="DSN=hp04" set conn2Auction=server.createobject("adodb.connection") conn2Auction.open myDSN set rsAuction=server.createobject("adodb.recordset") getID="SELECT TOP 1 pk_Reg_ID FROM Primary_Registrations ORDER BY pk_Reg_ID DESC;" rsAuction.open getID, myDSN set Session("reg_ID")=rsAuction.Fields("pk_Reg_ID") %> |
Posted By: Bunce
Date Posted: 04 December 2004 at 4:42am
|
Really shoudldn't be using 'Select Top 1' - too risky. Check out the links I posted. They implemented that feature exactly for the reason that you *wouldn't* have to run another query... ------------- There have been many, many posts made throughout the world... This was one of them. |