Microsoft KB Archive/163948

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

Article ID: 163948

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q163948



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



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:   Open the 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   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  </li></ul>

<div class="status_section">

STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 97.

<div class="moreinformation_section">

Steps to Reproduce Problem
  Create the following new table called Table1: <pre class="fixed_text">     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 </li> Create an AutoForm based on this table.</li> 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.</li> Use a Web browser to open your new ASP file.</li> Enter and save data in your ASP form.</li> 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.</li></ol>

<div class="references_section">