ExecuteReader requires an open and available Connection. The connection's current state is Open, Exe

  • Thread starter Thread starter fniles
  • Start date Start date
F

fniles

I am using VB.Net 2003 and MS Access (connecting using OleDBConnection).
I read using DataAdapter and DataSet, not DataReader.
When many people try to access the database at the same time, I get the
error "ExecuteReader requires an open and available Connection. The
connection's current state is Open, Executing." I do not use ExecuteReader,
why the error says ExecuteReader. What does it mean ?
When I get this error, is there a way for me to loop and wait until there is
an open and available Connection ?
Thank you.

Dim cmd As New OleDb.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet

With cmd
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
Catch ex As Exception
msgbox ex.message '-> error "ExecuteReader requires an open and
available Connection. The connection's current state is Open, Executing."
end try
 
When many people try to access the database at the same time, I get the
error "ExecuteReader requires an open and available Connection. The
connection's current state is Open, Executing." I do not use ExecuteReader,
why the error says ExecuteReader. What does it mean ?
Click to expand...

Internally, the Fill method uses ExecuteReader.
When I get this error, is there a way for me to loop and wait until there is
an open and available Connection ?
Thank you.

Dim cmd As New OleDb.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet

With cmd
.Connection = ConnectionDemoOLE
Click to expand...

Where is ConnectionDemoOLE defined? Is this a global variable? If
so, then try creating a new connections instead of using an existing
variable. Generally, it is better to create a connection, use it, and
then close it rather than to open a connection and keep it around for
a long time.


Chris
 
Thank you.
In this case ConnectionDemoOLE is a global variable.
I tried to create a new connection (connection pooling) and close it right
after I fill a dataset (I do not wait until it access the dataset), but I
get an error "Unspecified Error" at the Open method, I think it is caused
when the maximum pool size has been reached. I am using MS Access database.
Do you know what is the maximum pool size for MS Access ?
In my other posting I posted a question, when the maximum pool size has been
reached, can I somehow loop and wait until there is an available connection
again ? Or, maybe can I increase the connection time out in MS Access
connection ?

Try
ConnectionOLE = New OleDb.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = g_dbPath
.Open() --> IF MAX POOL SIZE HAS BEEN REACHED, IT WILL GO
to the Catch. How can I loop and wait to open the db until a connection is
available again ?
End With
Catch ex As Exception
Try
 
Thank you.
In this case ConnectionDemoOLE is a global variable.
I tried to create a new connection (connection pooling) and close it right
after I fill a dataset (I do not wait until it access the dataset), but I
get an error "Unspecified Error" at the Open method, I think it is caused
when the maximum pool size has been reached. I am using MS Access database.
Do you know what is the maximum pool size for MS Access ?
In my other posting I posted a question, when the maximum pool size has been
reached, can I somehow loop and wait until there is an available connection
again ? Or, maybe can I increase the connection time out in MS Access
connection ?

Try
ConnectionOLE = New OleDb.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = g_dbPath
.Open() --> IF MAX POOL SIZE HAS BEEN REACHED, IT WILL GO
to the Catch. How can I loop and wait to open the db until a connection is
available again ?
End With
Catch ex As Exception
Try
Click to expand...

I don't know the max pool size (I am thinking it's 110 by default),
but looking at your code your I don't see where you are disposing the
connection objects. AFAIK, If you don't dispose of them, they will sit
around idle taking up a space in the pool until the GC runs. This is
very bad as the GC will run when it wants to, meaning it might take a
while before any of the connections are released. I highly suggest you
wrap the connection objects in a Using block - that way you won't need
to wait for the GC.

Also, if you want to keep trying to open the connection you could do
something like this:

' Typed in message

Dim conn as new OleDbConnection(connString)
Using (conn)
Dim maxTries as Integer = 10
For i as Integer = 1 to maxTries
Try
conn.Open()
' The connection opened
Exit For
Catch
' The connection did not open
If i = maxTries Then
Msgbox("I give up!")
Return
Else
' Wait for a bit before trying again
Threading.Thread.Sleep(1000)
Continue For
End If
End Try
Next i
' Do whatever with the connection
End Using

