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
Murat RaymondCIOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Murat RaymondCIOAuthor 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
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murat RaymondCIOAuthor Commented:
Thank you so Much for the advice, expertise and patience, It worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.