Link to home
Start Free TrialLog in
Avatar of Murat Raymond
Murat RaymondFlag for United States of America

asked on

Loop with sql query

I have this update query, it works. However I need to create a loop with it since I have to update 40 rows.
I have tried to do it with a Do Loop, but I don't see how to replace the numbers with the variables.
Take a look below at the query:
Assuming that you have done so, the Numbers that I am talking about are the from the variables: Request("NAMEx") x being the numbers.
The first set of codes are the codes tested without a loop so I know that it works.
The second set of codes is the loop but I believe that it's wrong.
Can somebody please help me with this matter. I am a novice on programming, I do it as a Hobby.
Thanks in advance,
-----------FIRST SET WORKS
<%
   set objConnection=Server.CreateObject("ADODB.Connection")
   objConnection.ConnectionTimeout = 15
   objConnection.CommandTimeout =  10
   objConnection.Mode = 3 'adModeReadWrite 
      if objConnection.state = 0 then
         objConnection.Open Application("DB_Connection_ConnectionString")
      end if
 
      strSQL1 = "UPDATE SYS_GRID " & _
            "SET G_NAME = '" & Request("GRID1") & "', " & _
            "G_CAT = '" & Request("CATGRID1") & "', " & _
            "G_PCT = '" & Request("PCTGRID1") & "', " & _
            "G_TYPE = " & Request("GRIDTYPE1") & " " & _
            "WHERE (G_NUMBER) = 1 ;"
      objConnection.Execute strSQL1
      
      strSQL2 = "UPDATE SYS_GRID " & _
            "SET G_NAME = '" & Request("GRID2") & "', " & _
            "G_CAT = '" & Request("CATGRID2") & "', " & _
            "G_PCT = '" & Request("PCTGRID2") & "', " & _
            "G_TYPE = " & Request("GRIDTYPE2") & " " & _
            "WHERE (G_NUMBER) = 2 ;"
      objConnection.Execute strSQL2
   objConnection.Close
%>
------------------SECOND SET LOOKS WRONG AND I GET NO ANSWER
 
<%
   Dim Gcount
   Gcount=1
   set objConnection=Server.CreateObject("ADODB.Connection")
   objConnection.ConnectionTimeout = 15
   objConnection.CommandTimeout =  10
   objConnection.Mode = 3 'adModeReadWrite 
   
   DO Until Gcount = 41
      if objConnection.state = 0 then
         objConnection.Open Application("DB_Connection_ConnectionString")
      end if
 
      strSQL = "UPDATE SYS_GRID " & _
            "SET G_NAME = '" & Request("GRID" & Gcount & "") & "', " & _
            "G_CAT = '" & Request("CATGRID" & Gcount & "") & "', " & _
            "G_PCT = '" & Request("PCTGRID" & Gcount & "") & "', " & _
            "G_TYPE = " & Request("GRIDTYPE" & Gcount & "") & " " & _
            "WHERE (G_NUMBER) = " & Gcount & " ;"
      objConnection.Execute strSQL
      Gcount=Gcount+1
   Loop   
   objConnection.Close
%>

Open in new window

Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi

try to take the objConnection.open out of the loop.

if it did not work,try to change to change
DO Until Gcount =  41
to
DO Until Gcount =  2
and check if it works.
<%
   Dim Gcount
   Gcount=1
   set objConnection=Server.CreateObject("ADODB.Connection")
   objConnection.ConnectionTimeout = 15
   objConnection.CommandTimeout =  10
   objConnection.Mode = 3 'adModeReadWrite 
   
   if objConnection.state = 0 then
      objConnection.Open Application("DB_Connection_ConnectionString")
   end if
   DO Until Gcount = 41
       strSQL = "UPDATE SYS_GRID " & _
            "SET G_NAME = '" & Request("GRID" & Gcount & "") & "', " & _
            "G_CAT = '" & Request("CATGRID" & Gcount & "") & "', " & _
            "G_PCT = '" & Request("PCTGRID" & Gcount & "") & "', " & _
            "G_TYPE = " & Request("GRIDTYPE" & Gcount & "") & " " & _
            "WHERE (G_NUMBER) = " & Gcount & " ;"
      objConnection.Execute strSQL
      Gcount=Gcount+1
   Loop   
   objConnection.Close
%>

Open in new window

Avatar of Murat Raymond

ASKER

I did That was my first Try I always try wih a smaller Number befor I go crazy.If I Take the objConnection.open out, how will the Database connection be open so it can write to the table?
Remember,
The second set of codes is what i replace the First set of codes with as a loop instead of keep going 3, 4, 5...,40 on the first loop.

They are not both on the page.
I believe that my mistake is here: '" & Request("GRID" & Gcount & "") & "'
Can you actually put a variable into a variable?
Avatar of Göran Andersson
Don't use the Request collection. Specify if you want to read from Request.Cookies, Request.ServerVariables, Request.Form or Request.QueryString.

I don't understand why you build a for loop without using the For command...

You don't have to open the database connection more than once. In a web application the code runs for so short times that the connection never times out.

Your queries are wide open for SQL injection attacks. You should sql encode your string values, and you should verify your numeric values. I have assumed that you are using an MS Access or MS SQL database, and that the grid type is a numeric value.

