Article ID: 826457
Article Last Modified on 11/29/2007
APPLIES TO
- Microsoft ActiveX Data Objects 2.7
- Microsoft ActiveX Data Objects 2.7
- Microsoft ActiveX Data Objects 2.7, when used with:
- Microsoft .NET Framework 1.1
- Microsoft .NET Framework 1.0
- Microsoft Visual Basic .NET 2003 Standard Edition
- Microsoft Visual Basic .NET 2002 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
In a Microsoft Visual Basic .NET application that uses Microsoft Component Object Model (COM) interoperability to access the members of the Microsoft ActiveX Data Objects Library, when you call the parameterless Command.Execute method to run an SQL batch file that retrieves data from a global temporary table, you may notice that this method retrieves incorrect or incomplete data.
Note You do not notice this behavior if you call the Command.Execute method to run an SQL batch file that retrieves data from a local temporary table.
CAUSE
You notice the behavior that is mentioned in the "Symptoms" section of this article when your SQL batch file contains code to retrieve data that depends on successfully completing an earlier call to the parameterless Command.Execute method. Additionally, this earlier call to the parameterless Command.Execute method runs another SQL batch file.
When you make the initial call to the parameterless Command.Execute method, the corresponding SQL batch file uses a Connection object to run. If you do not explicitly associate a Connection object with your Command object, the common language runtime implicitly creates a Connection object for your SQL batch file to use. Additionally, the common language runtime creates a Recordset object that remains in memory until the garbage collector releases this object. This Recordset object blocks the additional running of code that uses the current Connection object until the garbage collector performs garbage collection.
Because the current Connection object is blocked, when you make your next call to the Command.Execute method, the common language runtime creates another Connection object to run the next SQL batch file. Because the two SQL batch files use different Connection objects, a race condition may occur. If a race condition occurs, the code that is contained in this SQL batch file retrieves data from the global temporary table before the code that is contained in the initial SQL batch file manipulates the corresponding dependent data. Therefore, you notice the behavior that is mentioned in the "Symptoms" section of this article.
WORKAROUND
To work around this problem, pass the ExecuteOptionEnum.adExecuteNoRecords value to the Options parameter when call the Command.Execute method. When you pass this value, the common language runtime does not create a Recordset object that blocks your Connection object. Therefore, your next call to the Command.Execute method can use the same Connection object and you will not notice any race conditions. To do this, use code that is similar to the following sample code:
' Pass the ExecuteOptionEnum.adExecuteNoRecords value to the Command.Execute method. cmd.Execute(, , ADODB.ExecuteOptionEnum.adExecuteNoRecords)
Additionally, Microsoft recommends that you always implement the following coding best practices in your code:
Create and open an explicit Connection object, and then set the MultipleConnections property of this object to False. After you do this, you will receive an error message if the common language runtime tries to use a different Connection object to run your SQL batch files. You can use this error message to prevent the common language runtime from using a different Connection object. To do this, use code that is similar to the following sample code:
' Declare a new Connection object. Dim cn As ADODB.Connection ' Create and then open the Connection object. cn = New ADODB.Connection cn.Open(connString) ' Set the MultipleConnections property to False. cn.Properties("MultipleConnections").Value = FalseNote Replace
connStringwith a connection string that you can use.Explicitly associate your Connection object with the Command object so that the common language runtime does not implicitly create another Connection object. To do this, use code that is similar to the following sample code:
' Explicitly associate your Connection object with the Command object. cmd.ActiveConnection = cn
Explicitly close the Recordset object and the Connection object (in that order) when you finish using these objects so that the garbage collector can release these objects. To do this, use code that is similar to the following sample code:
' Explicitly close the Recordset object and the Connection object. rs.Close() cn.Close()
STATUS
This behavior is by design.
MORE INFORMATION
Steps to reproduce the behavior
- Start Microsoft Visual Studio .NET.
- Use Visual Basic .NET to create a Windows Application project. By default, the Form1.vb file is created.
- In your Visual Basic .NET project, add a project reference to the Microsoft ActiveX Data Objects Library.
Add the following code at the top of the Form1.vb file:
Imports ADODB
In your Form1.vb file, use code that is similar to the following sample code to call the parameterless Command.Execute method to run two SQL batch files:
' Use the strBatch1 variable to specify the first SQL batch file to run. cmd.CommandText = strBatch1 ' Run the first SQL batch file. cmd.Execute() ' Use the strBatch2 variable to specify the second SQL batch file to run. cmd.CommandText = strBatch2 ' Run the second SQL batch file. cmd.Execute()
- Build and then run your application. You may notice the behavior that is mentioned in the "Symptoms" section of this article.
REFERENCES
For more information, visit the following Microsoft Developer Network (MSDN) Web sites:
Keywords: kbgarbagecollect kbsqlprog kbinterop kbdatapooling kbdatabase kbprogramming kbtable kbprb kbcominterop KB826457
