ACC97: Data in ASP Fields with Spaces Not Committed to Access DB

• Microsoft Access 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you try to enter or change data on an ASP file that is linked to a Microsoft Access database, the new data will be committed only for fields that do not have spaces in their field names. You will not receive any errors about the data not being saved.

RESOLUTION

In order to have the data saved back to a Microsoft Access database, make sure that the underlying recordset does not contain field names that have space in them. Three possible solutions are as follows:

• Redesign your table to include only field names that do not have spaces, and then recreate your ASP files.

  NOTE: If you select this option, be sure to update all of the other objects in the database which use the newly modified fields. Otherwise, you will receive errors when you try use those objects.

• Build a query based on the original ASP record source and alias the fields in the query that contain spaces. Once the new query is created, you can recreate your ASP file based on the new query.
• Modify the ALX file that is linked to a Microsoft Access database as follows:

  NOTE: This solution contains information about editing ASP files and assumes that you are familiar with editing HTML files, Active Server Pages, and Visual Basic Scripting. Microsoft Access Technical Support engineers do not support customization of any HTML, HTX, IDC, or ASP files.

  The following example demonstrates how to change the VBScript code in an ASP file. To modify the VBScript for a particular file, follow these steps:

        1. Open the <object name>alx.asp file. It has a function that looks
           something like this:
  
           NOTE: In the following sample code, an underscore (_) at the end
           of a line is used as a line-continuation character. Remove the
           underscore from the end of the line when re-creating this code.
  
              Function GetCtrlQueryString()
              Dim szRet
              Dim i
                 for i = 1 to cMaxCtrls
                    if rgszCtrls(i, 1) = "" Then Exit For
                 szRet = szRet & rgszCtrls(i, 2) & "=" &
              MakeHTMLValue(Test_Formalx.Controls(rgszCtrls(i, 1)).Value) & _
              "&"
                 next
              GetCtrlQueryString = szRet
              End Function
  
        2. Modify the function, changing only the line that sets szRet so it
           looks like the following (note the use of MakeHTMLValue):
  
              Function GetCtrlQueryString()
              Dim szRet
              Dim i
                 for i = 1 to cMaxCtrls
                    if rgszCtrls(i, 1) = "" Then Exit For
                 szRet = szRet & MakeHTMLValue(rgszCtrls(i, 2)) & "=" &
              MakeHTMLValue(Test_Formalx.Controls(rgszCtrls(i, 1)).Value) & _
              "&"
                 next
              GetCtrlQueryString = szRet
              End Function
  
  

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Problem

1. Create the following new table called Table1:

         Table: Table1
         -------------------------------
         Field Name: ID
            Data Type: Counter
            Indexed: Yes (No Duplicates)
         Field Name: FirstName
            Data Type: Text
         Field Name: Last Name
            Data Type: Text
   
   

2. Create an AutoForm based on this table.

3. Use the "Publish to the Web Wizard" to create an ASP File. Please note this must be publish to a site which supports ASP file formats and has a system DSN assigned to your database.

4. Use a Web browser to open your new ASP file.

5. Enter and save data in your ASP form.

6. Check the underlining data in the table.

   Note that the data you entered into the FirstName field is saved, but that the data you entered into the Last Name field is not saved.

REFERENCES

For more information about aliasing fields, search the Help Index for "alias," or ask the Microsoft Access 97 Office Assistant.