n previous 10-Minute Solutions, I have covered how to retrieve data from a database and display it in the browser. The complement to that functionality is getting data from the user and updating your database. You can write to a database using a couple of different techniques. You can write by creating and modifying a recordset and then updating the recordset, or write by issuing a SQL statement that contains the values to be updated.

In the first technique, you create a recordset variable containing the present contents of the database that you wish to update. You apply the updates to your local copy of the data. Once you have updated all the fields you need, you invoke the “update” method and the data gets updated in the database. For an existing record, this means that data has traveled from your database to your ASP page (on the server side), and then has to travel back with the updated values. This is in addition to the data that traveled the first time when you fetched the data so that it can be displayed in the browser. This operation is usually expensive in terms of resources. However, it is much simpler to program this way, and that’s why novice programmers usually tend to use this technique.

The second technique is more difficult to understand and implement, but it is much more efficient, especially when working with Web pages. In this technique, you do not fetch the data from the database, change it locally, and then update it back on the database. Instead, you construct a SQL statement to perform your update directly on the database and then execute the SQL statement.

Say you wish to display a single record from the customer’s table and allow the user to modify all fields but the primary key field (in this case, the customer ID). To implement this, display the data from the database, allowing the user to pick a particular record to edit. Next, display the data in the chosen record in the form of an HTML form, using text boxes and so forth for displaying the data. Then, when the user submits the form, construct your SQL statement to update the database with the new values.

Display Data From the Database
Displaying data from the database has been the topic of my last few 10-Minute Solutions. If you have missed any of them, please follow this link to the previous articles. You will be modifying the code presented in the article, “Filter Data from a Database,” so that in addition to displaying the records in a Grid format, you also provide the ability for the user to edit a particular record. You can accomplish this in one of two ways: provide an “Edit” button next to each record which, when clicked, initiates the editing; or provide a hyperlink on a single field which, when clicked, initiates the editing.

Let’s assume that your current ASP page that is displaying this grid is 10MinWrite1.asp. In addition, you have a new ASP page that handles the editing called 10MinWrite2.asp. Further, this ASP page requires to know the ID of the record that the user wishes to edit, the CustomerID field value. Let’s assume that this ID is being sent to the ASP page via a variable called “id”. Then, if you initiate a call to the ASP page using “10MinWrite2.asp?id=Value”, your ASP page can retrieve the “id” from the QueryString and use it in its processing. This means that the page displaying the data in a grid format needs to construct this QueryString when providing the edit mechanism. To do so, modify the code in the page 10MinPrimer3.asp where it reads:

' -- Now output the contents of the Recordset objRS.MoveFirst Do While Not objRS.EOF    ' -- output the contents    Response.Write ""    For i = 0 to objRS.Fields.Count - 1       Response.Write "" & objRS.Fields(i) & ""    Next    Response.write ""    ' -- move to the next record    objRS.MoveNext Loop 

and replace with this code:

' -- Now output the contents of the Recordset objRS.MoveFirst Do While Not objRS.EOF    ' -- output the contents    Response.Write ""    For i = 0 to objRS.Fields.Count - 1       If Ucase(objRS.Fields(i).Name) = "CUSTOMERID" Then         Response.Write "" & _          objRS.Fields(i) & ""       Else         Response.Write "" & objRS.Fields(i) & ""       End if   Next    Response.write ""    ' -- move to the next record    objRS.MoveNext Loop 

Note the change in the code. You are examining if the field is called “CustomerID”, and if so, you enclose the value of the field in a hyperlink () tag. First check to see if the field is the ID field, making sure to check it case-insensitive to be on the safe side:

If Ucase(objRS.Fields(i).Name) = "CUSTOMERID" Then

If it is the ID field, then instead of simply writing the value of the field, you enclose it in a hyperlink. When building the hyperlink, you also include the question mark (?) to begin a query string, as well as the key=value pair:

10MinWrite2.asp?id=" & objRS.Fields(i) & """>" 

In your sample data, if the ID was for “Let’s Stop N Shop” (LETSS), the hyperlink would be in this format:

This code would result in a page where the first column in the Grid was a column of hyperlinks (see Figure 1). When the user clicks on any hyperlink, it executes the page, 10MinWrite2.asp.

Display the Data Entry Form
Your next task is to create the 10MinWrite2.asp page that will display the data for the record chosen in the form of a data-entry HTML form. This page will not only display the data-entry form, but will also handle the data update. Using the same logic as in my previous articles, you will use a hidden variable called PASS to identify if the page is being called the first time or any subsequent time.

To display the data-entry form, use the same technique that you use to display data from the database, but instead of displaying the data as text, you need to display it as form text boxes. First, figure out whether your user is accessing this page for the first time, or if this is a repeat visit after a form submission.

Dim intPassintPass = Request("PASS") 

The variable “PASS” comes from an invisible textbox on the page so the user cannot tamper with its value. It can have only two possible values: a value of “1”; or a value of “”, or nothing. The variable “PASS” tells you how the user accessed the page, 10MinWrite2.asp. If the user is coming to this page for the first time, the variable “PASS” will have no value. If the user is coming to this page for the second or later time (after submitting the form), the variable “PASS” will have a value of “1”. You use this variable to decide what you need to do next.

