• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 830
  • Last Modified:

SQL insert loop

hello all!

I currently have a page on which the user can click a series of checkboxes, and then submit.  This page sends its data to another asp page and on this page the part number is extracted so that I can use it for SQL inserts.  However, I would like to loop the inserts (so i dont have to type 1000's of line of SQL insert codes).

What I have currently (but doesnt perform the insert is:

<%

Set oConnA=Server.CreateObject("ADODB.Connection")

 oConnA.Open "DRIVER={Microsoft Access Driver (*.mdb)};" &_
       "DBQ=c:\inetpub\wwwroot\CTC Plant Air Orders.mdb;"

Model = "TA2000"
Total = Request.Form("numTotal")
response.write "<td><b>Total Price</b></td><td>"
response.write (formatcurrency(Total))
response.write "</td>"
%>
<%
if Model = "TA2000" then

Dim MXID
Dim MXIDER
Dim inTableA
Dim inTableB
SQLA="select Max([Reference ID]) AS MXID from [Basic Requirements]"
set inTableA = oConnA.execute(SQLA)
MXIDER = inTableA("MXID")

for each x in request.form
        if left(x,4)="chk_" then
      partnumber=mid(x,5)
            end if
SQLB="Select dbtable, dbfield, dbvalue from [Part Pricing] WHERE Model='"&Model&"' AND partnumber = '"&partnumber&"' " 
set inTableB = oConnA.execute(SQLA)

                  if partnumber = "& partnumber &"  then
                  SQLB="UPDATE" & dbtable & "SET" & dbfield & " = "& dbvalue &" WHERE [Reference ID] = " & MXIDER
              end if
next
end if
%>

I think the issue is with the "set inTableB = oConnA.execute(SQLB)" line.  I need to be able to access the values of this SQL query and use them in the insert.  Any ideas?  Thanks!
0
flow79
Asked:
flow79
1 Solution
 
flow79Author Commented:
i'm currently trying:
for each x in request.form
' the following is a loop which extracts all the Part Numbers selected on AdderPricing.asp
        if left(x,4)="chk_" then
      partnumber=mid(x,5)
            end if
            
' The following Select statement retrieves the data to use in the below insert loop            
SQLB="Select dbtable, dbfield, dbvalue from [Part Pricing] WHERE Model='"&Model&"' AND partnumber = "&partnumber&" " 
set inTableB = oConnA.execute(SQLA)
dbtabler = inTableB("dbtable")
dbfieldr = inTableB("dbfield")
dbvaluer = inTableB("dbvalue")

                  if partnumber = "& partnumber &"  then
                  SQLB="UPDATE ["&dbtabler&"] SET ["&dbfieldr&"] = '" & dbvaluer &"' WHERE [Reference ID] = " & MXIDER
              end if
next

end if

it still isnt working:  the error it gives me is:

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/AdderInsert2.asp, line 43

0
 
mantaraeCommented:
which line is line 43?
0
 
flow79Author Commented:
line 43 is: dbtabler = inTableB("dbtable")

i'm using:

Dim MXID
Dim MXIDER
Dim inTableA
Dim inTableB
SQLA="select Max([Reference ID]) AS MXID from [Basic Requirements]"
set inTableA = oConnA.execute(SQLA)
MXIDER = inTableA("MXID")

for each x in request.form
' the following is a loop which extracts all the Part Numbers selected on AdderPricing.asp
       if left(x,4)="chk_" then
      partnumber=mid(x,5)
          end if
         
' The following Select statement retrieves the data to use in the below insert loop          
SQLB="Select dbtable, dbfield, dbvalue from [Part Pricing] WHERE Model='"&Model&"' AND partnumber = "&partnumber&" " 
set inTableB = oConnA.execute(SQLA)
dbtabler = inTableB("dbtable")
dbfieldr = inTableB("dbfield")
dbvaluer = inTableB("dbvalue")

               if partnumber = "& partnumber &"  then
               SQLC="UPDATE ["&dbtabler&"] SET ["&dbfieldr&"] = '" & dbvaluer &"' WHERE [Reference ID] = " & MXIDER
                                           Call oConnA.execute(SQLC)
            end if
next

end if

i had forgotten the Call oConnA.execute(SQLC) to run the insert (but it still gives me that error)

Thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
flow79Author Commented:
using:

Dim MXID
Dim MXIDER
Dim inTableA
Dim inTableB
SQLA="select Max([Reference ID]) AS MXID from [Basic Requirements]"
set inTableA = oConnA.execute(SQLA)
MXIDER = inTableA("MXID")

for each x in request.form
' the following is a loop which extracts all the Part Numbers selected on AdderPricing.asp
       if left(x,4)="chk_" then
      partnumber=mid(x,5)
          end if
         
' The following Select statement retrieves the data to use in the below insert loop          
SQLB="Select dbtable, dbfield, dbvalue from [Part Pricing] WHERE Model='"&Model&"' AND partnumber = "&partnumber&" " 
set inTableB = oConnA.execute(SQLA)

               if partnumber = "& partnumber &"  then
               SQLC="UPDATE ["&dbtable&"] SET ["&dbfield&"] = " & dbvalue &" WHERE [Reference ID] = " & MXIDER
                                           Call oConnA.execute(SQLC)
            end if
next

end if

i get no errors, but it also doesnt enter into the database.
0
 
HilaireCommented:
I'm failing to see what th follwong line is supposed to do.
Could you explain what this test is intended for ?

   if partnumber = "& partnumber &"  then
0
 
flow79Author Commented:
i think i got it:
<%
if Model = "TA2000" then

' the following determines the highest Reference ID number so that the data is entered correctly
Dim MXID
Dim MXIDER
Dim inTableA
Dim inTableB
SQLA="select Max([Reference ID]) AS MXID from [Basic Requirements]"
set inTableA = oConnA.execute(SQLA)
MXIDER = inTableA("MXID")

for each x in request.form
' the following is a loop which extracts all the Part Numbers selected on AdderPricing.asp
       if left(x,4)="chk_" then
      partnumber=mid(x,5)
       end if  
         
' The following Select statement retrieves the data to use in the below insert loop          
SQLB="Select dbtable, dbfield, dbvalue from [Part Pricing] WHERE Model='"&Model&"' AND [Part Number] = '"&partnumber&"' " 
set inTableB = oConnA.execute(SQLB)
dbtable = inTableB("dbtable")
dbfield = inTableB("dbfield")
dbvalue = inTableB("dbvalue")

               if partnumber = ""& partnumber &""  then
               SQLE="UPDATE [" &dbtable& "] SET [" &dbfield& "] = '" & dbvalue & "' WHERE [Reference ID] = "&MXIDER
               Call oConnA.execute(SQLE)
            end if
                              
next

end if
%>

the only issue I'm having is with the database field types.

as you can see in my INSERT, I can set it as '" & dbvalue & "' or " & dbvalue & ".  If the database fieldtype is Yes/No, then the syntax must be: " & dbvalue & ", but if the fieldtype is text, then the syntax must be: '" & dbvalue & "'.

What do i do about this?  because some of the checkboxes are related to Yes/No, and some are related to Text.

Thanks!
0
 
mantaraeCommented:
you can add a column dbfieldtype, set it to YesNo or Text.

then you would build your sql statement based upon if it needs those pesky little '' around it or not.
0
 
mantaraeCommented:
Just add the dbfieldtype to your db & set the values for the fields.
Then:

SQLB="Select dbtable, dbfield, dbvalue, dbfieldtype from [Part Pricing] WHERE Model='"&Model&"' AND [Part Number] = '"&partnumber&"' " 
set inTableB = oConnA.execute(SQLB)
dbtable = inTableB("dbtable")
dbfield = inTableB("dbfield")
dbvalue = inTableB("dbvalue")
dbfieldtype = inTableB("dbfieldtype")

if dbfieldtype = "Text" then
    SQLE="UPDATE [" &dbtable& "] SET [" &dbfield& "] = '" & dbvalue & "' WHERE [Reference ID] = "&MXIDER
else
    SQLE="UPDATE [" &dbtable& "] SET [" &dbfield& "] = " & dbvalue & " WHERE [Reference ID] = "&MXIDER
end if
0
 
GaryCommented:
Simple solution change all the columns to type text and use 0/1 to indicate no/yes
0
 
flow79Author Commented:
good ideas from both of you!  I ended up using Mantarae's solution, and it works like a charm.  Thanks guys!

0
 
mantaraeCommented:
Thanks and good luck.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now