Solved

SQL insert loop

Posted on 2004-08-03
11
818 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP VB... 7 99
An ASP query I cannot work out... 2 46
two submit buttons one form 15 75
XML Parsing Classic ASP 5 50
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 information …
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/…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now