Threading and Database connection problems

[Jerry Spence1]

I have multiple threads in my application, each which open a connection to a
database as follows:

Sub ReaderThread()

Dim cmd As New OleDbCommand
Dim dr As OleDbDataReader
cmd.Connection = cn
cmd.CommandText = "SELECT * from Mytable"
dr = cmd.ExecuteReader

While dr.Read

'Do something

End While

cmd.dispose()
dr.close

do
'Do lots of things
loop

end sub
I am getting an error saying that "There is already a connection open" which
suggests that a connection on one thread is fighting with a connection on
the other.

Since I am declaring dr and cmd locally I can't work out why I am getting
this error.

-Jerry

[Cor Ligthert]

Jerry,

In Net 1.x you can only use one connection in a time.

So when you want to use it as you do, you should create everytime a new
connection.

What is better anyway to get a more efficient connection.pooling (and than
use dispose in this case to close it everytime when your datareader process
is ready)

I hope this helps?

Cor

"Jerry Spence1" <je**********@somewhere.com>

I have multiple threads in my application, each which open a connection to
a
database as follows:

Sub ReaderThread()

Dim cmd As New OleDbCommand
Dim dr As OleDbDataReader
cmd.Connection = cn
cmd.CommandText = "SELECT * from Mytable"
dr = cmd.ExecuteReader

While dr.Read

'Do something

End While

cmd.dispose()
dr.close

do
'Do lots of things
loop

end sub
I am getting an error saying that "There is already a connection open"
which
suggests that a connection on one thread is fighting with a connection on
the other.

Since I am declaring dr and cmd locally I can't work out why I am getting
this error.

-Jerry

[Herfried K. Wagner [MVP]]

"Jerry Spence1" <je**********@somewhere.com> schrieb:

I have multiple threads in my application, each which open a connection to
a
database as follows:
[...]
I am getting an error saying that "There is already a connection open"
which
suggests that a connection on one thread is fighting with a connection on
the other.

<URL:http://www.google.de/groups?threadm=%23xyXF%24xwDHA.2712%40tk2msftngp13 .phx.gbl>

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

[Cor Ligthert]

Herfried,

When you want to show a thread about this than it is in my opinion better to
use a thread with Angel Saenz-Badillos[MS] in it.

Cor

[Jerry Spence1]

Thank you all very much. Yes - I had the connection itself as a Public
variable. I have redefined it in each thread and it works fine.

Cheers

-Jerry

"Cor Ligthert" <no************@planet.nl> wrote in message
news:ec**************@TK2MSFTNGP14.phx.gbl...

Herfried,

When you want to show a thread about this than it is in my opinion better to use a thread with Angel Saenz-Badillos[MS] in it.

Cor