Wrox Programmer Forums
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 5th, 2005, 04:09 PM
Registered User
Join Date: Apr 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default ASP Add New Record and Get ID

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.


Old August 22nd, 2005, 12:12 AM
Authorized User
Join Date: Jan 2005
Posts: 36
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to askenkray

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
Old August 22nd, 2005, 01:58 PM
Friend of Wrox
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts

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

Old November 27th, 2005, 06:57 AM
Registered User
Join Date: Nov 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts


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

Old November 27th, 2005, 12:40 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post

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.
Old April 10th, 2008, 12:20 PM
Registered User
Join Date: Apr 2008
Posts: 2
Thanks: 0
Thanked 1 Time in 1 Post

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

    '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
        '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"
        CustId = .Fields("ID")
    end with

    'closing the connection
    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:

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:

' 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!

Similar Threads
Thread Thread Starter Forum Replies Last Post
ADD DB RECORD/ RETURN ID Huascar82 Classic ASP Databases 1 October 1st, 2007 11:55 PM
HELP ME to add a record to a database using ASP kumail Classic ASP Databases 3 August 18th, 2005 05:33 AM
ASP program How to add new ID for new record kinshuk SQL Server ASP 3 March 21st, 2005 02:43 PM
why not index.asp?id=1 can be www.myweb.com/?id=1 BurhanKhan Classic ASP Professional 11 September 6th, 2004 02:06 PM
ASP Insert - Get Record ID llowe Classic ASP Databases 2 March 24th, 2004 11:11 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.