BUG: Incorrect SQL Generated for Access Query with String Parameter (172898)


The information in this article applies to:

  • Microsoft Visual InterDev 1.0
This article was previously published under Q172898

SYMPTOMS

The design time controls in Visual InterDev version 1.0 generates incorrect SQL in Active Server Pages (ASP) for an Access query with a string parameter. Single quotes are missing around the string parameter in the SQL statement.

When attempting to run the page with the invalid SQL the following error message may be seen:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10' [Microsoft][ODBC Microsoft Access 97 Driver] Too few parameters. Expected 1.

/SamplesWeb/KBTestFolder/KBTest.asp, line 31

CAUSE

The Access ODBC driver does not support parameterized queries. As a result, the design-time control has no way of determining the expected type for each expression when using an Access data connection.

When setting up criteria inside Visual InterDev's Query Designer the criteria should be entered in the following format to enable the query to run inside the Query Designer for testing purposes: 
   = [CatName]

The problem is occurs when the Query Designer is closed and incorrect SQL is then written to the ASP page.

RESOLUTION

The only workaround for this problem is to put single quotes outside the square brackets when entering the parameterized query. This forces the SQL being generated to include single quotes around the String parameter. However, this will break the ability to execute the query in the query builder. The criteria should be entered as follows: 
   = '[CatName]'

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

If you attempt to execute a query in the Query Builder that contains expressions and you are connected to an Access database, the design-time controls always assume the type of the expression is a string. This means that internally they always put single-quotes around the expression before executing it. This works fine if you are comparing against a text field. However, it will fail if you are coming against a numeric field. Since the design-time controls assume it is a numeric field in this case, the user has control over whether quotes are put around the expression.

Steps to Reproduce Behavior


  1. Open a Web project that contains a data connection to an Access Database (such as Northwind.mdb).
  2. Insert an ASP into the project.
  3. Open the ASP file.
  4. Insert ActiveX control and click Data Command control.
  5. Select a data source and click SQL Builder.
  6. Enter the following SQL statement in the query pane:

    SELECT categoryid,categoryname FROM categories WHERE categoryname=[CatName]
  7. Close the query.
  8. Close the object editor
The runtime HTML contains the following line: 
   cmdTemp.CommandText = "SELECT categoryid, categoryname FROM categories
   WHERE (CategoryName = " & CatName & ")"

This SQL is incorrect because single quotes are missing around the string parameter "CatName". The expected SQL should be as follows: 
   cmdTemp.CommandText = "SELECT CategoryID, CategoryName FROM Categories
   WHERE (CategoryName = '" & CatName & "')"

REFERENCES

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:

http://support.microsoft.com/search/default.aspx?qu=vinterdev


Modification Type:MajorLast Reviewed:5/2/2006
Keywords:kbbug kberrmsg kbpending KB172898
©2004 Microsoft Corporation. All rights reserved.