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

LVL 15
vico1CIOAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Göran AnderssonConnect With a Mentor Commented:
Is it an input with type="checkbox" that you have in your form? Then you have to read the value in a different way. A checkbox only sends a value when it's checked, otherwise no value at all is sent for the field. Check if there is a value, and set a variable accordingly:

If IsEmpty(Request.Form("GRIDTYPE" & Gcount)) Then
   strGridValue = "0"
Else
   strGridValue = "1"
End If

Now you can use the variable in the query.

> And what is the way around the warning you gave me "SQL Injections".

To always verify all data. Strings and numbers can be handled as I showed earlier, and checkboxes can be handled as I showed above.
0
 
ee_rleeCommented:
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

0
 
vico1CIOAuthor Commented:
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?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Göran AnderssonCommented:
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

0
 
Göran AnderssonCommented:
> 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.
0
 
Göran AnderssonCommented:
Oops. The line

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

should of course be

"G_CAT = '" & SqlEncode(Request.Form("CATGRID" & Gcount)) & "', " & _
0
 
vico1CIOAuthor Commented:
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

0
 
Göran AnderssonCommented:
> 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.
0
 
vico1CIOAuthor Commented:
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

0
 
Göran AnderssonCommented:
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.
0
 
vico1CIOAuthor Commented:
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".


0
 
vico1CIOAuthor Commented:
Thank you so Much for the advice, expertise and patience, It worked.
0
All Courses

From novice to tech pro — start learning today.