Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 280084 - Last Review: February 23, 2007 - Revision: 4.2
PRB: Parameters.Refresh Fails Using Parameter Token in Nested SQL
This article was previously published under Q280084
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"
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.
You must manually specify the data types for all parameters by using the ADO
CreateParameter function.
This behavior is by design.
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
For additional information, click the article numbers below
to view the articles in the Microsoft Knowledge Base:
235053Â
(http://kbalertz.com/Feedback.aspx?kbNumber=235053/EN-US/
)
PRB: E_FAIL Returned from Prepare() When SQL Statement Contains a Parameter in a Subquery
293790Â
(http://kbalertz.com/Feedback.aspx?kbNumber=293790/EN-US/
)
BUG: SQLDescribeParam Causes Syntax Error or Access Violation
APPLIES TO
- Microsoft Data Access Components 1.5
- Microsoft Data Access Components 2.0
- Microsoft Data Access Components 2.1
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft OLE DB Provider for SQL Server 7.0
- Microsoft ODBC Driver for Microsoft SQL Server 3.7
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please