I have defined two global variables that are intended to be initialized on loading and then used
through out my application.
These variables are declared and initialized as follows,
Public gcCCXConn As OleDbConnection
Public gcCCXCmd As OleDbCommand
' Connect to the CCXlate database
gcCCXConn = New OleDbConnection()
gcCCXConn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = "ccxlate.mdb;"
gcCCXCmd = New OleDbCommand()
gcCCXCmd.Connection = gcCCXConn
The idea was to a global OleDBCommand object that already had the connection initialized.
Another reason for this approach was to make transaction coding easier.
For instance in my application I create two datareaders an
Dim drOne As OleDbDataReader
Dim drTwo As OleDbDataReader
gcCCXCmd.CommandText = someSqlString
drOne = gcCCXCmd.ExecuteReader()
The problem happens in a loop when I try to create the second datareader
gcCCXCmd.CommandText = someOtherSqlString
drTwo = gcCCXCmd.ExecuteReader()
This results in an error stating
"There is already an open DataReader associated with this Command which must be closed first."
Clearly a reference to the data reader is maintained by OleDbCommand object.
I found this odd since I do not see such a member in the documentation.
I suppose I could work around this by creating a second command object whenever I need a datareader
but annother reason for using a global OleDbCommand instance was for make transaction coding easier
Dim transaction As OleDbTransaction
transcaction = gcCCXConn.BeginTransaction()
gcCCXCmd.Transaction = transaction
For iIdx To 10
Where AFcn(iIdx as Integer) would have code like,
gcCCXCmd.CommandText = someSQLStringBasedOniIDX
I'm new to ADO.NET so I'm sure there a much better way of accomplishing all of this.
Can any help me out?