Modifying a Record

You can display a record in a browser and allow the user to edit the data. Changes made to the data can then be submitted to the server for processing. The easiest and quickest way to modify a record is by executing the SQL UPDATE statement.

The following hands-on exercise creates an ASP page where the user can select a product to update from a drop-down list. After clicking on the Retrieve Data button, the selected product's current price and units in stock are retrieved from the Products table. The retrieved data is placed in text boxes inside a table structure. The user can edit the data in the retrieved fields and insert the changes to the database table by clicking the Update Data button.

Taking Your VBA Programming Skills to the Web

© Hands-On 28-14: Creating a Page for Data Modification

If you don't feel like typing, locate the UpdateProduct.asp file from the downloadable files and copy it to your Learn_ASP folder.

1. Start Windows Notepad and enter the ASP code as shown below.

<HEAD><TITLE>Update Product Information</TITLE></HEAD>

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &_ "c:\Learn_ASP\northwind.mdb" set rst = Server.CreateObject("ADODB.Recordset") If Len(Request.QueryString("ProductId")) <> 0 Then strSQL="SELECT * FROM Products WHERE ProductId=" rst.Open(strSQL & Request.QueryString("ProductId")), conn, 0, 1 If Not rst.EOF Then rst.MoveFirst

<FORM ACTION="UpdateProduct.asp" METHOD="POST" id=form2 name=form2> <Input Type=hidden Name=txtProductId Value="<% =rst("ProductId") %>"> <Input Type=hidden Name=txtProductName Value="<% =rst("ProductName") %>">

<CENTER><H4><% =rst("ProductName") %>

&nbsp;(Product Id=<%=rst("ProductId")%>)</H></CENTER><P> <TABLE BORDER=0 CELLSPACING=4 CELLPADDING=4> <TR>

<TD WIDTH=200 COLSPAN="2"><FONT COLOR=Blue>Unit Price ($):</FONT></TD> <TD BGCOLOR="#00FF00">

<INPUT TYPE="text" NAME="UnitPrice" VALUE="<% =rst("UnitPrice") %>"</TD> </TR> <TR>

<TD COLSPAN="2"><FONT COLOR=Blue>Units In Stock:</FONT></TD> <TD BGCOLOR=#00FF00">

<INPUT TYPE="text" NAME="UnitsInStock" VALUE="<% _ =rst("UnitsInStock") %>"</TD>

<TD><INPUT type="submit" value="Update Data" id=submit2 _ name=submit2></TD>

End If rst.Close End If

If Not IsEmpty(Request.Form("submit2")) Then If Request.Form("UnitPrice")= "" or _

Request.Form("UnitsInStock") = "" Then

Response.Write "<B><Font Color=Red>You cannot leave any fields blank." _ & "Please Try Again</B></FONT>"

Part V

Else strSQL = "UPDATE Products SET " _

& "UnitPrice = '" & Request.Form("UnitPrice")& "', " _

& "UnitsInStock = '" & Request.Form("UnitsInStock")& _

& "WHERE ProductId = " & Request.Form("txtProductId") conn.Execute strSQL

Response.Write "The following Update statement was executed for <B>" _

& Request.Form("txtProductName") &"</B><BR>" Response.Write "<PRE>" & strSQL & "</PRE><BR>" End If End If strSql = "SELECT * FROM [Products] ORDER BY [ProductName]" Set rst = conn.Execute (strSql) If Not rst.EOF Then rst.MoveFirst

<FORM ACTION="UpdateProduct.asp" METHOD="Get" >

<TD><B>Select a Product to Update</B></TD> <TD><SELECT Name="ProductId"> <OPTION></OPTION>

Do While Not rst.EOF

Response.Write "<OPTION Value='" & rst("ProductID") &"'>"&_

rst("ProductName") & "</OPTION>" rst.MoveNext Loop End If rst.Close set rst = Nothing conn.Close set conn = Nothing

<TD><INPUT type="submit" value="Retrieve Data" id=submit1 name=submit1></TD>

2. Save the file as UpdateProduct.asp in the Learn_ASP folder.

Notice that the ASP page shown above contains two HTML forms: Form1 and Form2.

Form1 (whose code appears at the bottom of the ASP page) displays a drop-down list of products for the user to select. This form uses the GET method to send data to the server. This means that you will see the query string in the browser's address bar once you click the Retrieve Data button (see Figures 28-30 and 28-31).

Form2 (whose code appears higher in the ASP page) displays two text boxes with Unit Price and Units in Stock values for the product that was selected from the drop-down list on Form1. The user can modify the data in these text boxes. This form uses the POST method to send the information to

