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

• Microsoft Access 97

SYMPTOMS

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 Product Support professionals 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.

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

Additional query words: missing gone saved maintained

Keywords : kbinterop AccCon GnlDe IntpWeb IntAsp
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbbug