PRB: Parameters.Refresh Fails Using Parameter Token in Nested SQL (280084)

SYMPTOMS

When you call the ActiveX Data Objects (ADO) Parameters.Refresh function on a SQL statement that contains parameter tokens inside of a nested SELECT statement, the following error may be reported by the SQL Server OLEDB Provider and SQL Server ODBC driver:

"Run-time error '-2147467259 (80004005)' Syntax error or access violation"

CAUSE

When you call Parameters.Refresh, ADO attempts to obtain parameter information from the OLEDB provider by using the OLEDB command ICommandWithParameters::GetParameterInfo. The SQL Server ODBC driver and SQL Server OLEDB Provider cannot resolve parameter information inside of nested SELECT queries, which causes the internal call to GetParameterInfo to fail.

MORE INFORMATION

Steps to Reproduce Behavior

The following Microsoft Visual Basic code sample demonstrates the error and the resolution:

' This demonstrates the error.
Sub ParamFailTest()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.recordset
    Set conn = New ADODB.Connection
    conn.Open "Provider=SQLOLEDB;Server=YourSQLServer;Database=Pubs;UID=YourUserID;PWD=YourPassword;"
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = conn
    cmd.CommandText = "select * from authors where au_id in " & _
                      "(select au_id from authors where au_id=?)"
    cmd.Parameters.Refresh ' <--- Code will fail here.
    cmd.Parameters(0).value = "409-56-7008"
    Set rs = cmd.Execute
    While Not rs.EOF
        Debug.Print rs.Fields("au_id").value
        rs.MoveNext
    Wend
End Sub

' This demonstrates how to manually set the parameter.
Sub ParamSuccessTest()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.recordset
    Set conn = New ADODB.Connection
    conn.Open "Provider=SQLOLEDB;Server=YourSQLServer;Database=Pubs;UID=YourUserID;PWD=YourPassword;"
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = conn
    cmd.CommandText = "select * from authors where au_id in " & _
                      "(select au_id from authors where au_id=?)"
    cmd.Parameters.Append cmd.CreateParameter("au_id", adVarChar, _                                                adParamInput, 20)
    cmd.Parameters("au_id").value = "409-56-7008"
    Set rs = cmd.Execute
    While Not rs.EOF
        Debug.Print rs.Fields("au_id").value
        rs.MoveNext
    Wend
End Sub

REFERENCES

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

235053 PRB: E_FAIL Returned from Prepare() When SQL Statement Contains a Parameter in a Subquery

293790 BUG: SQLDescribeParam Causes Syntax Error or Access Violation