ACC2000: Syntax Error When You Export a Parameter Query to Active Server Page (ASP) Format (243834)

SYMPTOMS

When you export a Microsoft Access parameter query in the Active Server Page (ASP) format and then view the ASP page from a browser, you may receive an error message similar to the following:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '((([Customers].[CustomerID])=stomer ID]))'.

/test/qryAsp.ASP, line 22

where the expected syntax would have been:

((([Customers].[CustomerID])=[Enter Customer ID]))

CAUSE

When the query is saved to ASP, two files are generated: one in ASP format, the other in HTML format. The parameter is written to both, but in the ASP file, the parameter portion that references Request.QueryString has been incorrectly parsed.

Further, if the query is re-exported to the same name, Access requests to overwrite the ASP file, but does not overwrite the HTML file. This results in a mismatch with Request.QueryString.

For example, in a query that is based on the Customer table in the sample database Northwind.mdb, the SQL statement might read:

SELECT Customers.CompanyName, Customers.ContactName
FROM Customers
WHERE (((Customers.CustomerID)=[Enter Customer ID]));

When you export the query to ASP format, the syntax becomes:

"SELECT Customers.CompanyName, Customers.ContactName 
FROM Customers 
WHERE (((Customers.CustomerID)=" & Request.QueryString("[Enter Cu") & "stomer ID]))   "

RESOLUTION

To get the correct result, use Notepad or an HTML editor to correct the syntax. For example, in the "Steps to Reproduce the Behavior" section later in this article, the following syntax is generated when the query is exported:

sql = "SELECT Customers.CompanyName, Customers.ContactName 
FROM Customers 
WHERE (((Customers.CustomerID)=" & Request.QueryString("[Enter Cu") & _
"stomer ID]))   "

Edit the SQL statement to read as follows:

sql = "SELECT [Customers].[CustomerID], [Customers].[CompanyName], _
      [Customers].[ContactName]  
FROM Customers  
WHERE ((([Customers].[CustomerID])='" & _
   Request.QueryString("[Enter CustomerID]") & "'))"

MORE INFORMATION

Note that in the generated SQL statement, the string delimiters (apostrophes) are missing because Access does not take the data type into consideration when it exports the query. If the string delimiters are omitted from the SQL statement, you receive the following error when you view the ASP page:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E10) [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/asptest/qryAsp.ASP, line 22

Steps to Reproduce the Behavior

On a Web server that has the ASP components installed, create a System DSN that points to a copy of the sample database Northwind.mdb.
Open the database that the DSN points to.

Create the following query based on the Customers table, and then save it as qryAsp:

   Query: qryAsp
   --------------------------------
   Type: Select Query

   Field: CustomerID
   Table: Customers
   Criteria: "=[Enter Customer ID]"

   Field: CompanyName
   Table: Customers

   Field: ContactName
   Table: Customers

Close the query, and then ensure that it is selected in the Database window.
On the File menu, click Export.
In the Export Query 'qryASP' To dialog box, change the Save as type box to Microsoft Active Server Pages (*.asp).
Save the file as qryAsp.asp in a Web folder on the Web server.
Use a Web browser to view the file. Note that you receive the error message that is mentioned in the "Symptoms" section.