Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 5th, 2005, 04:09 PM
Registered User
 
Join Date: Apr 2005
Location: Toronto, ON, Canada.
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.

Thanks,
Mike



Reply With Quote
  #2 (permalink)  
Old August 22nd, 2005, 12:12 AM
Authorized User
 
Join Date: Jan 2005
Location: Mumbai, Maharashtra, India.
Posts: 36
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to askenkray
Default

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
Reply With Quote
  #3 (permalink)  
Old August 22nd, 2005, 01:58 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old November 27th, 2005, 06:57 AM
Registered User
 
Join Date: Nov 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT IDENT_CURRENT('THETABLE') AS myId

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

Reply With Quote
  #5 (permalink)  
Old November 27th, 2005, 12:40 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
Reply With Quote
  #6 (permalink)  
Old April 10th, 2008, 12:20 PM
Registered User
 
Join Date: Apr 2008
Location: , , .
Posts: 2
Thanks: 0
Thanked 1 Time in 1 Post
Default

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!
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:58 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.