ACC97: How to Modify ASP Forms to Allow Deleting from Text Boxes (166911)


The information in this article applies to:

  • Microsoft Access 97
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:
  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."
  3. 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
  4. 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
  5. Save and close the Customers.asp file.
  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

  7. 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.

REFERENCES

For more information on how to create and modify ASP files, please refer to your Microsoft ASP online documentation.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbhowto kbinterop kbProgramming KB166911
©2004 Microsoft Corporation. All rights reserved.