A few superflous things like empty strings and extra parentheses in the SQL, other than that it look alright.
<%
   Dim Gcount
   set objConnection=Server.CreateObject("ADODB.Connection")
   objConnection.ConnectionTimeout = 15
   objConnection.CommandTimeout =  10
   objConnection.Mode = 3 'adModeReadWrite 
   objConnection.Open Application("DB_Connection_ConnectionString")
   
   For Gcount = 1 to 40
 
      strSQL = "UPDATE SYS_GRID " & _
            "SET G_NAME = '" & SqlEncode(Request.Form("GRID" & Gcount)) & "', " & _
            "G_CAT = '" & SqlEncode(Request.Form("CATGRID" & Gcount)= & "', " & _
            "G_PCT = '" & SqlEncode(Request.Form("PCTGRID" & Gcount)) & "', " & _
            "G_TYPE = " & CLng(Request.Form("GRIDTYPE" & Gcount)) & " " & _
            "WHERE G_NUMBER = " & Gcount
      objConnection.Execute strSQL
   Next   
   objConnection.Close
 
' Note: ONLY for MS Access / MS SQL databases,
' other databases needs other implementation
Function SqlEncode(byval str)
   SqlEncode = Replace(str, "'", "''")
End Function
%>

Open in new window

> I believe that my mistake is here: '" & Request("GRID" & Gcount & "") & "'
> Can you actually put a variable into a variable?

It's not a variable in a variable. The "GRID" string is concatenated with the string representation of Gcound to form a string like "GRID1". This is used to access the Request.Form collection (don't use Request) just like when you access it with a literal string. The only difference is how you construct the string.
Oops. The line

"G_CAT = '" & SqlEncode(Request.Form("CATGRID" & Gcount)= & "', " & _

should of course be

"G_CAT = '" & SqlEncode(Request.Form("CATGRID" & Gcount)) & "', " & _
below is your codes revised,
I corected "WHERE G_NUMBER = " & Gcount           to     "WHERE G_NUMBER = " & Gcount & ";
And  G_CAT as you suggested
I also use a smaller number just in case.
As you asked they are not all Numbers
GRIDx  and CATGRIDx could be anything no constraints
PCTGRIDx = Number
GRIDTYPEx = True or False
It didn't work.
I am not seing any detail on the error like I use to with IE 6.
I just say "HTTP 500 Internal Server Error"
<%
   Dim Gcount
   set objConnection=Server.CreateObject("ADODB.Connection")
   objConnection.ConnectionTimeout = 15
   objConnection.CommandTimeout =  10
   objConnection.Mode = 3 'adModeReadWrite 
   objConnection.Open Application("DB_Connection_ConnectionString")
   
   For Gcount = 1 to 2
 
      strSQL = "UPDATE SYS_GRID " & _
            "SET G_NAME = '" & SqlEncode(Request.Form("GRID" & Gcount)) & "', " & _
            "G_CAT = '" & SqlEncode(Request.Form("CATGRID" & Gcount))= & "', " & _
            "G_PCT = '" & SqlEncode(Request.Form("PCTGRID" & Gcount)) & "', " & _
            "G_TYPE = " & CLng(Request.Form("GRIDTYPE" & Gcount)) & " " & _
            "WHERE G_NUMBER = " & Gcount & ";
      objConnection.Execute strSQL
   Next   
   objConnection.Close
 
' Note: ONLY for MS Access / MS SQL databases,
' other databases needs other implementation
Function SqlEncode(byval str)
   SqlEncode = Replace(str, "'", "''")
End Function
%>

Open in new window

> I corected "WHERE G_NUMBER = " & Gcount           to     "WHERE G_NUMBER = " & Gcount & ";

Corrected... Right... ;)

Just count the number of quotation marks on that line, and you see that they don't match any more. Change that  back.

> PCTGRIDx = Number

Ok, that's another problem. You try to store it as a string in the database. Change the line to:

           "G_PCT = " & CLng(Request.Form("PCTGRID" & Gcount)) & ", " & _

> I just say "HTTP 500 Internal Server Error"

You do? ;)

In the advanced settings of the browser, disable the "show friendly http error message" setting.
The query works GreenGhost
Below is what works:
However G_TYPE is True or False Field
The form post it as ON
When checked  ON (True)
I get the following:

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'Clng'
/Grid_Update.asp, line 12

Line 12 Being the query line


strSQL = "UPDATE SYS_GRID " & _
            "SET G_NAME = '" & SqlEncode(Request.Form("GRID" & Gcount)) & "', " & _
            "G_CAT = '" & SqlEncode(Request.Form("CATGRID" & Gcount)) & "', " & _
            "G_PCT = '" & SqlEncode(Request.Form("PCTGRID" & Gcount)) & "', " & _
            "G_TYPE = " & Clng(Request.Form("GRIDTYPE" & Gcount)) & " " & _
            "WHERE G_NUMBER = " & Gcount 

Open in new window

If you remove the Clng call, it should work:

"G_TYPE = " & Request.Form("GRIDTYPE" & Gcount) & " " & _

You should however be aware that inserting unverified data directly from user input into the SQL query opens up for SQL injections. By sending a modified request to your page, anyone could do any changes they like to your database, or even delete the tables int it.
I just notice something,
If everything is checked as true or false it works,
But if I have a true and a false
then I get an error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC dBase Driver] Syntax error in UPDATE statement.
/Grid_Update.asp, line
And what is the way around the warning you gave me "SQL Injections".


ASKER CERTIFIED SOLUTION
Avatar of Göran Andersson
Göran Andersson
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you so Much for the advice, expertise and patience, It worked.