Microsoft KB Archive/243834

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

Article ID: 243834

Article Last Modified on 7/14/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q243834



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



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]") & "'))"



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



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.</li> On the File menu, click Export.</li> In the Export Query 'qryASP' To dialog box, change the Save as type box to Microsoft Active Server Pages (*.asp).</li> Save the file as qryAsp.asp in a Web folder on the Web server.</li> Use a Web browser to view the file. Note that you receive the error message that is mentioned in the "Symptoms" section.</li></ol>

Additional query words: pra saved error 80040e14

Keywords: kberrmsg kbbug kbpending KB243834

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.