Multiple Insert Statements

tp194 · June 7th, 2003, 09:33 PM

I'm hopeing someone can help me...I'm trying to put multiple SQL INSERT statements into a database with one connection. When I do each INSERT on it's own, it works fine, but when I combine the two I get this error message:

ADODB.Command (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

The code is below, and thanks in advance! Terry

<%
    Dim strCompanyName, strAddress1, strAddress2, strCity, strState, intZip, intPhoneNum, intFaxNum, strWebSite, intEin, intDuns, strStaff, intNaics, strBusStructure, strBusType, strProducts, strOwnership1, strOwnership2, strOwnership3, strOwnership4, strOwnership5, strOwnership6, strBusSize, strRegionalCouncil, strRegionalCouncilName, strCityAgency, strCityAgencyName, strStateAgency, strStateAgencyName, strMinCategory, strProNet, strHubZone, strSdb, strAgree1, strAgree2, strSignerName, strBusTitle, strEmailAddress, dateEntered

    strCompanyName = Request.Form("strCompanyName")
    strAddress1 = Request.Form("strAddress1")
    strAddress2 = Request.Form("strAddress2")
    strCity = Request.Form("strCity")
    strState = Request.Form("strState")
    intZip = Request.Form("intZip")
    intPhoneNum = Request.Form("intPhoneNum")
    intFaxNum = Request.Form("intFaxNum")
    strWebSite = Request.Form("strWebSite")
    intEin = Request.Form("intEin")
    intDuns = Request.Form("intDuns")
    strStaff = Request.Form("strStaff")
    intNaics = Request.Form("intNaics")
    strBusStructure = Request.Form("strBusStructure")
    strBusType = Request.Form("strBusType")
    strProducts = Request.Form("strProducts")
    strOwnership1 = Request.Form("strOwnership1")
    strOwnership2 = Request.Form("strOwnership2")
    strOwnership3 = Request.Form("strOwnership3")
    strOwnership4 = Request.Form("strOwnership4")
    strOwnership5 = Request.Form("strOwnership5")
    strOwnership6 = Request.Form("strOwnership6")
    strBusSize = Request.Form("strBusSize")
    strRegionalCouncil = Request.Form("strRegionalCouncil")
    strRegionalCouncilName = Request.Form("strRegionalCouncilName")
    strCityAgency = Request.Form("strCityAgency")
    strCityAgencyName = Request.Form("strCityAgencyName")
    strStateAgency = Request.Form("strStateAgency")
    strStateAgencyName = Request.Form("strStateAgencyName")
    strMinCategory = Request.Form("strMinCategory")
    strProNet = Request.Form("strProNet")
    strHubZone = Request.Form("strHubZone")
    strSdb = Request.Form("strSdb")
    strAgree1 = Request.Form("agree1")
    strAgree2 = Request.Form("agree2")
    strSignerName = Request.Form("signerName")
    strBusTitle = Request.Form("busTitle")
    strEmailAddress = Request.Form("emailAddress")
    dateEntered = Request.Form("dateEntered")
%>

<%
    Dim objCommand, objCommand2

    'CREATE A COMMAND OBJECT
    Set objCommand = Server.CreateObject("ADODB.Command")

    'CREATE A CONNECTION TO DATABASE
    objCommand.ActiveConnection = strConnect

    'ADD A COMMANDTEXT (SQL STATMENT)
    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)"

    'INDICATE EXECUTE PARAMETERS
    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

    objCommand.Execute

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

    objCommand2.CommandText = "INSERT INTO linkToCategory(ein, categoryId) " & _
                              "VALUES (intEIN, strProducts)"
    objCommand2.CommandType = adCmdText

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

    objCommand2.Execute

Imar · June 8th, 2003, 07:27 AM

Hi there,

I think this is caused by the fact that you are not assigning a connection to the ActiveConnection property of the second command.

