Solved

SQL insert loop

Posted on 2004-08-03
11
822 Views
Last Modified: 2008-01-09
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
Comment
Question by:flow79
11 Comments
 
LVL 13

Author Comment

by:flow79
ID: 11705628
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
 
LVL 7

Expert Comment

by:mantarae
ID: 11705774
which line is line 43?
0
 
LVL 13

Author Comment

by:flow79
ID: 11705850
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
Independent Software Vendors: 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!

 
LVL 13

Author Comment

by:flow79
ID: 11705897
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 11706325
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
 
LVL 13

Author Comment

by:flow79
ID: 11706365
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
 
LVL 7

Expert Comment

by:mantarae
ID: 11706500
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
 
LVL 7

Accepted Solution

by:
mantarae earned 500 total points
ID: 11706543
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
 
LVL 58

Expert Comment

by:Gary
ID: 11706583
Simple solution change all the columns to type text and use 0/1 to indicate no/yes
0
 
LVL 13

Author Comment

by:flow79
ID: 11706607
good ideas from both of you!  I ended up using Mantarae's solution, and it works like a charm.  Thanks guys!

0
 
LVL 7

Expert Comment

by:mantarae
ID: 11706954
Thanks and good luck.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question