Solved

SQL insert loop

Posted on 2004-08-03
11
824 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

688 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