Adding records within a loop

Hello,

I am experienced in Access, but new to ASP....

I have an online survey that I want to add results to an access table...by
using a saved query with paramaters...

Not sure where my code is failing....or for that matter what the best way to
go about testing it is....

The SQL of the access query is....
----------------------------------
INSERT INTO tblSurveyResults ( RecordID, QuestionID, AnswerNUM, AnswerText,
SurveyID )
SELECT [MyRecordID] AS Expr1, [MyQuestionID] AS Expr2, [numAnswer] AS Expr3,
[txtAnswer] AS Expr4, [MySurveyID] AS Expr5;
---------------------------------
the only other field in tblSurveyResults is an autonumber ID field.

The offending code is as follows...
Any advice greatly appreciated.

Mal.
-------------------------------------
'ADD Question responses

if Form_SurveyID = 1 then
StartNum = 1 'These numbers are the form field names for each
question
EndNum = 57
End if

if Form_SurveyID = 2 then
StartNum = 71
EndNum = 94
End If

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("SurveyResults.mdb")

For i = StartNum to EndNum

if isnumeric(Request.Form(i)) then 'Test for numeric (radio Group)
answer or text answer given and assign query params
Form_NumAnswer = request.form(i)
Form_TxtAnswer = ""
else
Form_NumAnswer = ""
Form_TxtAnswer = request.form(i)
end if

adoCon.qryInsertResponse Form_RecordID, i, Form_NumAnswer, Form_TxtAnswer,
Form_SurveyID
next

Set adoCon = Nothing

Re: Adding records within a loop

Mal Reeve wrote:
> Hello,
>
> I am experienced in Access, but new to ASP....
>
> I have an online survey that I want to add results to an access table...by
> using a saved query with paramaters...
>
> Not sure where my code is failing....or for that matter what the best way to
> go about testing it is....
>
> The SQL of the access query is....
> ----------------------------------
> INSERT INTO tblSurveyResults ( RecordID, QuestionID, AnswerNUM, AnswerText,
> SurveyID )
> SELECT [MyRecordID] AS Expr1, [MyQuestionID] AS Expr2, [numAnswer] AS Expr3,
> [txtAnswer] AS Expr4, [MySurveyID] AS Expr5;
> ---------------------------------
> the only other field in tblSurveyResults is an autonumber ID field.
>
> The offending code is as follows...
> Any advice greatly appreciated.
>
> Mal.
> -------------------------------------
> 'ADD Question responses
>
> if Form_SurveyID = 1 then
> StartNum = 1 'These numbers are the form field names for each
> question
> EndNum = 57
> End if
>
> if Form_SurveyID = 2 then
> StartNum = 71
> EndNum = 94
> End If
>
> 'Create an ADO connection object
> Set adoCon = Server.CreateObject("ADODB.Connection")
> 'Set an active connection to the Connection object using a DSN-less
> connection
> adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("SurveyResults.mdb")
>
> For i = StartNum to EndNum
>
> if isnumeric(Request.Form(i)) then 'Test for numeric (radio Group)
> answer or text answer given and assign query params
> Form_NumAnswer = request.form(i)
> Form_TxtAnswer = ""
> else
> Form_NumAnswer = ""
> Form_TxtAnswer = request.form(i)
> end if
>
> adoCon.qryInsertResponse Form_RecordID, i, Form_NumAnswer, Form_TxtAnswer,
> Form_SurveyID
> next
>
> Set adoCon = Nothing


First thing to do when testing this kind of thing is to put some
response.write's at key stages in the code to verify that the values
you expect are actually being passed. So, instead of
adoCon.qryInsertResponse, do this:

Response.Write Form_RecordID & ", " & i & ", " & Form_NumAnswer & ", "
& Form_TxtAnswer & ", " & Form_SurveyID & "
"

If this shows that values are missing, "walk" the response.write
further up the code to see where the value goes missing. Try this
first, then get back to us if you need further help. I for one can't
see in your code where Form_RecordID obtains its value.

Btw, a better DSN-Less connection is :
"Provider = Microsoft.Jet.OLEDB.4.0;Date Source=" &
Server.MapPath("SurveyResults.mdb")

The ODBC driver you are currently using has been deprecated.

--
Mike Brind