Update Question

[ecugrad]

Question:Can I do the below and if so how.

We have a survey that I put on our Intranet using ASP & Access db. Survey is sent out to our regions which they fill in their comments that are stored in db. Each department has to reply to the comments. On one update page I would like to list all the names,comments and an input box to enter our reply. Can I send multiple inputs to one update page. I use the SurveyID field as the key. Ex:On the example below I would like the "Thank You" to update ID 21 and will try update ID 22 using one update statement..


21 Jane Doe Good job on helpdesk Thank You
22 Doug Smith you need help will try

Thanks

 

[jemminger]

ecugrad,

yes...it's not that difficult if you keep your field names consistent.

in a multi-row form like above, if your each row has the same field names, like "SurveyID", "userName", "comment", "reply"...then the data will be sent to the form as comma-delimited strings. you can then Split() these strings into arrays, then iterate through the arrays & perform an update:
[tt]
Dim arIds, arReplies, x, sql

arIds = Split(Request.Form("SurveyID&quot,",&quot
arReplies = Split(Request.Form("reply&quot,",&quot

For x = 0 to UBound(arIds)
sql = "UPDATE myTableName" &_
" SET reply = '& arReplies(x) &'" &_
" WHERE SurveyID = '& arIds(x) &'"
myConnObj.Execute(sql)
Next
[/tt]

the only drawback is that your form fields cannot contain commas...if you need to allow commas, you'll have to come up with another method, such as appending the row number to each field name, storing the total row number in a hidden field, then setting up a loop to execute the update query x number of times based upon the total, something like:
[tt]
For x = 1 to Request.Form("rowTotal&quot
sql = "UPDATE myTableName" &_
" SET reply = '& Request.Form("reply" & x) &'"
" WHERE SurveyID = '& Request.Form("SurveyID" & x) &'"
myConnObj.Execute(sql)
Next
[/tt]

=========================================================
if (!succeed) try();
-jeff