#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    8
    Rep Power
    0

    Mutliple Insert Statments


    Hi

    I'm using ASP to insert data into an Access database from an online form. Elements of the form need to be updated into multiple tables, and I was wondering if I could do this with one connection.

    I'm kinda new to ASP so any help would be awesome. My code is below and only includes the first sql insert statment:


    Dim objCommand, objRS, objParam

    Set objCommand = Server.CreateObject("ADODB.Command")


    objCommand.ActiveConnection = strConnect
    objCommand.CommandText = "INSERT INTO vendorTable(companyName, address1, address2, city, state, zip, phoneNum, faxNum, webSite, ein, duns, staff, naics, busStructure, busType, busSize, regionalCouncil, regionalCouncilName, cityAgency, cityAgencyName, stateAgency, stateAgencyName, minCategory, proNet, hubZone, sdb, agree1, agree2, signerName, busTitle, emailAddress, dateEntered) " & _
    "VALUES (strCompanyName, strAddress1, strAddress2, strCity, strState, intZip, intPhoneNum, intFaxNum, strWebSite, intEin, intDuns, strStaff, intNaics, strBusStructure, strBusType, strBusSize, strRegionalCouncil, strRegionalCouncilName, strCityAgency, strCityAgencyName, strStateAgency, strStateAgencyName, strMinCategory, strProNet, strHubZone, strSdb, strAgree1, strAgree2, strSignerName, strBusTitle, strEmailAddress, dateEntered)"

    objCommand.CommandType = adCmdText

    objCommand.Parameters("strCompanyName") = strCompanyName
    objCommand.Parameters("strAddress1") = strAddress1
    objCommand.Parameters("strAddress2") = strAddress2
    objCommand.Parameters("strCity") = strCity
    objCommand.Parameters("strState") = strState
    objCommand.Parameters("intZip") = intZip
    objCommand.Parameters("intPhoneNum") = intPhoneNum
    objCommand.Parameters("intFaxNum") = intFaxNum
    objCommand.Parameters("strWebSite") = strWebSite
    objCommand.Parameters("intEin") = intEin
    objCommand.Parameters("intDuns") = intDuns
    objCommand.Parameters("strStaff") = strStaff
    objCommand.Parameters("intNaics") = intNaics
    objCommand.Parameters("strBusStructure") = strBusStructure
    objCommand.Parameters("strBusType") = strBusType
    objCommand.Parameters("strBusSize") = strBusSize
    objCommand.Parameters("strRegionalCouncil") = strRegionalCouncil
    objCommand.Parameters("strRegionalCouncilName") = strRegionalCouncilName
    objCommand.Parameters("strCityAgency") = strCityAgency
    objCommand.Parameters("strCityAgencyName") = strCityAgencyName
    objCommand.Parameters("strStateAgency") = strStateAgency
    objCommand.Parameters("strStateAgencyName") = strStateAgencyName
    objCommand.Parameters("strMinCategory") = strMinCategory
    objCommand.Parameters("strProNet") = strProNet
    objCommand.Parameters("strHubZone") = strHubZone
    objCommand.Parameters("strSdb") = strSdb
    objCommand.Parameters("strAgree1") = strAgree1
    objCommand.Parameters("strAgree2") = strAgree2
    objCommand.Parameters("strSignerName") = strSignerName
    objCommand.Parameters("strBusTitle") = strBusTitle
    objCommand.Parameters("strEmailAddress") = strEmailAddress
    objCommand.Parameters("dateEntered") = dateEntered




    Set objRS = objCommand.Execute
  2. #2
  3. No Profile Picture
    I am here to share knowledge
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    India
    Posts
    83
    Rep Power
    11
    Yup pal you can do it
    Conn is your connection variable
    sql="Insert into table1"
    Conn.Execute(sql)

    sql1="Insert into table2"
    Conn.Execute(sql1)

    sql2="Insert into table3"
    Conn.Execute(sql2)

    This above way you can do it, but all the tables must have in the same database, if not then you have open a new connection.

    Rahul
    Rahul

    Small things lead to perfection and perfection is not a small thing.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    8
    Rep Power
    0
    Rahul

    Thanks for the reply...it works like a dream. But this leads me to another question:

    The info for the database is being taken from an html form that the user fills out. One of the fields is a select box where the user can choose multiple options. Right now, the way the sql is set up, only the first option gets inputed into the table.

    Any ideas on how I can get all selections into the table?

    The code is below:

    THE HTML IS:

    <select name="products" size=6 multiple>
    <option value="select" selected>Select all that apply (Use Ctrl for multiple selections)</option>
    <option value="5400">Animal Related Supplies - 5400</option>
    <option value="7500">Appliances - 7500</option>
    <option value="3800">Athletic - 3800</option>
    <option value="6700">Audio Visual - 6700</option>
    <option value="6100">Batteries (Industrial) - 6100</option>
    <option value="5800">Chemiclas &amp; Gases - 5800</option>
    ...

    AND THE ASP/SQL IS:

    Set objCommand2 = Server.CreateObject("ADODB.Command")

    'CREATE A CONNECTION TO DATABASE
    objCommand2.ActiveConnection = strConnect

    'ADD A COMMANDTEXT (SQL STATMENT)
    objCommand2.CommandText = "INSERT INTO linkToCategory(ein, categoryId) " & _
    "VALUES (intEIN, strProducts)"

    'INDICATE EXECUTE PARAMETERS
    objCommand2.CommandType = adCmdText

    objCommand2.Parameters("strProducts") = strProducts
    objCommand2.Parameters("intEIN") = intEIN

    objCommand2.Execute
  6. #4
  7. No Profile Picture
    I am here to share knowledge
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    India
    Posts
    83
    Rep Power
    11
    Hello pal
    pleased to know that your issue has been resolved, with this issue i can help you but u need to have a proper concept of array handling in ASP

    this is waht you can do and ur issue will be resolved

    you listbox name is products right and the maximum value can be selected from it is 6 ok

    dim strproduct, arrX, temp

    /first get all the values in a single array variable
    strproduct=Request.Form("products")
    /now eliminate the , and space between the values
    arrX=split(strproduct, ", ")
    temp=0

    do while temp <> 6

    sql=" Insert into tablename(field1, field2) values (firstvalue, arrX(temp))"
    Conn.Execute("sql")

    temp=temp+1

    loop

    try this if you are having any other issue let me know

    Regards,
    Rahul
    Rahul

    Small things lead to perfection and perfection is not a small thing.

IMN logo majestic logo threadwatch logo seochat tools logo