ID: Q166911
The information in this article applies to:
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.
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:
1. Start Microsoft Access 97 and open the sample database Northwind.mdb.
2. 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."
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
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
6. 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://<ServerName>/Test/Customers.asp
record. Note that when the form is refreshee, the text box remains
empty.
For more information on how to create and modify ASP files, please refer to your Microsoft ASP online documentation.
Additional query words: delete clear remove control empty save
Keywords : kbinterop IntpWeb IntAsp
Version : 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowtoLast Reviewed: May 18, 1999