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

View products that this article applies to.

This article was previously published under Q280084

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"

↑ Back to the top

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.

↑ Back to the top

Resolution

You must manually specify the data types for all parameters by using the ADO CreateParameter function.

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

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

↑ Back to the top