Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

ADO.net duplicate datareader problem. There must be a better way

Avatar of DaveChoiceTech
DaveChoiceTech asked on
Visual Basic.NET
2 Comments1 Solution343 ViewsLast Modified:


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;"

 gcCCXConn.Open()

 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

While drOne.Read()

      gcCCXCmd.CommandText = someOtherSqlString
      drTwo = gcCCXCmd.ExecuteReader()

End While

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

For instance,

Dim transaction As OleDbTransaction
transcaction = gcCCXConn.BeginTransaction()
gcCCXCmd.Transaction = transaction

For iIdx To 10
      AFcn(iIDX)
Next

transaction.commit

Where AFcn(iIdx as Integer) would have code like,

gcCCXCmd.CommandText = someSQLStringBasedOniIDX
gcCCXCmd.ExecuteNonQuery()



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?
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answers