Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/172898

From BetaArchive Wiki

Article ID: 172898

Article Last Modified on 5/2/2006



APPLIES TO

  • Microsoft Visual InterDev 1.0 Standard Edition



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]

  1. Close the query.
  2. 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:




Additional query words: 80040e10

Keywords: kbbug kberrmsg kbpending KB172898