Explanation

If we wished to update multiple records with the same value we could use a similar method to the multiple delete example, whereby we would provide the values we want the fields updated with, then select the records to be updated, something like this:

UPDATE tableName SET Field1='ABC', Field2=123 WHERE RecordID IN(1,5,8,12)

However, in the example we are updating multiple fields with different values, in which case we need to loop through the records, updating them one at a time with the correct values.

You can see the code for the entire page discussed in this article here, in addition you can download MultiUpdateDemo.zip, this contains a demo page and Access database. If you extract the zip file to your web testing directory and browse to the MultiUpdateDemo.asp page it should work without any modification.

First create a recordset to retrieve the records you wish to update and the Record Identity value from the database. Now set up a table within a form and place a text box in each of the cells where you are going to display the values to be updated. Now drag the dynamic text from the data bindings window to the relevant text box in your table. Now select the table row and use the Dreamweaver Repeat Region server behaviour to produce a list.

Now switch the page to code view, and assign the Record Identity value from the recordset to a variable, something like this:

<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<% intRecID =(Recordset1.Fields.Item("RecID").Value) ' Store the current RecordID in a variable %>
<tr>
<td nowrap><font size="1" face="Arial, Helvetica, sans-serif">_
     <%= intRecID %><input name="hidRecID<%= intRecID %>" type="hidden"_
     value="<%= intRecID %>" size="5"></font></td>
<td nowrap><font size="1" face="Arial, Helvetica, sans-serif">_
     <input name="txtText<%= intRecID %>" type="text"_
     onChange="RecUpdate('<%= intRecID %>')"_
     value="<%=(Recordset1.Fields.Item("RecText").Value)%>"_
     size="20"></font></td>
<td nowrap><font size="1" face="Arial, Helvetica, sans-serif">_
     <input name="txtNum<%= intRecID %>" type="text"_
     onChange="RecUpdate('<%= intRecID %>')"_
     value="<%=(Recordset1.Fields.Item("RecNum").Value)%>" size="20"></font></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>

As you can see I've used the record Identity to dynamically give each of the form fields a unique name, in the browser these will appear as txtNum1, txtNum2 etc.

Now we could just place an UPDATE command within the repeat region, so that when the form is submitted the command will update every record as the code loops through. However this isn't very satisfactory. Let's suppose that we display 40 records and only change 3 of them, the code still loops through and updates all 40 records, 37 of them with exactly the same values as they had previously.

So I've used some client side JavaScript and created a function that creates a list of the records that were actually changed. Notice the onChange="RecUpdate('<%= intRecID %>') code on each of the editable text boxes.

Here is the JavaScript function:

function RecUpdate(RecID){
   var ThisID = "*" + (RecID) + "*"
   if (document.form1.hidRecIDs.value == ""){
      document.form1.hidRecIDs.value = (ThisID)
   }
   if (document.form1.hidRecIDs.value != ""){
      var str = document.form1.hidRecIDs.value;
      var pos = str.indexOf(ThisID);
      if (pos == -1) {
         document.form1.hidRecIDs.value = document.form1.hidRecIDs.value +_
          ", " + (ThisID)
      }
   }
}

This will create a list of RecordIDs like this: *2*, *3*, *5*, *8* etc. and store it in the hidden field hidRecIDs. As the list is created, it checks itself, so that only 1 of each ID is included, the asterisks are used so that *1* will only be found in *1*, without the asterisks 1 would also be found in 10, 11, 12 etc.

When the form is submitted we use the list of record IDs stored in the hidden field to set up a loop. First we strip out all the asterisks using the Replace function, then create an array from the comma delimited list of Record IDs. We can now loop through the array of Record IDs, using the array values to refer to the the dynamically named text boxes, because we used the Record IDs to do this in the first instance the values will match up. Using this method we ensure that we only update the records that were actually modified.

<%
If Request("Submit") <> "" Then
   intRecIDs = Replace(Request("hidRecIDs"), "*", "")
   arrRecIDs = Split(intRecIDs, ", ")
   For i = 0 to Ubound(arrRecIDs)
      strText = Replace(Request("txtText" & arrRecIDs(i)), "'", "''")
      intNum = Replace(Request("txtNum" & arrRecIDs(i)), "'", "''")

      set commUpdate = Server.CreateObject("ADODB.Command")
      commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;_
          Data Source=" & Server.MapPath("Databases/TestDB.mdb") & ";_
          Persist Security Info=False"
      commUpdate.CommandText = "UPDATE TestTable SET RecText = '"_
          & strText & "', RecNum = " & intNum & " WHERE RecID = " & arrRecIDs(i)
      commUpdate.CommandType = 1
      commUpdate.CommandTimeout = 0
      commUpdate.Prepared = true
      commUpdate.Execute()
   Next
   strMessage = i & " Records Updated"
   Response.Redirect("MultiUpdateDemo.asp?Message=" & strMessage)
End If
%>