Tutorial #170

Updating Multiple Records

Level: Intermediate
Requirements: DreamweaverMX, Access2000
Language: ASP VBScript

Introduction

This tutorial will show you how to first display rows of data from a recordset and then allow you to update the records that have been displayed on the screen. This tutorial uses commands and altered recordset code from the standard Dreamweaver code so you should be relatively confident in hand coding some script to understand this tutorial and not get scared by the red exclamation mark!

Step 1: Creating the table.

We are going to use a small table of contact information, which we will populate with a few records. Set the table up as below and populate it with some records off the top of your head.

tblContact
- RecordID (Primary Key, Autonumber)
- ContactName (Text)
- ContactNumber (Text)
- ContactEmail (Text)
- ContactAge (Number)

Step 2: Displaying the data.

First off, create a new page called update.asp, secondly, we need a recordset, so create a recordset and call it rsContact using the following SQL...

...click ok to create the recordset. Now, on the page create a form, inside that form create a table with 2 rows and 4 columns.

In the top four columns we will simply add some titles, so enter from column 1 to 4 respectively, Name, Number, Email, Age.

In the second row create a text field in each of columns. We will name them the same as we have the fields in the table, eg. contactName, contactNumber, contactEmail & contactAge. In the first column, beside the name field create a hidden field and called it RecordID.

We need to now bind these fields to their recordset equivalents. So, click on the contactName field and in the properties for this field, locate the Init Val property and click on the lightning bolt beside it. Select the ContactName field from the recordset and click ok.

Repeat this process for all the fields, including the hidden RecordID field, these should be bound to their recordset counterparts as we did with the contactName field.

We now need to create a repeat region to display ALL the data from the data. So, click in the first column of the second row and then click on the <tr> tag in the status bar.

This will highlight the entire second row of the table. From here, go to your server behaviours panel and create a repeat region for ALL the records.

Click ok to create the repeat region.

Save the page and run it. We now have all the information from the table being displayed within a form in the text fields on the webpage. All we need to do now is allow any changes made to these records to be saved.

Step 3: Identifying the fields to update.

As it stands at the moment, when the page is run, if we tried to capture the information from the text fields, we could only capture the last set of data. Why is this? In simple terms, this is because all the text fields have the same name. We need to make the text fields unique to the row of data they are displaying so that we can capture each line of data and do as we wish with it.

Simple to do, but prepare to annoy dMX in the process. Lets take a look at the code for the repeat region we have.

<% While ((Repeat1__numRows <> 0) AND (NOT rsContact.EOF)) %>

Not very exciting is it? What we need to do here to is create a new variable that will house a number that we will use to identify what row we are currently in. So, add the following lines to the above code...

<% Dim counter
While ((Repeat1__numRows <> 0) AND (NOT rsContact.EOF))
counter = counter + 1
%>

Please note: any code in blue is there simply as a marker so you know where you should be entering the new code (new code in black) into the current block of code.

As you can see from that, we are creating a variable called counter, which will increment by 1 each time the loop kicks off. All fine and dandy, now we need to use this with the text fields that we currently have. Have a look at the code for the text fields.

To uniquely name these fields, we have to add the counter value to the end of each of the names of them. Not great grammar that, but easy enough in tech speak. All we need to do is add the following code...

<%=counter%>

...to each name and id value of the form fields. Doing so will leave our code looking like this...

<tr>
  <td>
    <input name="contactName<%=counter%>" type="text" id="contactName<%=counter%>" value="<%=(rsContact.Fields.Item("ContactName").Value)%>">
    <input name="RecordID<%=counter%>" type="hidden" id="RecordID<%=counter%>" value="<%=(rsContact.Fields.Item("RecordID").Value)%>"></td>
  <td>
    <input name="contactNumber<%=counter%>" type="text" id="contactNumber<%=counter%>" value="<%=(rsContact.Fields.Item("ContactNumber").Value)%>"></td>
  <td>
    <input name="contactEmail<%=counter%>" type="text" id="contactEmail<%=counter%>" value="<%=(rsContact.Fields.Item("ContactEmail").Value)%>"></td>
  <td>
    <input name="contactAge<%=counter%>" type="text" id="contactAge<%=counter%>" value="<%=(rsContact.Fields.Item("ContactAge").Value)%>"></td>
</tr>

So now, if you save the page and run it then look at the source code of the webpage you will see that all the names are proceeded by 1, 2, 3 etc. Now we can capture ALL the data in the form as we can now access each field uniquely.

Before we can get into the tasty part of the coding you will need to add a button and two hidden fields just before the end of the form. The button, quite obviously, will be used to submit the form so fire ahead with that. Now create a hidden field called action with an initial value of update. The second hidden field should be called counter will a hidden value of <%=counter%>

The hidden field counter will simply contain the final number of fields that have been displayed whilst in the loop of the repeat region. You will see why we need this next.

Step 4: Updating the records.

As you could probably well imagine, we will not be creating the update code using the standard dMX update behaviour routine. No, no. We will be coding a routine that will scroll through all the records and update them to the table one at a time.

To do this though, we will use a command from the dMX server panel, however, I will not bother with the nitty gritty of that and just display the code and the detail for it. Go to the code editor, scroll to the top of the page and add the following code to it.

<%@LANGUAGE="VBSCRIPT"%>

<%
Sub sRunSQL(vSQL)
  set cExecute = Server.CreateObject("ADODB.Command")
  With cExecute
    .ActiveConnection = MM_YourConnectionString_STRING
    .CommandText = vSQL
    .CommandType = 1
    .CommandTimeout = 0
    .Prepared = true
    .Execute()
  End With