Taking Your VBA Programming Skills to the Web the server. The submitted information will not be visible in the browser's address bar. This form will be submitted to itself after the user clicks the Update Data button. Two hidden text fields are placed on Form2 to store information about the retrieved Product ID and Product Name:

<Input Type=hidden Name=txtProductId Value="<% =rst("ProductId") %>"> <Input Type=hidden Name=txtProductName Value="<% =rst("ProductName") %>">

In this example, the information stored in hidden fields is used by the VBScript code further in the .asp file to create an SQL UPDATE statement and write an information message in the browser. Hidden form fields are often used with the POST method to hide information from the user.

The first VBScript code segment establishes a connection to the Northwind database and creates an instance of the Recordset object. Next, we check if a selection was made from the drop-down list. If the user made a product selection and clicked the Retrieve Data button, we open the recordset:

rst.Open(strSQL & Request.QueryString("ProductId")), conn, 0, 1

The Open method of the Recordset object is used to issue an SQL SELECT statement with the WHERE clause that specifies which record should be retrieved. We placed the SELECT statement in the strSQL variable. The Open method also specifies the connection to the database (conn), the cursor type (adOpenForwardOnly = 0), and the lock type (adLockReadOnly = 1). The recordset is opened to retrieve only the data that the user is allowed to modify. The data is placed in a table (see the HTML code segment). Once the data is retrieved, the recordset is closed.

The next VBScript code segment runs after the user clicks the Update Data button on Form2. When the form is posted, all controls, including the command buttons, are posted with it. Using the IsEmpty function you can find out if the user clicked the command button:

If Not IsEmpty(Request.Form("submit2")) Then

Prior to submitting the data to the server for insertion into the Products table, the code checks whether the Unit Price and Units in Stock text boxes contain any data. If either of these fields is empty (the user may have erased the data completely), a validation message is sent to the browser and the user must request the product again from the drop-down list if he or she wants to continue. On the other hand, if there is data in both text fields (even if the user has not made any changes to the original data), clicking the Update button on the form will send the SQL UPDATE statement to the server. As a result, the user will see the name of the product he or she updated together with the SQL UPDATE statement that was executed.

The last code segment creates a recordset to populate a drop-down list with product names. You should already be familiar with this code as it was demonstrated in the previous hands-on exercise.

3. Open your browser and type http://localhost/NorthDB/UpdatePro-duct.asp in the address bar.

Part V

4. Press Enter or click Go.

5. Select the product as shown in Figure 28-30 and click the Retrieve Data button.

¿1 Update Product Information

EMI®

File Edt View Favorites Tools Help

Qßaek » Q Q g) - ¿ p Search '¿V' Favortes

0 ES •

Address http://localhoit/NorthDB/UpdatePfoduct.asp

1 1

Select a Product to Update SlSESHISílíMíi^Hll

IHK v 1 Retrieve Data

|-gjpore

Local intranet

Figure 28-30: When you request the UpdateProduct.asp file in your browser, a screen appears with a drop-down list where you can select a product you want to update.

Ü Update Product Information

00®

File Edit View Favorites Tools Help

»

Qßack - \x\ ¿ ¡ p Search ¿^Favorites jg) ¡¡¡^ jgj §

U B "

äss http¡f¿ocdhc^/TtothDB/UpdateProduct.asp?ftoduct!d-28

Rössle Sauerkraut (Product Id=28)

I Update Data

Select a Product to Update v [ Retrieve Data ]

Done Local intranet

Figure 28-31: When you select a product from the drop-down list and click the Retrieve Data button, the selected product's unit price and units in stock data are retrieved from the Products table and placed at the top of the page.

Figure 28-31: When you select a product from the drop-down list and click the Retrieve Data button, the selected product's unit price and units in stock data are retrieved from the Products table and placed at the top of the page.

The user can modify the original data in the text boxes and click the Update Data button. If the user clicks the Update Data button when information in missing in the Unit Price or Units in Stock text boxes, he is advised to enter the data and try again.

If the user clicks the Update Data button while the Unit Price and Units in Stock text boxes are not empty, the UPDATE statement is executed on the server and the submitted changes are inserted in the Products table. The user sees the page shown in Figure 28-32 in confirmation of the update request.

Taking Your VBA Programming Skills to the Web

Taking Your VBA Programming Skills to the Web

Figure 28-32: After submitting the product modification, the user sees the confirmation page with the UPDATE statement that was executed and is given an opportunity to continue by retrieving other products for modification.
0 0

Post a comment