Problems with loops and SQL statements...using variables

loops, SQL, variables, ASP

[n8thegr898]

I'm trying to create a loop that will pull the responses from a form on an ASP and then update my database accordingly. However, I'm running into two spots that are causing problems in the loop working (you'll notice they are in bold).

[1]
Dim UserResponse, currentquestion, TotalQuestions, StatCounter
TotalQuestions = Request.Form("TotalQuestions")
currentquestion = 0

[/1]

[1]
Do While (currentquestion) < [b]TotalQuestions[/b]
currentquestion = currentquestion + 1
StatCounter = 0
UserResponse = Request.Form(Cstr(currentquestion))
StatsSQL = "SELECT * FROM Stats WHERE QuestionNumber = '" & (currentquestion) & "'" 
Set StatsRS = MyConn.Execute(StatsSQL)
StatCounter = StatsRS(UserResponse) + 1
StatUpdate = "UPDATE Stats SET [b]'" & UserResponse & "'[/b] = '" & StatCounter & "' WHERE (Stats.QuestionNumber) = '" & currentquestion & "';"
MyConn.Execute StatUpdate
Loop

[/1]

For those two spots, I'm trying to use variables...and it doesn't work (apparently I'm trying incorrectly) However, if I replace the variables with the actual values, like this:

[1]
Do While (currentquestion) < [b]5[/b]
StatUpdate = "UPDATE Stats SET [b]QuestionValue[/b] = '" & StatCounter & "' WHERE (Stats.QuestionNumber) = '" & currentquestion & "';"

[/1]

Then it'll work. So, I'm wondering if there is a way to fix those two areas so I can use my variables and not a constant.

Also, if anyone has any suggestions on ways to "clean up" my code, I'd be more than willing to hear your suggestions. For example, I would like to know if there is a way to eliminate my first SQL statement by using my UPDATE SQL statement to just add 1 to the specific field. Thanks in advance for your help!

[duckhunter]

First, remove the parenthesis from around the variable name. Then it looks like your update statement did not have the fieldname (I assume QuestionValue as your field name since the changed SQL worked)

Dim UserResponse, currentquestion, TotalQuestions, StatCounter
TotalQuestions = Request.Form("TotalQuestions")
currentquestion = 0

Do While currentquestion < TotalQuestions
currentquestion = currentquestion + 1
StatCounter = 0
UserResponse = Request.Form(Cstr(currentquestion))
StatsSQL = "SELECT * FROM Stats WHERE QuestionNumber = '" & currentquestion & "'"
Set StatsRS = MyConn.Execute(StatsSQL)
StatCounter = StatsRS(UserResponse) + 1
StatUpdate = "UPDATE Stats SET QuestionValue = '" & StatCounter & "' WHERE (Stats.QuestionNumber) = '" & currentquestion & "';"
MyConn.Execute StatUpdate
Loop

[n8thegr898]

Thanks for your response! Unfortunately, those didn't work. When I change the first part to:

[1]Do While currentquestion < TotalQuestions

[/1]

The page apparently gets stuck in a loop because I get an error that "The maximum amount of time for a script to execute was exceeded." I know that the TotalQuestions variable is 5, because if I do a Response.Write(TotalQuestions)...it will output a 5. And, it's not the parenthesis because the code will work if it put either:

[1]Do While (currentquestion) < 5 or Do While currentquestion < 5

[/1]

So, it's something with the the code not interpreting the TotalQuestions variable correctly. Second, the suggestion for setting the field name is not quite what I want to do. I want to use a variable for the field name because the field I update the data in will depend on what information I'm pulling from the form. That's why I want to use a variable instead of a constant. Does that make sense? I really do appreciate your help though...any other ideas?

[wardbekker]

Do this:

TotalQuestions = Clng(Request.Form("TotalQuestions"))

[n8thegr898]

Sweet! Wardbekker, once again, you've come to my rescue. Any ideas on the second half...the part of using a variable in an SQL statement? Let me know if anyone needs more of an explaination on what I'm trying to do...

[aspdaddy]

Good advice there, if you are using a form value for a loop counter, always cast it to number, to be sure use isNumeric() first as well.

You could make it much simpler , for questionaires (if that is was this is?) I usually use code like this:

For Each Question in Request.Form
if Question <> "Submit"

strAnswer =
replace(trim(Request((Question)),"'","''")

strSQL="INSERT INTO tblResults (Question, Answer) VALUES ('" & Question & "', '" & strAnswer & "')"

[n8thegr898]

Not sure if that's quite what I want to do. I'll try to explain my project a little more. I'm creating a little application (2 pages - one for the questions and one for the results) On the first page, there are questions with multiple choices (some have just two choices (T/F) while others have 6). Each question has a group of radio buttons given a name starting at 1 and auto-incrementing...which also is the same as the question number. For each possible answer, the values are "Response1", "Response2"..."Response6".

So, when they submit the form, I would like to update a statistics table that tells me how many people answered which of the question. My stats table is called "Stats" and the Fields are QuestionNumber, Response1, Response2, Response3, Response4, Response5, Response6.

So, I would like to make an SQL statement that will loop and go down the questions from the form and first get the question number (the name of the radio buttons) (i.e. 1) and also find out which response they picked (i.e. Response2). Then, it will pull the record that has 1 as the value of the QuestionNumber and then find the Response2 field and update that value by 1. Then, I want it to go back to the loop and tally up the rest of the responses.

Hope this helps some more. Once again, thanks so much for everyone's help...I really appreciate it as I'm learning all this!

[aspdaddy]

n8thegr898,

Nothing wrong with your approach at all, I was just pointing out what I see as a simpler way.

There are many ways of designing questionires in asp. I get asked to do a lot of these and they are allways totally different :(

So I designed a simple db and some code that is very short, and then only modify the front end html for each new questionaire.

If you name the radio groups same as the actual questions, and the values same as the actual answers, you can report simply like this:

SELECT Answer, count(Answer) as Total
FROM tblResults
WHERE Question = <Your Question>
GROUP BY Answer

Good luck :)