End Sub
%>
<%
Dim rsContact
Dim rsContact_numRows

What we have here is a small subroutine that when called will expect an SQL statement to be passed to it. It will then execute this SQL and await further instruction.

Please note: be sure to change the MM_YourConnectionString_STRING to that of whatever name you have for your connection. This can be found by looking at the value of the rsContact.ActiveConnection line in the recordset code for rsContact.

We also need to add the following piece of code that creates a function that will check for any dodgy occurrences of the single quote mark appearing in the data on the form. So, just above the Sub code, add the following...

<%@LANGUAGE="VBSCRIPT"%>

<%
Function fFormat(vText)
fFormat = Replace(vText, "'", "''")
End Function

Sub sRunSQL(vSQL)
set cExecute = Server.CreateObject("ADODB.Command")

All that we need know is the glue that sticks all this together. Follow the code flow below, copying and pasting the code onto your page. The code will appear in blocks with an explanation below it. The blue code again serves as a marker, only enter the code in black text.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
If Request.Form("action")="update" Then
  'Set variables for update
  Dim updateSQL, i
  Dim cRecordID, cName, cNumber, cEmail, cAge

Function fFormat(vText)
  fFormat = Replace(vText, "'", "''")
End Function

We first need to check that the form has been processed. Using the IF statement, we will check for the hidden field action to be passed through. If so, we will then create some variables that will deal with the form objects.

Dim cRecordID, cName, cNumber, cEmail, cAge

'Loop through records on screen and update
For i = 1 To fFormat(Request.Form("counter"))

We now create a FOR loop that will start at 1 and go to the number of loops we made in the repeat region. This is where the hidden field counter is used, because that holds the number of repeats we done on the page when initially loaded, it will now serve as the limit for this processing loop.

For i = 1 To fFormat(Request.Form("counter"))
'Create the proper field names to reference on the form
cRecordID = "RecordID" & CStr(i)
cName = "contactName" & CStr(i)
cNumber = "contactNumber" & CStr(i)
cEmail = "contactEmail" & CStr(i)
cAge = "contactAge" & CStr(i)

To successfully grab the information from the text fields we need to create the names of those fields to use to capture the data held within in. Bit complicated, but basically what we are doing here is placing the value RecordID1, contactName1, contactNumber1 etc. in the variables so we can access them using the Request.Form command.

cAge = "contactAge" & CStr(i)

'Create the update sql statement
updateSQL = "UPDATE tblContact SET ContactName='" & fFormat(Request.Form(cName)) & "', ContactNumber='" & fFormat(Request.Form(cNumber)) & "', ContactEmail='" & fFormat(Request.Form(cEmail)) & "', ContactAge=" & fFormat(Request.Form(cAge)) & " WHERE RecordID=" & fFormat(Request.Form(cRecordID))

Please note: the above SQL statement (in bold) should be entered in as one line of code.

We now create the sql statement that will be used to update the current record in the FOR loop. We grab the data from the text field and apply the fFormat function to it to weed out an single quote marks. Why do we do this? Check out this url for the answer: http://www.securiteam.com/securityreviews/5DP0N1P76E.html

updateSQL = "UPDATE tblContact SET ContactName='" & fFormat(Request.Form(cName)) & "', ContactNumber='" & fFormat(Request.Form(cNumber)) & "', ContactEmail='" & fFormat(Request.Form(cEmail)) & "', ContactAge=" & fFormat(Request.Form(cAge)) & " WHERE RecordID=" & fFormat(Request.Form(cRecordID))

'Run the sql statement
Call sRunSQL(updateSQL)
Next

'Refresh page
Response.Redirect("update.asp")
End If

Finally, we call the sub routine, passing the updateSQL value through for it to run. It will run the sql and update the record. Once that is done, it will continue round the FOR loop until it is exhausted the criteria.

We then do a Response.Redirect back to this page to show that data again as it currently stands within the database ie. updated.

Here is the full code for the routine in case the above got a little bit confusing.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
If Request.Form("action")="update" Then
  'Set variables for update
  Dim updateSQL, i
  Dim cRecordID, cName, cNumber, cEmail, cAge

  'Loop through records on screen and update
  For i = 1 To fFormat(Request.Form("counter"))
    'Create the proper field names to reference on the form
    cRecordID = "RecordID" & CStr(i)
    cName = "contactName" & CStr(i)
    cNumber = "contactNumber" & CStr(i)
    cEmail = "contactEmail" & CStr(i)
    cAge = "contactAge" & CStr(i)

    'Create the update sql statement
    updateSQL = "UPDATE tblContact SET ContactName='" & fFormat(Request.Form(cName)) & "', ContactNumber='" &       fFormat(Request.Form(cNumber)) & "', ContactEmail='" & fFormat(Request.Form(cEmail)) & "', ContactAge=" &       fFormat(Request.Form(cAge)) & " WHERE RecordID=" & fFormat(Request.Form(cRecordID)) 'Remember, this is all one line!

    'Run the sql statement
    Call sRunSQL(updateSQL)
  Next

  'Refresh page
  Response.Redirect("update.asp")
End If

Using this method we can update as many multiple records within a repeat region as we wish. However, the above does not cater for any error trapping, so if you leave any data out in the form fields this routine will throw an error. You can either code a routine that checks the fields before entry or set your text fields property in Access to allow zero length.

Tutorial By	Submitted On	Views	Rating
Rob Boyle	15/02/2007	2708	Rate Tutorial