Murat Raymond
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,
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
%>
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?
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?
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.
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
%>
> 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.
> 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("CA TGRID" & Gcount)= & "', " & _
should of course be
"G_CAT = '" & SqlEncode(Request.Form("CA TGRID" & Gcount)) & "', " & _
"G_CAT = '" & SqlEncode(Request.Form("CA
should of course be
"G_CAT = '" & SqlEncode(Request.Form("CA
ASKER
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"
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
%>
> 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.
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
> 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.
ASKER
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
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
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.
"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.
ASKER
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".
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so Much for the advice, expertise and patience, It worked.
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.
Open in new window