Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q280084

↑ Back to the top


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


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

↑ Back to the top


Keywords: KB280084, kbprb

↑ Back to the top

Article Info
Article ID : 280084
Revision : 6
Created on : 2/23/2007
Published on : 2/23/2007
Exists online : False
Views : 331