Microsoft KB Archive/166911

= ACC97: How to Modify ASP Forms to Allow Deleting from Text Boxes =

Article ID: 166911

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q166911



SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

When you delete the contents of a text box on an ASP form, and then click the Commit button, the data reappears. This article demonstrates how you can modify the ASP form to allow you to delete data from a text box.

NOTE: This article contains information about editing ASP files. It assumes that you are familiar with Active Server, Visual Basic Scripting, and editing HTML files. Microsoft Access Product Support professionals do not support modification of any HTML, HTX, IDC, or ASP files.



MORE INFORMATION
The VB Script that Microsoft Access generates when you export a form to ASP format performs tests on each field to determine which ones it will update when you click Commit. One of the tests checks whether or not a QueryString variable is equal to a zero length string (""). If it is not equal to "", the field is updated. However, deleting the data in a text box causes its QueryString variable to equal "", so the field is not updated.

You can modify the ASP script that Microsoft Access generates so that it checks for the existence of the QueryString variable. If it exists and it is equal to a zero length string, the field will be updated with a NULL value. If it does not exist, the field will not be updated.

The steps in the following example show you how to modify the ASP file that Microsoft Access generates:  Start Microsoft Access 97 and open the sample database Northwind.mdb. Export the Customers form to ASP format in a folder on your Web server where you have Execute permission. This step creates two files: Customers.asp and Customersalx.asp.

For more information about exporting forms to ASP format, search the Help Index for "ASP files."  Use Notepad or another text editor to open the Customers.asp file. In the middle of the script you will see the following lines of code: If cstr(Request.QueryString("Country")) <> "" Then rs.Fields("Country").Value = Request.QueryString("Country") End If     If cstr(Request.QueryString("Fax")) <> "" Then rs.Fields("Fax").Value = Request.QueryString("Fax") End If     If cstr(Request.QueryString("Phone")) <> "" Then rs.Fields("Phone").Value = Request.QueryString("Phone") End If     If cstr(Request.QueryString("PostalCode")) <> "" Then rs.Fields("PostalCode").Value = Request.QueryString("PostalCode") End If     If cstr(Request.QueryString("Region")) <> "" Then rs.Fields("Region").Value = Request.QueryString("Region") End If     If cstr(Request.QueryString("City")) <> "" Then rs.Fields("City").Value = Request.QueryString("City") End If     If cstr(Request.QueryString("Address")) <> "" Then rs.Fields("Address").Value = Request.QueryString("Address") End If     If cstr(Request.QueryString("ContactTitle")) <> "" Then rs.Fields("ContactTitle").Value = _ Request.QueryString("ContactTitle") End If     If cstr(Request.QueryString("ContactName")) <> "" Then rs.Fields("ContactName").Value = _ Request.QueryString("ContactName") End If     If cstr(Request.QueryString("CompanyName")) <> "" Then rs.Fields("CompanyName").Value = _ Request.QueryString("CompanyName") End If     If cstr(Request.QueryString("CustomerID")) <> "" Then rs.Fields("CustomerID").Value = Request.QueryString("CustomerID") End If                     Modify the code to appear as it does below. The nested If...End If blocks in this example are not required; they allow you to choose setting the field value to either NULL or "", depending on the requirements of the field in your table. You can simplify the code by just setting the value of the field equal to the value of the QueryString variable, whatever it is. For example, if the QueryString variable "Country" exists, you can set rs.Fields("Country").Value = Request.QueryString("Country"); if the variable contains nothing, the field is updated to a zero length string: ' If the QueryString variable has been set If Request.QueryString("Country").count <> 0 Then ' Update the field with the QueryString value. rs.Fields("Country").Value = Request.QueryString("Country") End If NOTE: The following code assumes that the fields in your Microsoft Access 97 table have their Required property set to No. If Required is set to Yes, the update will fail because you are trying to set the value of a required field to NULL. If the fields in the table have their AllowZeroLength property set to Yes, you can set the fields equal to a zero length string.

Replace the code in step 3 with the modified code below: ' If the QueryString variable has been set If Request.QueryString("Country").count <> 0 Then ' And if the QueryString variable is not a zero length string If cstr(Request.QueryString("Country")) <> "" Then ' Then update the field with the new data. rs.Fields("Country").Value = Request.QueryString("Country") Else ' Otherwise set the value of the field to NULL. rs.Fields("Country").Value = NULL ' If you are updating a required field, you can use the following ' line of code instead of the one above, as long as the ' AllowZeroLength property of the required field is set to Yes: '        '  rs.Fields("Country").Value = "" End If     End If

' Repeat the pattern for the remaining fields on the form. If Request.QueryString("Fax").count <> 0 Then If cstr(Request.QueryString("Fax")) <> "" Then rs.Fields("Fax").Value = Request.QueryString("Fax") Else rs.Fields("Fax").Value = NULL End If     End If      If Request.QueryString("Phone").count <> 0 Then If cstr(Request.QueryString("Phone")) <> "" Then rs.Fields("Phone").Value = Request.QueryString("Phone") Else rs.Fields("Phone").Value = NULL End If     End If      If Request.QueryString("PostalCode").count <> 0 Then If cstr(Request.QueryString("PostalCode")) <> "" Then rs.Fields("PostalCode").Value = _ Request.QueryString("PostalCode") Else rs.Fields("PostalCode").Value = NULL End If     End If      If Request.QueryString("Region").count <> 0 Then If cstr(Request.QueryString("Region")) <> "" Then rs.Fields("Region").Value = Request.QueryString("Region") Else rs.Fields("Region").Value = NULL End If     End If      If Request.QueryString("City").count <> 0 Then If cstr(Request.QueryString("City")) <> "" Then rs.Fields("City").Value = Request.QueryString("City") Else rs.Fields("City").Value = NULL End If     End If      If Request.QueryString("Address").count <> 0 Then If cstr(Request.QueryString("Address")) <> "" Then rs.Fields("Address").Value = Request.QueryString("Address") Else rs.Fields("Address").Value = NULL End If     End If      If Request.QueryString("ContactTitle").count <> 0 Then If cstr(Request.QueryString("ContactTitle")) <> "" Then rs.Fields("ContactTitle").Value = _ Request.QueryString("ContactTitle") Else rs.Fields("ContactTitle").Value = NULL End If     End If      If Request.QueryString("ContactName").count <> 0 Then If cstr(Request.QueryString("ContactName")) <> "" Then rs.Fields("ContactName").Value = _ Request.QueryString("ContactName") Else rs.Fields("ContactName").Value = NULL End If     End If      If Request.QueryString("CompanyName").count <> 0 Then If cstr(Request.QueryString("CompanyName")) <> "" Then rs.Fields("CompanyName").Value = _ Request.QueryString("CompanyName") Else rs.Fields("CompanyName").Value = NULL End If     End If      If Request.QueryString("CustomerID").count <> 0 Then If cstr(Request.QueryString("CustomerID")) <> "" Then rs.Fields("CustomerID").Value = _ Request.QueryString("CustomerID") Else 'CustomerID cannot be blank, so there is no other option End If     End If                     Save and close the Customers.asp file. Start Microsoft Internet Explorer and open the Customers.asp file on your Web server by typing the Uniform Resource Locator (URL) in the Address box, for example:

http:///Test/Customers.asp

</li> Delete the text from the Title box on the form, and then commit the record. Note that when the form is refreshee, the text box remains empty.</li></ol>

<div class="references_section">