| Author |
 Topic Search  Topic Options
|
d088 
Newbie
Joined: 02 December 2004
Status: Offline
Points: 5
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Get ID from a DB Field after INSERT
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
|
 |
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
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
|
|
d088
Newbie
Joined: 02 December 2004
Status: Offline
Points: 5
|
Post Options
Thanks(0)
Quote Reply
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)
|
|
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
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
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
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. |
|
|
|
Gullanian
Senior Member
Joined: 04 January 2002
Location: England
Status: Offline
Points: 4373
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
d088
Newbie
Joined: 02 December 2004
Status: Offline
Points: 5
|
Post Options
Thanks(0)
Quote Reply
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!
|
|
Bunce
Senior Member
Joined: 10 April 2002
Location: Australia
Status: Offline
Points: 846
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
