Update Multiple Rows In A Form

[Colin Steadman]

Part No Description Quantity
45643 Random part 10
45678 Another Random part 7
98944 And another 1
<submit button>


The above is an example of some data I am displaying in a
form on an ASP page. I need to allow the users to edit
any of the rows displayed and submit the changes back to
the database.

The problem I have is that I'm not entirely sure how to
determine which rows were changed (if any). And then do
the appropriate update. I've thought about putting an
edit button at the end of each row and forwarding the user
to another page which which deals with the corresponding
row on an individual basis. But this feels a bit long
winded. Can anyone suggest a better way?

TIA,

Colin

 

[Alan Howard]

You could update all rows regardless although that's a bit gash. You don't
say what the user is updating - assuming that it's the quantity field you
can pass each current quantity through to the processor in a hidden form
field. Each hidden field's name should be some derivation of the ID of the
product. When you're processing the form submission, generate a list of the
products that may have been updated and then for each product, request the
new value from it's text control, and the old value from the hidden field
(deriving names as required), and if the two values are different, update
that product record.

Cheers,

Alan

 

[Colin Steadman]

You could update all rows regardless although that's a bit gash.

Agreed.



You don't
say what the user is updating -

It could be any of the fields I've shown in my example, although there
are a whole load more of them.

assuming that it's the quantity field you
can pass each current quantity through to the processor in a hidden form
field. Each hidden field's name should be some derivation of the ID of the
product. When you're processing the form submission, generate a list of the
products that may have been updated and then for each product, request the
new value from it's text control, and the old value from the hidden field
(deriving names as required), and if the two values are different, update
that product record.

This is what I've decided to try and do. I'm giving each input box a
unique name based on the field name and row number with '_new' stuck
on the end. Under this I've got a hidden field setup the same way but
ending in '_old'. Like this (looks a bit messy in google):

<%
rowNumber = 1

'Main table
Do While Not rs2.EOF
response.write "<tr>"
For columnNumber = 0 To rs2.Fields.Count -1
Select Case LCase(rs2.Fields(columnNumber).Name)
Case "invoice_no"
Case Else
response.write " <td>"
response.write " <input type=text name='" & Right("00000"
& rowNumber, 5) & rs2.Fields(columnNumber).Name & "_new" & "' value='"
& rs2.Fields(columnNumber).Value & "'>"

response.write " <input type=hidden name='" & Right("00000"
& rowNumber, 5) & rs2.Fields(columnNumber).Name & "_old" & "' value='"
& rs2.Fields(columnNumber).Value & "'>"
response.write " </td>"
End Select
Next
response.write "</tr>"
rs2.MoveNext
rowNumber = rowNumber + 1
Loop
%>



Then on the page which I'm submitting to I've written the For.. Next
loop copied below which compares old and new values and generates SQL
accordingly. I'm planning to begin a transaction before processing
this loop, and if all goes well and none of the updates generate an
error I'll do the commit at the end. Not tested it yet, but the SQL
its generating looks good.


For Each Item In Request.QueryString
Select Case Item
Case "submit.x", "submit.y"
Case Else
If Right(Item,3) = "old" Then
newValue = Left(Item,Len(Item)-4) & "_new"
If Request.Querystring(Item) <> Request.Querystring(newValue)
Then
fieldName = Mid(Item, 6, Len(Item) - 9)
sql = "update table set " & fieldName & " = " &
Request.Querystring(newValue) & " where " & fieldName & " = " &
Request.Querystring(Item) & " and invoice_no = '" &
Request.Querystring("invoice_number") & "'"
response.write "<br>" & sql
End If
End If
End Select
Next


Colin