Double Insert :: best route?

Discussion in 'ASP General' started by jason, Aug 31, 2003.

  1. jason

    jason Guest

    If one is attempting to insert data into tables at the same time what is the
    best way to do this. I could do it the way below - but is there any reason I
    should not do it this way or perhaps follow a better route. The second
    table is really an audit table to track changes made by the user



    Set cnn = CreateObject("ADODB.Connection")
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
    Server.MapPath("../database/listings.mdb") '//This one is for Access
    2000/2002
    cnn.Open(strCon)

    SQL = "INSERT INTO tblCustomer (email_address, first_name, last_name)
    VALUES("
    SQL=SQL & "'" & email_address & "', "
    SQL=SQL & "'" & first_name & "', "
    SQL=SQL & "'" & last_name & "', "

    cnn.Execute(SQL)


    SQL = "INSERT INTO auditCustomer (email_address, first_name, last_name)
    VALUES("
    SQL=SQL & "'" & email_address & "', "
    SQL=SQL & "'" & first_name & "', "
    SQL=SQL & "'" & last_name & "', "

    cnn.Execute(SQL)
     
    jason, Aug 31, 2003
    #1
    1. Advertising

  2. jason

    Ray at Guest

    Since Access does not support multiple queries in one command, this is
    pretty much what you'll have to do. I personally would build to SQL strings
    separately and execute them right in succession, i.e.

    sSQL = "INSERT INTO tblCustomer (email_address, first_name, last_name)
    VALUES("
    sSQL=sSQL & "'" & email_address & "', "
    sSQL=sSQL & "'" & first_name & "', "
    sSQL=sSQL & "'" & last_name & "', "
    sSQLAudit = Replace(sSQL, "tblCustomer", "auditCustomer")

    Set cnn = CreateObject("ADODB.Connection")
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
    Server.MapPath("../database/listings.mdb") '//This one is for Access
    2000/2002
    cnn.Open(strCon)
    cnn.Execute sSQL
    cnn.Execute sSQLAudit
    cnn.Close
    Set cnn = Nothing


    '''Note that if your SQL contains "tblCustomer" in the actual data, it'll
    get replaced there as well, so perhaps you'll want to build the strings a
    little differently.

    Ray at home

    --
    Will trade ASP help for SQL Server help


    "jason" <> wrote in message
    news:OGZ2Xx%...
    > If one is attempting to insert data into tables at the same time what is

    the
    > best way to do this. I could do it the way below - but is there any reason

    I
    > should not do it this way or perhaps follow a better route. The second
    > table is really an audit table to track changes made by the user
    >
    >
    >
    > Set cnn = CreateObject("ADODB.Connection")
    > strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
    > Server.MapPath("../database/listings.mdb") '//This one is for Access
    > 2000/2002
    > cnn.Open(strCon)
    >
    > SQL = "INSERT INTO tblCustomer (email_address, first_name, last_name)
    > VALUES("
    > SQL=SQL & "'" & email_address & "', "
    > SQL=SQL & "'" & first_name & "', "
    > SQL=SQL & "'" & last_name & "', "
    >
    > cnn.Execute(SQL)
    >
    >
    > SQL = "INSERT INTO auditCustomer (email_address, first_name, last_name)
    > VALUES("
    > SQL=SQL & "'" & email_address & "', "
    > SQL=SQL & "'" & first_name & "', "
    > SQL=SQL & "'" & last_name & "', "
    >
    > cnn.Execute(SQL)
    >
    >
     
    Ray at, Sep 1, 2003
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sridhar Hegde
    Replies:
    2
    Views:
    2,536
    Ajeetha Kumari
    Sep 13, 2004
  2. Divyang M
    Replies:
    2
    Views:
    762
    Divyang M
    May 26, 2005
  3. Sydex
    Replies:
    12
    Views:
    6,528
    Victor Bazarov
    Feb 17, 2005
  4. wallge
    Replies:
    2
    Views:
    1,510
    wallge
    Nov 8, 2006
  5. António Marques

    Best route for object augmentation?

    António Marques, Jun 8, 2006, in forum: Javascript
    Replies:
    2
    Views:
    84
    Matt Kruse
    Jun 8, 2006
Loading...

Share This Page