I used a for loop to prevent a case where the program might get stuck
in an infinite loop, and a call to Thread.Sleep to prevent making too
many calls. You should adjust these settings to meet your needs.

Thanks,

Seth Rowe
 
Thank you very much

For this application, we are still using VB.Net 2003, and I do not see the
"Using" block or "Continue For". Do the following codes look OK ?

Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
As Boolean
Dim iMaxTries As Int16 = 5

Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
For iCnt As Int16 = 1 To iMaxTries
Try
.Open()
Exit For
Catch ex3 As Exception
If iCnt > iMaxTries Then
OpenDBDemoOLE = False
swError = New
StreamWriter(Application.StartupPath & "\ErrorLog.txt", True)
swError.Write(Now & " OpenDBDemoOLE - iCnt = " &
iCnt & " iMaxTries = " & iMaxTries & " error = " & ex3.Message & vbCrLf)
swError.Close()
swError = Nothing
Else
Thread.Sleep(1000)
End If
End Try
Next iCnt
End With
Catch ex As Exception
OpenDBDemoOLE = false
End Try

I do close the connection like in the following codes:

Dim cmd As New OleDb.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try

Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
If Not ConnectionDemoOLE Is Nothing Then
ConnectionDemoOLE.Close()
ConnectionDemoOLE = Nothing
End If
End Sub

Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
As Boolean
Dim iMaxTries As Int16 = 5

Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
For iCnt As Int16 = 1 To iMaxTries
Try
.Open()
Exit For
Catch
If iCnt <= iMaxTries Then
Thread.Sleep(1000)
End If
End Try
Next iCnt
End With
Catch ex As Exception
OpenDBDemoOLE = false
End Try
end Function
 
Thank you very much

For this application, we are still using VB.Net 2003, and I do not see the
"Using" block or "Continue For". Do the following codes look OK ?

Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
As Boolean
Dim iMaxTries As Int16 = 5

Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
For iCnt As Int16 = 1 To iMaxTries
Try
.Open()
Exit For
Catch ex3 As Exception
If iCnt > iMaxTries Then
OpenDBDemoOLE = False
swError = New
StreamWriter(Application.StartupPath & "\ErrorLog.txt", True)
swError.Write(Now & " OpenDBDemoOLE - iCnt = " &
iCnt & " iMaxTries = " & iMaxTries & " error = " & ex3.Message & vbCrLf)
swError.Close()
swError = Nothing
Else
Thread.Sleep(1000)
End If
End Try
Next iCnt
End With
Catch ex As Exception
OpenDBDemoOLE = false
End Try

I do close the connection like in the following codes:

Dim cmd As New OleDb.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try

Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
If Not ConnectionDemoOLE Is Nothing Then
ConnectionDemoOLE.Close()
ConnectionDemoOLE = Nothing
End If
End Sub

Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
As Boolean
Dim iMaxTries As Int16 = 5

Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
For iCnt As Int16 = 1 To iMaxTries
Try
.Open()
Exit For
Catch
If iCnt <= iMaxTries Then
Thread.Sleep(1000)
End If
End Try
Next iCnt
End With
Catch ex As Exception
OpenDBDemoOLE = false
End Try
end Function
Click to expand...

I didn't have time to read through your code, but I'll see if I can
get to it later. As for your other questions...
For this application, we are still using VB.Net 2003, and I do not see the
"Using" block or "Continue For"
Click to expand...

You can replace Using with a try...finally pair:

Dim conn as new OleDbConnection(connString)
try
conn.Open()
finally
conn.Dispose()
end try

And you should be able to ignore the continue for - it will continue
automatically.

Thanks,

Seth Rowe
 
basically; MS conned us all into buying .net 2.0 and SQL 2005 by
including 'MARS'

but then you STILL can't open two things on the same connection; like
you used to be able to in ADO Classic

..NET is crap l choose to go back to ADO classic
 
I disagree with your 'better'

it is 'more verbose' yes you are correct

better-- NO

not having to rewrite everything would be _BETTER_
 
Back
Top