BUG: Parameter Mismatch with Subqueries When Using Access ODBC Driver

• Microsoft ODBC Driver for Access, version 4.0
• Microsoft Data Access Components versions 2.1, 2.1 (GA), 2.1 SP1, 2.1 SP2

SYMPTOMS

When executing a parameterized query that contains a subquery, when using the Microsoft Open Database Connectivity (ODBC) Driver for Access, various unexpected problems occur.

Reported problems include unexpected results (no records returned or the wrong records returned) or other errors indicating a type mismatch or a numeric value out of range.

CAUSE

This problem is caused by a bug in the underlying Microsoft Jet 4.0 engine that is included with the Microsoft Data Access Components (MDAC) versions noted above.

Parameters markers in ODBC are typically mapped from left to right with no regard as to whether they are in the main query or a subquery. When you have a main query and a subquery, Jet maps the subquery parameters first and maps the main query parameters next. Therefore, in most cases, the parameters are mapped in a different order than expected. This parameter mapping order means that unexpected parameter values are passed in to one or more of the parameters and can result in unexpected query results or type mismatch errors (for example, if a text value is passed into a numeric parameter).

For example, the following SQL query has four parameters for fields A, B, C, and D:

SELECT * FROM MainTable 
WHERE A=? AND B=? AND 
E IN ( SELECT E FROM SubQueryTable WHERE C=? AND D=?)  

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next service pack for Microsoft Data Access Components that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

Sub ParameterMisMatchDemo()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.recordset
Dim strSQL As String

    ' This test requires a copy of the Microsoft Access sample database
    ' NorthWind.mdb in the C:\ folder.
    conn.Open "Driver=Microsoft Access Driver (*.mdb);" & _
              "DBQ=C:\NorthWind.mdb;"
    
    ' Build SQL statement. Note that there is a parameter marker
    ' in the main query (Country=?) as well as a parameter marker
    ' in the subquery (OrderID=?).
    strSQL = "Select CustomerID From Customers " & _
             "Where Country=? And CustomerID In " & _
             "(Select CustomerID From Orders Where OrderID=?)"

    cmd.ActiveConnection = conn
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText

    ' Manually add parameters in the normal "left to right" order.
    cmd.parameters.Append cmd.CreateParameter("Country", _
           adVarChar, adParamInput, 15)
    cmd.parameters.Append cmd.CreateParameter("OrderID", _
           adInteger, adParamInput)

    ' Set parameter values. These values should filter down the
    ' resultset to one record with a CustomerID of "HUNGC".
    cmd.parameters("Country").Value = "USA"
    cmd.parameters("OrderID").Value = 10375
    Set rs = cmd.Execute

    ' This should return CustomerID "HUNGC", but instead the following
    ' error is returned: "Either BOF or EOF is True, or the current record 
    ' has been deleted; the operation requested by the application requires         
    ' a current record."
    MsgBox rs.fields("CustomerID").Value

End Sub 

Additional query words: empty recordset crecordset

Keywords : kbADO kbDatabase kbJET kbMDAC kbODBC kbOLEDB kbGrpMDAC kbDSupport kbMDAC210bug
Version : WINDOWS:2.1,2.1 (GA),2.1 SP1,2.1 SP2,4.0
Platform : WINDOWS
Issue type : kbbug