"Alan Howard" <Xa***********@ Xparadise.net.n zX> wrote in message news:<#f******* *******@TK2MSFT NGP12.phx.gbl>. ..
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.Coun t -1
Select Case LCase(rs2.Field s(columnNumber) .Name)
Case "invoice_no "
Case Else
response.write " <td>"
response.write " <input type=text name='" & Right("00000"
& rowNumber, 5) & rs2.Fields(colu mnNumber).Name & "_new" & "' value='"
& rs2.Fields(colu mnNumber).Value & "'>"
response.write " <input type=hidden name='" & Right("00000"
& rowNumber, 5) & rs2.Fields(colu mnNumber).Name & "_old" & "' value='"
& rs2.Fields(colu mnNumber).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.QuerySt ring
Select Case Item
Case "submit.x", "submit.y"
Case Else
If Right(Item,3) = "old" Then
newValue = Left(Item,Len(I tem)-4) & "_new"
If Request.Queryst ring(Item) <> Request.Queryst ring(newValue)
Then
fieldName = Mid(Item, 6, Len(Item) - 9)
sql = "update table set " & fieldName & " = " &
Request.Queryst ring(newValue) & " where " & fieldName & " = " &
Request.Queryst ring(Item) & " and invoice_no = '" &
Request.Queryst ring("invoice_n umber") & "'"
response.write "<br>" & sql
End If
End If
End Select
Next
Colin