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

Article translations Article translations
Article ID: 812916 - View products that this article applies to.
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
Expand all | Collapse all

On This Page

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 "Symptoms" 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 "Applies to" section.

MORE INFORMATION

Steps to reproduce the behavior

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

    Note In Visual Studio 2005, click OK.
  5. Add a Button control to Form1.
  6. Double-click Button1 to add code for the click event of the Button1 control.
  7. 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 = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Pubs;Data Source=YourServer"
          cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
          cn.Open()
    
          ' Create a Command object.
          cmd = New ADODB.Command()
          With cmd
             .Name = "QueryCustomers"
             ' Set the SQL query to be executed.
             .CommandText = "SELECT TOP 5 stor_id AS CustName, Discount FROM DISCOUNTS WHERE DISCOUNT > ?"
             .CommandType = ADODB.CommandTypeEnum.adCmdText
             .Parameters.Append(.CreateParameter("DiscountIn", 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("Total Number of Customers with " & intDiscount & "% Discount is " & rs.RecordCount)
          rs.ActiveConnection = Nothing
          cmd.ActiveConnection = Nothing
    Note Replace YourServer with the name of your computer that is running SQL Server.
  8. On the Debug menu, click Start to run the application.
  9. Click Button1. In the dialog box, click OK.
  10. Click Button1 again. You receive the error message that is mentioned in the "Symptoms" section of this article.

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
308499 PRB: Unhandled exception when you set an ADO property to a string in Visual Basic .NET
188857 PRB: Use Open method to change CursorType and LockType
308047 How to open an ADO connection and Recordset objects by using Visual Basic .NET

Properties

Article ID: 812916 - Last Review: December 27, 2007 - Revision: 3.2
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
Keywords: 
kbtshoot kbhotfixserver kbqfe kbvs2002sp1sweep kbprogramming kbtsql kbdatabase kbcominterop kbwindowsforms kberrmsg kbbug KB812916

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com