%>
Dim cmd
Dim sqlInsert
sqlInsert = "Insert Into questionnaireResults(q1, q2, q3, q4,
q5)Values(?,
?, ?, ?, ?, ?)"
Set cmd = Server.CreateObject("ADODB.Command")
Dim counter
Dim paramName, paramValue
For counter = 1 To 20
paramName = "@q" + counter
paramValue = Request.Form("q" + counter)
cmd.Parameters.Append(cmd.CreateParameter(paramName, 202, 1,
paramValue))
Next
cmd.ActiveConnection = my_connection_string_variable
cmd.Execute
%>
Using a Command object to execute an insert command.
Using Parameters to encapsulate the form posted values.
The loop adds parameters to the Command object, assuming that
all 20 form
fields have been posted correctly.
About the cmd.CreateParameter(..) call:
1) It creates a parameter with the name format of "@qX" where
X is the
current value of the counter, so it would be "@q1", "@q2",
"@q3", etc.
2) It creates a parameter of type adVarWChar which has a
numeric value 202
3) It specifies an input parameter direction (value is 1)
If adVarWChar is not the correct datatype, then have a look
at c:\program
files\common files\system\ado\adovbs.inc - DataTypeEnum
values to get the
numeric value of the correct datatype.
Ron
"15266" <webforumsuser@macromedia.com> wrote in message
news:e5p2n7$73o$1@forums.macromedia.com...
>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
>
>
4
Replies
AdChoices