Microsoft KB Archive/172898
Article ID: 172898
Article Last Modified on 5/2/2006
- Microsoft Visual InterDev 1.0 Standard Edition
This article was previously published under Q172898
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:
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:
The problem is occurs when the Query Designer is closed and incorrect SQL is then written to the ASP page.
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:
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.
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
- Open a Web project that contains a data connection to an Access Database (such as Northwind.mdb).
- Insert an ASP into the project.
- Open the ASP file.
- Insert ActiveX control and click Data Command control.
- Select a data source and click SQL Builder.
- Enter the following SQL statement in the query pane:
SELECT categoryid,categoryname FROM categories WHERE categoryname=[CatName]
- Close the query.
- 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 & "')"
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:
Additional query words: 80040e10
Keywords: kbbug kberrmsg kbpending KB172898