Microsoft KB Archive/812916

= BUG: You receive a &quot;Syntax error or access violation&quot; error message in ADO.NET when you run a query two times =

Article ID: 812916

Article Last Modified on 12/27/2007

-

APPLIES TO


 * Microsoft ADO.NET 2.0
 * Microsoft ADO.NET 1.0
 * Microsoft Visual Basic 2005
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition

-





Caution ADO and ADO MD have not been fully tested in a Microsoft .NET Framework environment. They may cause intermittent issues, especially in service-based applications or in multithreaded applications. The techniques that are discussed in this article should only be used as a temporary measure during migration to ADO.NET. You should only use these techniques after you have conducted complete testing to make sure that there are no compatibility issues. Any issues that are caused by using ADO or ADO MD in this manner are unsupported. For more information, see the following article in the Microsoft Knowledge Base:

840667 You receive unexpected errors when using ADO and ADO MD in a .NET Framework application



SYMPTOMS
When you use the ActiveX Data Objects (ADO) Connection object in Microsoft Visual Basic .NET or Microsoft Visual Basic 2005 to run a SQL query that is associated with the ADODB.Command object two times consecutively, you may receive the following error message similar to:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in microsoft.visualbasic.dll

Additional information: Syntax error or access violation



CAUSE
When you use the ADODB.Connection object to run a SQL query that is associated with the ADODB.Command object, late binding is used. The error that is mentioned in the &quot;Symptoms&quot; section occurs because late binding the COM interop caches the results of the GetIDsOfNames method of the IDispatch Interface.



WORKAROUND
To work around this bug, use early binding instead of late binding to run the SQL query. You can use the Excecute method of the ADODB.Command object to run the SQL query, as shown in the following code: myRecordset = myCommand.Execute(, New Object {myParameter})



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



Steps to reproduce the behavior
 Create a new Microsoft Windows Application by using Visual Basic .NET or Microsoft Visual Basic 2005. By default, Form1.vb is created. In Solution Explorer, right-click References, and then click Add Reference. In the Add Reference window, click the COM tab. In the Component Name list, click Microsoft ActiveX Data Objects 2.7 Library. Click Select, and then click OK.

Note In Visual Studio 2005, click OK. Add a Button control to Form1.</li> Double-click Button1 to add code for the click event of the Button1 control.</li>  Add the following code to the Button1_Click event: Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim cmd As ADODB.Command Dim intDiscount As Short

' Open the Connection. cn = New ADODB.Connection cn.ConnectionString = &quot;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Pubs;Data Source=YourServer&quot; cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient cn.Open

' Create a Command object. cmd = New ADODB.Command With cmd .Name = &quot;QueryCustomers&quot; ' Set the SQL query to be executed. .CommandText = &quot;SELECT TOP 5 stor_id AS CustName, Discount FROM DISCOUNTS WHERE DISCOUNT > ?&quot; .CommandType = ADODB.CommandTypeEnum.adCmdText .Parameters.Append(.CreateParameter(&quot;DiscountIn&quot;, ADODB.DataTypeEnum.adDouble, ADODB.ParameterDirectionEnum.adParamInput)) .ActiveConnection = cn     End With

' Create a Recordset object to get the results. rs = New ADODB.Recordset ' Execute the query. cn.QueryCustomers(intDiscount, rs) MsgBox(&quot;Total Number of Customers with &quot; & intDiscount & &quot;% Discount is &quot; & rs.RecordCount) rs.ActiveConnection = Nothing cmd.ActiveConnection = Nothing Note Replace  with the name of your computer that is running SQL Server. </li> On the Debug menu, click Start to run the application.</li> Click Button1. In the dialog box, click OK.</li> Click Button1 again. You receive the error message that is mentioned in the &quot;Symptoms&quot; section of this article.</li></ol>

<div class="references_section">