596 Using VBScript to Process a Recordset

In Tip 594 "Updating and Deleting Database Data Through an HTML Form," you learned how to create HTML forms that let users generate and execute SQL UPDATE and DELETE statements. You also learned that the best way to let most users remove table rows or change data within a database is to require that they first execute a query that displays potential target rows onscreen. Then, with data displayed within an HTML form, the user can select the rows to change or delete visually, which is much easier and less prone to error than writing selection criteria for the WHERE clause within an SQL UPDATE or DELETE statement.

Whereas users may, at times, want to delete several table rows at once, modifying column values within a table is typically a one-row-at-a-time operation. As such, you might replace the check box you placed at the start of each row of query results in Tip 594 with a hyperlink that retrieves the column values from a particular row in an database table. By placing the row's column values within the elements of an HTML form, you can let the user modify each value within the row as desired. When finished modifying the row's values, the user clicks the HTML form's Submit button to send the form results (that is, the update row values) to a server-side script that submits an UPDATE statement to the DBMS for execution.

Suppose, for example, that you wanted to create a Web-based application that lets users update the data within a CUSTOMERS table. First, create a Web page with a form such as the following that lets the user select the row (or rows) within the CUSTOMERS table that he or she might want to change:

<form action="http: //www.NVBizNet2.com/SQLTips/CustList.asp" method="POST"> SELECT: cust_ID, <input type="text" name="selectClause" size="40"><br> FROM: <input type="text" name="fromClause" size="40" value=customers593><br> WHERE: <input type="text" name="whereClause"

size

="40"><br> ORDER BY: <input type="text"

name

="orderBy" size="40"><br><br> <input type="submit" value="Submit Query"> </form>

After the user clicks the form's Submit button (labeled "Submit Query," in this example), the Web browser sends the form results (that is, the information entered into the form) to an ASP (or PHP) Web page (CUSTLIST.ASP, in this example). Embedded within CUSTLIST.ASP is a script, such as the following, which creates an SQL SELECT statement based on the form results and submits the query to the DBMS:

<% Sub SubmitQuery(objConn, byref objRecordset) DIM queryString If (Trim(Request.Form("selectClause")) = "*") Then queryString = "SELECT * " else queryString = "SELECT cust_ID " If (Trim(Request.form("selectClause")) <> "") Then queryString = queryString & ", " & Trim(Request.form("selectClause")) End If End If queryString = _ queryString & " FROM " & Request.Form("fromClause") If Trim(Request.Form("whereClause")) <> "" Then queryString = queryString & " WHERE " & _ Request.Form("whereClause") End If If Trim(Request.Form("orderBy")) <> "" Then queryString = queryString & " ORDER BY " & Request.Form("orderBy") End If 'submit the query, the SELECT statement to the DBMS Set objRecordset = objConn.Execute (queryString) End Sub %>

Note that the script must include within the query's SELECT clause the PRIMARY KEY column from the target table (CUST_ID, in this example). During the update process, other scripts will use the PRIMARY KEY value to retrieve and then modify the values in a specific row within the target table (CUSTOMERS, in this example). After submitting the query, another script within the CUSTLIST.ASP Web page must process the query results returned from the DBMS. DisplayInTable() is a VBScript subroutine that processes the rows of query results returned within an ADO Recordset object to display the customer list, as shown in Figure 596.1:

click to expand
Figure 596.1: An HTML form that lets users click on the "Edit" hyperlink within the first column to select a row within the CUSTOMERS table to update

<% Sub DisplayInTable(objRecordset) With Response .Write "<table border='1'

cellpadding

='5'>" .Write "<tr>" 'use Recordset field

names

as HTML table column headings .Write "<th>EDIT</th>" For column = 0 To objRecordset.Fields.Count - 1 .Write "<th>" & objRecordset.Fields(column).Name _ & "</th>" Next .Write "<tr>" 'display the value in the Recordset within the HTML table Do While Not objRecordset.EOF .Write "<tr>" 'Put an "EDIT" hyperlink in the First column of each row .Write "<td><a href= 'EditCust.asp?cust_ID=" & _ objRecordset.Fields("cust_ID") & "'>EDTT</a></td>" For column = 0 To objRecordset.Fields.Count - 1 If objRecordset.Fields(column) <> "" Then .Write "<td>" & objRecordset.Fields(column) _ & "</td>" Else .Write "<td>&nbsp;</td>" End If Next .Write "<tr>" objRecordset.MoveNext Loop .write "</table>" End With End Sub %>

After the user clicks one of the "Edit" hyperlinks within the first column of the table shown in Figure 596.1, the Web browser retrieves the ASP Web page EDITCUST.ASP and passes to it a query string with the PRIMARY KEY value for the customer's row within the CUSTOMERS table. A VBScript embedded within EDITCUST.ASP uses the PRIMARY KEY value to retrieve the customer's row and calls the following DisplayInForm() subroutine to display the row's current column values within an HTML form:

<% Sub DisplayInForm(objRecordset) With Response .Write _ "<form action=" & _ "'http://www.NVBizNet2.com/SQLTips/UpdateCust.asp'" & _ "method='POST'>" .Write "<table border='1' cellpadding='5'>" .Write "<tr>" 'use Recordset field names as HTML table column headings For column = 0 To objRecordset.Fields.Count - 1 .Write "<th>" & objRecordset.Fields(column).Name _ & "</th>" Next .Write "<tr>" 'display the value in the Recordset within the HTML table Do While Not objRecordset.EOF .Write "<tr>" 'Put an "EDIT" hyperlink into the first column of each row For column = 0 To objRecordset.Fields.Count - 1 .Write "<td>" & _ "<input type='text' " & _ "name='" & objRecordset.Fields(column).Name & "'" & _ "size='" & objRecordset.Fields(column).ActualSize & "'" & _ "value='" & objRecordset.Fields(column) & _ "'" & "</td>" Next .Write "<tr>" objRecordset.MoveNext Loop .write "</table>" .Write "<input name='primaryKey' type='hidden' value='" & _ Request.Querystring("cust_ID") & "'>" .Write "<br><input type='submit' value='Save Changes'>" .Write " <input type='reset' value='Reset'>" .Write "</form>" End With End Sub %>

When the user clicks the form's Submit button (labeled "Save Changes"), the Web browser sends the form results to VBScripts embedded within the ASP Web page UPDATECUST.ASP. The SubmitUpdate() subroutine embedded within UPDATECUST creates and submits to the DBMS an UPDATE statement, which writes the new column values to the customer's row within the CUSTOMERS table:

<% Sub SubmitUpdate(objConn) DIM queryString, i, setCount setCount = 0 queryString = "UPDATE customers593 SET " For i = 1 To Request.Form.count - 1 If Request.Form.Key(i) <> "cust_ID" Then setCount = setCount + 1 If setCount > 1 Then queryString = queryString & "," End If queryString = _ queryString & Request.Form.key(i) & "='" & _ Request.Form(i) & "'" End If Next queryString = queryString & "WHERE cust_ID =" & Request.Form("cust_ID") 'submit the UPDATE statement to the DBMS Set objRecordset = objConn.Execute (queryString End Sub %>

Flylib.com

Category list

Similar pages

596 Using VBScript to Process a Recordset

596 Using VBScript to Process a Recordset

Similar products