View Single Post
  #6 (permalink)  
Old April 10th, 2008, 12:20 PM
Frye-Guy Frye-Guy is offline
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