Adding records within a loop

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mal Reeve - 17 Aug 2006 15:55 GMT

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

Reply to this Message

Mike Brind - 17 Aug 2006 20:55 GMT

> Hello,
>
[quoted text clipped - 56 lines]
>
> 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 & "<br>"

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

Reply to this Message

Webmaster Forum / ASP / Database Access / August 2006

Adding records within a loop