Reading and writing data to and from a database

This code sample shows how to read data from a database using adodb on an ASP page.


    ' --- the code to serahc for, can be obtained like:
    s_findme = "124119"   ' --- direct string
    s_findfme = Request.Querystring("urlparm")  ' --- an url parameter
    s_findme= Request.form("formfield")  ' --- a form field

    ' --- Setup a connection to the sql server
    Set myconnection = Server.CreateObject("ADODB.Connection")
    myconnection.open "DSN=mydsname;UID=sqlusername;PWD=sqlpassword;DATABASE=mydatabasename"

    ' --- Find a mail address based on customer code
    set rstemail = server.CreateObject("ADODB.Recordset")
    rstemail.open "SELECT * FROM adreslist WHERE customercode='" & s_findme & "'",myconnection
    if rstemail.eof=false then  ' --- there is data in the set
        string_email = rstemail("emailfield")   '  --- Retrieve the field value
    else
        string_email= ""
    end if
    rstemail.close

The next section shows how to insert data into a datatable using the same method. Altough you will see many examples with direct SQL statements, the vast advantage of the method below is that you do not have to worry about special characters and escaping them in hidious ways. All handling is done through objects, so is completely transparant to anything special you may have in your fields.

    ' --- Submit data to the database
   
    ' --- Setup connection
    Set myconnection = Server.CreateObject("ADODB.Connection")
    myconnection.open "DSN=mydsnaname;UID=sqlusername;PWD=sqlpassword;DATABASE=mydatabase"
   
    ' --- create a recordset object
    set myrst = server.CreateObject("ADODB.Recordset")
    myrst.open "tablenamehere",myconnection,1,3
   
    myrst.addnew
    myrst("somefield")=s_somevalue
    myrst("someotherfield")=s_someothervalue
   
    myrst.update
    myrst.close
    myconnection.close