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

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

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 & 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

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