ASP Add New Record and Get ID

omninewt · April 5th, 2005, 04:09 PM

Question regarding adding new records to a database table with ASP.

I'm trying to add a new record to a table, pull the ID, and add new records to linked sub tables based on this ID. I had a little trouble getting the ID after adding a record, because when I requiry, I loose my position in the table.

Therefore I've done a real embarrassing work-around. Basically I date/time stamp the record then pull based on the time stamp (with a little verification for single record results).

I'm sure there are better ways to do this and I hope someone might be able to enlighten me.

Thanks,
Mike

askenkray · August 22nd, 2005, 12:12 AM

In VB ,you can use transactions,but I am not sure if you have transaction-commit & rollback in ASP.However,You can use transaction at the database level.

This is surely the best method.

Ashwin S.Kenkare
Programmer
CMI

rstelma · August 22nd, 2005, 01:58 PM

It depends on what database you are using. MS-SQL server has a function that you can use to retrieve the just inserted identity.

mcamire · November 27th, 2005, 06:57 AM

SELECT IDENT_CURRENT('THETABLE') AS myId

Use the IDENT_CURRENT to retreive the last ID from a given table

pgtips · November 27th, 2005, 12:40 PM

What's the point of resurrecting topics that are months old? Especially to give dodgy advice like that...

For the sake of the archive - IDENT_CURRENT will give the last inserted ID in SQL Server but it might not be the one that *you* inserted! @@IDENTITY works on SQL Server and Access (with Jet 4), SCOPE_IDENTITY is better on SQL Server 2000 if you're worried about other IDs inserted by triggers.

Frye-Guy · April 10th, 2008, 12:20 PM

I realize this is an old topic but I am answering it because it is spidered on google and I wanted to tell others finding this topic how this is done properly. I am rather surprised that this one wasn't answered due to how simple the answer is. Anyhow here's what you do.

Before you add a new record always check for an existing record. Use an ADODB connection to open the database. Use the proper connection string. Create the recordset object (.Open "<TABLENAME>", dbconn, 1, 2 '.Open "name_of_your_table", dbconn, adOpenKeyset,adLockPessimistic). Add a new record, populate the fields and then update. After you update the recordset and before you close it set a variable to the value of the ID. Below is an example

Code:

    'Create a database connection
    Set dbConn = Server.CreateObject("ADODB.Connection")
    dbConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & databasePath2)
    'Create a recordset object
    Set rsNt = Server.CreateObject("ADODB.Recordset")

    with rsNt
        'this line uses the constants
        'If you don't wanna go find adovbs.asp then you can use this line:
        .Open "users", dbconn, 1, 2
        '.Open "name_of_your_table", dbconn, adOpenKeyset,adLockPessimistic
        .AddNew
        'defining some data
        .Fields("Billing_Firstname") = cStr(billing_firstname)
        .Fields("Billing_Lastname") = cStr(billing_lastname)
        .Fields("Billing_Address1") = cStr(billing_address1)
        .Fields("Billing_Address2") = cStr(billing_address2)
        .Fields("Billing_City") = cStr(billing_city)
        .Fields("Billing_State") = cStr(billing_state)
        .Fields("Billing_Zip") = cStr(billing_zip)
        .Fields("Billing_Country") = cStr(billing_country)
        .Fields("Billing_Phone") = cStr(billing_phone)
        .Fields("Billing_Email") = cStr(billing_email)
        .Fields("Billing_Type") = cStr(billing_type)
        .Fields("password") = cStr(pw1)
        .Fields("signup_ip") = cStr(request.ServerVariables("REMOTE_HOST") & ":" & request.ServerVariables("REMOTE_ADDR"))
        .Fields("type") = "consumer"
        .Update        
        CustId = .Fields("ID")
    end with

    'closing the connection
    dbConn.close
    set dbConn=nothing
    set rsNt = nothing

In the above example CustId will have the value of the newly created record's ID.

It is also worth noting you should always use this method to add or edit database tables rather than use SQL UPDATE or INSERT commands for code requiring user input. If you use SQL then security could be compromised See Below:

Code:

Dim SSN as String
Dim SqlQuery as String

SSN = Request.QueryString("SSN")
SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"

could be compromised like this:

Code:

' URL to the page containing the above code
listauthordetails.aspx?SSN=172-32-9999';DROP DATABASE pubs --

' SQL Query executed against the database 
SELECT au_lname, au_fname FROM authors WHERE au_id = '';DROP DATABASE pubs --

the above example would delete the whole DATABASE pubs!