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

[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

 

[Chris Dunaway]

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 ?

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

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

 

[fniles]

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

 

[rowe_newsgroups]

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

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

 

[fniles]

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

 

[rowe_newsgroups]

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

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"

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

 

[dbahooker]

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

 

[dbahooker]

I disagree with your 'better'

it is 'more verbose' yes you are correct

better-- NO

not having to rewrite everything would be _BETTER_