Use a Select Case statement to evaluate the value of the variable intPass. If it is equal to “1”, call a separate subroutine to handle the update of the data to the database. If it is anything else, again call a separate subroutine to display the HTML form. Also, make sure that no code below the Select Case gets accidentally executed by “ending” the response with a “Response.End” statement.

' -- Take Action based on how you came into this pageSelect Case Trim(intPass)	Case Trim("1")		' -- Repeat Visit, update the data		UpdateData	Case Else		' -- First Time Visit, display HTML Form		DisplayHTMLFormEnd Select' -- Make sure nothing else gets processedResponse.End

To display the HTML form, obtain the data from the database for the ID chosen and output the data within textboxes. Create your subroutine and then start placing code within it.

Sub DisplayHTMLForm()End Sub

First, you need to get the ID of the record you wish to edit. You can obtain the ID from the QueryString variable “id”. If no ID is found, you cannot proceed, so “redirect” the user back to the original page. Otherwise, populate your recordset with data.

Dim strID, strSQLStrID = Request("id")If strID = "" Then	' - we have a problem, we cannot proceed, send user back        Response.redirect "10MinWrite1.asp"End if' - Build the SQL Statement to fetch the single record for this IDstrSQL = "SELECT * FROM CUSTOMERS WHERE CustomerID = '" & strID & "' "' -- Populate our Recordset with data set objRS = objConn.Execute (strSQL) 

Once you have data in the recordset, you can display it within a form using a table with two columns. In the left column, you place the field name, and in the right one, you place the field value within a text box.

%>
 <%   objRS.MoveFirst   ' -- Output data   For i = 0 to objRS.Fields.Count - 1      Response.write ""       Response.Write ""       ' - output value as a textbox, except for ID field      Select Case Ucase(objRS.Fields(i).Name)         Case "CUSTOMERID"            ' -- write text, and a hidden form field            Response.Write ""          Case Else         ' - output a text box            Response.Write ""       End Select      Response.write ""    Next%>
" & objRS.Fields(i).Name & "
" & _             objRS.Fields(i) & "
 

Note that you have a hidden field called “PASS” with a value of “1”. The ID field cannot be modified and is placed in a hidden field. This will result in an HTML form that looks like Figure 2.

Update the Data in the Database
When your user modifies the data and then hits the Update button, the form is submitted. The 10MinWrite2.asp file executes once again, but this time the variable “PASS” has a value of “1”. This results in the subroutine UpdateData being executed.

Create a subroutine called UpdateData. Within the subroutine, declare the variables for all fields used in your form:

' -- our modifiable fields    Dim strCompanyName, strContactName, strContactTitle, _        strAddress, strCity, strRegion    Dim strPostalCode, strCountry, strPhone, strFax

Then, obtain the values from the form.

' -- Get values from our Form    strCompanyName = Request("CompanyName")    strContactName = Request("ContactName")    strContactTitle = Request("ContactTitle")    strAddress = Request("Address")    strCity = Request("City")    strRegion = Request("Region")    strPostalCode = Request("PostalCode")    strCountry = Request("Country")    strPhone = Request("Phone")    strFax = Request("Fax")

Finally, prepare your SQL statement. Because you are updating an existing record, your SQL statement will be in this format:

UPDATE TABLENAME SET FIELD1 = VALUE1, FIELD2=VALUE2... AND SO ON  ... WHERE PRIMARYKEYFIELD = VALUE

In addition, you also want to make sure that all character fields are enclosed within single quotes. If a field has a single quote within it, it needs to be incremented to two single quotes in order for the SQL string to work. You can use a separate function, CheckString, to handle the single quote. Also, if a field has no value, instead of sending ” as the value, it is better to send the reserved word “Null”. The CheckString function handles all of these details:

' -- Build our SQL Statement    strSQL = "UPDATE Customers " & _             " SET CompanyName = " & CheckString(strCompanyName, ",") & _             " ContactName = " &  CheckString(strContactName, ",") & _             " ContactTitle = " & CheckString(strContactTitle, ",") & _             " Address = " & CheckString(strAddress, ",") & _             " City = " & CheckString(strCity, ",") & _             " Region = " & CheckString(strRegion, ",") & _             " PostalCode = " & CheckString(strPostalCode, ",") & _             " Country = " & CheckString(strCountry, ",") & _             " Phone = " & CheckString(strPhone, ",") & _             " Fax = " & CheckString(strFax, " ") & _             " WHERE CustomerId = '" & strID & "' "

The last line in the code uses the WHERE clause to identify the record you need to update. Do not omit this line, as it will cause all your database records to be modified, not just the one you want.

Finally, you can update the database by using the Execute method of the Connection object. You do not need a recordset object for this step.

' -- Create objects	Set objConn = Server.CreateObject("ADODB.Connection")	' -- Open the Connection	objConn.Open strConnection        ' - Execute        objConn.Execute (strSQL)            objConn.Close        set objConn = Nothing

You are all set; the record has been updated. You can finish your code by sending the user back to the grid or back to the same page displaying updated values. By the way, to insert a new record, you would use an identical technique, except your SQL statement would be an INSERT statement. Get the full source code here. Next month’s 10-Minute Solution will look at ways to enhance this code as well as other data updating techniques.