To be sure you are reusing the connection, define one explicitly. By assigning a connection string instead of a real connection (at least that's what strConnect seems to imply) a connection is created implicitly / behind the scenes. IMO, it's better to Dim and Set your own connection, open it using the strConnect string, and then assign it to both the Command objects. Alternatively, reset the current Command object and reuse it. Makes your code a bit cleaner as you only have to declare one Command.

HtH

Imar

tp194 · June 8th, 2003, 09:28 PM

Imar

Thanks, that worked perfectly. :D Can I ask one more question?

I have an html form with a selection box, where the user can choose multiple options using the ctrl key. Each one of these options needs to go into it's own row in the database...the sql statement is the second sql statement in the code above.

How can I itereate through all selected options into a generic sql statement to enter all selected options into the database?

Thanks

Terry

Imar · June 9th, 2003, 03:58 AM

Hi Terry,

It depends on the database you are using. For example, SQL Server supports multiple INSERT statements at once, separated by a ;. Access doesn't understand this (if I am not mistaken) so you need to execute each individual SQL statement.

Here's the general idea. Suppose we have a listbox called lstGroups and for the user ID 5 we want to add a record in the database for each group that was selected:

Code:

' Multiple SQL statements solution:

Dim SelectedGroup
Dim UserID
Dim SQL
UserID = 5 ' Usually you would get this from a from, session etc.

For Each SelectedGroup In Request.Form("lstGroups")

    SQL = SQL & "INSERT INTO UserGroup(UserID, GroupID) " _
        VALUES(" & UserID & ", " & SelectedGroup & ");" & vbCrLf

Next

' SQL now contains the INSERT statement for all selected groups.
' Then use a command or a connection to execute your SQL statement 
' here like you did in the code you already posted.

' Close and clean up the connection / command object(s) here

The other solution could look like this

Code:

' Single SQL statements solution:
Dim SelectedGroup
Dim UserID
Dim SQL
UserID = 5 ' Usually you would get this from a form, session etc.

' Create and open a connection here

For Each SelectedGroup In Request.Form("lstGroups")
    SQL = "INSERT INTO UserGroup(UserID, GroupID) " _
        VALUES(" & UserID & ", " & SelectedGroup & ")"

    ' Use a command or a connection to execute your SQL 
    ' statement here. For example:
    ' MyConnection.Execute(SQL)

Next

' Close and clean up the connection / command object(s) here

HtH

Imar

tp194 · June 9th, 2003, 11:02 PM

Imar,

I tried to use the single SQL statement version and now I get this error message: "Number of query values and destination fields are not the same."

I can't figure this out...thanks for your help. The revised code is below.

Terry

Dim EIN
    EIN = intEin

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

    objCommand2.ActiveConnection = strConnect

    For Each selectedGroup In Request.Form("strProducts")

    objCommand2.CommandText = "INSERT INTO linkToCategory(ein, categoryId) " & _
                              "VALUES (" & EIN & ", " & selectedGroup &")"
    objCommand2.CommandType = adCmdText

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

    objCommand2.Execute

    Next

    Set objCommand2 = Nothing

KenSchaefer · June 10th, 2003, 12:41 AM

You need to comment out the bit where you append the parameters. When you have a straight SQL string that you're sending to the database, there are no parameters. At the moment, the DB engine is complaining because you are sending parameters, and it doesn't know what to do with them...

Cheers
Ken

www.adOpenStatic.com

Imar · June 10th, 2003, 01:25 AM

Hi Terry,

You should no longer use the Command parameters, as the SQL INSERT string contains all the info that is needed (ein, categoryId as fieldsnames and EIN, selectedGroup as values).

This is an either / or situation. Either supply the command object the required parameters and values _OR_ build a full custom SQL string and execute it directly. You are using the custom SQL here, so drop the parameers.

Imar

tp194 · June 10th, 2003, 08:25 PM

Thanks guys, I commented out the parameters and I'm still getting this error message "Number of query values and destination fields are not the same."

Sorry for dragging this out, but it's really frustrating! :(

t~

    Dim EIN
    EIN = intEin

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

    objCommand2.ActiveConnection = strConnect

    For Each selectedGroup In Request.Form("strProducts")

    objCommand2.CommandText = "INSERT INTO linkToCategory(ein, categoryId) " & _
                              "VALUES (" & EIN & ", " & selectedGroup &")"
    objCommand2.CommandType = adCmdText

    'objCommand2.Parameters("strProducts") = strProducts
    'objCommand2.Parameters("intEin") = intEin

    objCommand2.Execute

    Next

    Set objCommand2 = Nothing

Imar · June 11th, 2003, 01:26 AM

Hi,

If you are not using the Command object for parameters, you might as well use the Execute method of the Connection object directly:

Code:

Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open YourConnectionString
For Each .....
    Conn.Execute(MySQLStatement)
Next
Conn.Close
Set Conn = Nothing

For your current problem, are you sure the datatypes for the table linkToCategory are both a int (That is, they don't require apostropes around them?)?

If that doesn't help, do a Response.Write of

Code:

"INSERT INTO linkToCategory(ein, categoryId) " & _
"VALUES (" & EIN & ", " & selectedGroup &")"

(or assign it to a variable first) and then see if all fields contain a valid value.

Cheers,

Imar