Solved

SQL insert loop

Posted on 2004-08-03
11
817 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
Comment Utility
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
Comment Utility
which line is line 43?
0
 
LVL 13

Author Comment

by:flow79
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 13

Author Comment

by:flow79
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Simple solution change all the columns to type text and use 0/1 to indicate no/yes
0
 
LVL 13

Author Comment

by:flow79
Comment Utility
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
Comment Utility
Thanks and good luck.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

17 Experts available now in Live!

Get 1:1 Help Now