Microsoft KB Archive/280084

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

Article ID: 280084

Article Last Modified on 2/23/2007

-

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 OLE DB Provider for SQL Server 7.01
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft OLE DB Provider for SQL Server 2000 2000.80.194
 * Microsoft ODBC Driver for SQL Server 2000 2000.80.194

-



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:

&quot;Run-time error '-2147467259 (80004005)' Syntax error or access violation&quot;



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.



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



STATUS
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 &quot;Provider=SQLOLEDB;Server=YourSQLServer;Database=Pubs;UID=YourUserID;PWD=YourPassword;&quot; Set cmd = New ADODB.Command Set cmd.ActiveConnection = conn cmd.CommandText = &quot;select * from authors where au_id in &quot; & _ &quot;(select au_id from authors where au_id=?)&quot; cmd.Parameters.Refresh ' <--- Code will fail here. cmd.Parameters(0).value = &quot;409-56-7008&quot; Set rs = cmd.Execute While Not rs.EOF Debug.Print rs.Fields(&quot;au_id&quot;).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 &quot;Provider=SQLOLEDB;Server=YourSQLServer;Database=Pubs;UID=YourUserID;PWD=YourPassword;&quot; Set cmd = New ADODB.Command Set cmd.ActiveConnection = conn cmd.CommandText = &quot;select * from authors where au_id in &quot; & _ &quot;(select au_id from authors where au_id=?)&quot; cmd.Parameters.Append cmd.CreateParameter(&quot;au_id&quot;, adVarChar, _                                               adParamInput, 20) cmd.Parameters(&quot;au_id&quot;).value = &quot;409-56-7008&quot; Set rs = cmd.Execute While Not rs.EOF Debug.Print rs.Fields(&quot;au_id&quot;).value rs.MoveNext Wend End Sub

