ASP if loop problem in response.form syntax

[waarbs]

I have a questionnaire that submits all its results to my database. There is no further processing of the information, my client wants to see the database results only.

I have got the code to work, but in an inefficient way, like this:

-----------------------------------------

SQLQuery = "INSERT into questionnaireResults (q1, q2, q3, q4, q5, q6, q7, q8, q9, q10, q11, q12, q13, q14, q15, q16, q17, q18, q19, q20, q21) VALUES "
SQLQuery = SQLQuery & "('"
SQLQuery = SQLQuery & Request.Form("q1") & "','"
SQLQuery = SQLQuery & Request.Form("q2") & "','"
SQLQuery = SQLQuery & Request.Form("q3") & "','"
SQLQuery = SQLQuery & Request.Form("q4") & "','"
SQLQuery = SQLQuery & Request.Form("q5") & "','"
SQLQuery = SQLQuery & Request.Form("q6") & "','"
SQLQuery = SQLQuery & Request.Form("q7") & "','"
SQLQuery = SQLQuery & Request.Form("q8") & "','"
SQLQuery = SQLQuery & Request.Form("q9") & "','"
SQLQuery = SQLQuery & Request.Form("q10") & "','"
SQLQuery = SQLQuery & Request.Form("q11") & "','"
SQLQuery = SQLQuery & Request.Form("q12") & "','"
SQLQuery = SQLQuery & Request.Form("q13") & "','"
SQLQuery = SQLQuery & Request.Form("q14") & "','"
SQLQuery = SQLQuery & Request.Form("q15") & "','"
SQLQuery = SQLQuery & Request.Form("q16") & "','"
SQLQuery = SQLQuery & Request.Form("q17") & "','"
SQLQuery = SQLQuery & Request.Form("q18") & "','"
SQLQuery = SQLQuery & Request.Form("q19") & "','"
SQLQuery = SQLQuery & Request.Form("q20") & "','"
SQLQuery = SQLQuery & Request.Form("q21") & "')"

set RS1 = ObjDbConnection.Execute(SQLQuery)

---------------------------------------------------------------------------

Instead I would like to use a counter in an if loop to execute much of this code in one go. My attempt looks like this (not working):
-------------------------------------------------------

SQLQuery = "INSERT into questionnaireResults (q1, q2, q3, q4, q5, q6, q7, q8, q9, q10, q11, q12, q13, q14, q15, q16, q17, q18, q19, q20, q21) VALUES "
SQLQuery = SQLQuery & "('"


'SQLQuery = SQLQuery & Request.Form("q1") & "','"
Do While not counterFinished
IF counter < 20 THEN
counter = counter + 1
'SQLQuery = SQLQuery & Request.Form("counter + 'q'") & "','"
SQLQuery = SQLQuery & Request.Form("q") + (counter) & "','"
ELSE
counterFinished = true
END IF

LOOP

SQLQuery = SQLQuery & Request.Form("q21") & "')"

set RS1 = ObjDbConnection.Execute(SQLQuery)-------------------------------------------------------

I feel that I am close to the solution but my syntax involves some guesswork. Can anybody see how I can do this successfully?

Thanks

[miranda]

SQLQuery = "INSERT into questionnaireResults (q1, q2, q3, q4, q5, q6, q7, q8, q9, q10, q11, q12, q13, q14, q15, q16, q17, q18, q19, q20, q21) VALUES "
SQLQuery = SQLQuery & "("
For i = 1 to 20
SQLQuery = SQLQuery & "'" & Request.Form("q" & i) & "',"
Next

SQLQuery = Left(SQLQuery, Len(SQLQuery) -1) 'remove last comma
SQLQuery = SQLQuery & ");"
ObjDbConnection.Execute(SQLQuery) 'this is only part needed (set RS1 is not needed)

You do realize that this opens you up to SQL interjection attacks as well as errors if there are apostrophes in any of the Request.Form values

you should add a function to prevent both like so

Note I just replace them with their ascii values (these have been altered to ge them to show on the screen)

'prevent SQL interjection --
'SQL interjection is used to take command of a database.
Private Function preventInjection(ByRef theString)
theString = Replace(theString, "'", "& # 39;") 'removes lone apostrophe's '
theString = Replace(theString, ";", "& # 59;") 'removes semicolon
theString = Replace(theString, "--", "& # 45;& # 45;") 'removes double dash sql comment
preventInjection = theString
End Function
'note the

SQLQuery = "INSERT into questionnaireResults (q1, q2, q3, q4, q5, q6, q7, q8, q9, q10, q11, q12, q13, q14, q15, q16, q17, q18, q19, q20, q21) VALUES "
SQLQuery = SQLQuery & "("
For i = 1 to 20
SQLQuery = SQLQuery & "'" & preventInjection(Request.Form("q" & i)) & "',"
Next

SQLQuery = Left(SQLQuery, Len(SQLQuery) -1) 'remove last comma
SQLQuery = SQLQuery & ");"
ObjDbConnection.Execute(SQLQuery) 'this is only part needed (set RS1 is not needed)

[waarbs]

Fantastic Miranda,

thanks very much for your input, that has helped loads, even if I don't fully understand your coding! It is working fine now.

[miranda]

glad that I could help. What part do you not understand?

PS If you used the function to prevent SQL attacks and also insert errors hopefully you removed the spaces that I had to put into the ascii values to display them here.