SQL Server connection pooling

[fniles]

I am using VB.NET 2003 and SQL 2005.
To use connection pooling and avoid the error "There is already an open
DataReader associated with this Connection which must be closed first." , I
understand that I want to release/close connections in a timely fashion.
What I do is I declare the SqlClient.SqlConnection variable as a local
variable inside my subroutine (instead of a global variable). Right before I
Fill the dataset I open the connection, and right after I fill the dataset I
close the connection, like shown below. Do I do it correctly ? Thank you.

sub MySub
Dim adoCon As SqlClient.SqlConnection
Dim cmdSQL As SqlClient.SqlCommand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDataAdapter

adoCon = New SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCommand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDataAdapter
dsSQL = New DataSet
daSQL.SelectCommand = m_cmdSQL
daSQL.Fill(m_dsSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Close() '--close connection
adoCon = Nothing
End If

[Carlos Camargo]

I get the same message a couple of weeks ago.

You can use the MultipleActiveResultSets on your connection string so you
don't have to close a DataReader (or whatever) first to be able to go for
the next one.

see this link with information about it:

http://msdn2.microsoft.com/en-us/lib...esultsets.aspx

Hope it helps.

Best regards,

Carlos Camargo

"fniles" <fn****@pfmail.comescribió en el mensaje
news:uy*************@TK2MSFTNGP05.phx.gbl...

>I am using VB.NET 2003 and SQL 2005.
To use connection pooling and avoid the error "There is already an open
DataReader associated with this Connection which must be closed first." ,
I understand that I want to release/close connections in a timely fashion.
What I do is I declare the SqlClient.SqlConnection variable as a local
variable inside my subroutine (instead of a global variable). Right before
I Fill the dataset I open the connection, and right after I fill the
dataset I close the connection, like shown below. Do I do it correctly ?
Thank you.

sub MySub
Dim adoCon As SqlClient.SqlConnection
Dim cmdSQL As SqlClient.SqlCommand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDataAdapter

adoCon = New SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCommand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDataAdapter
dsSQL = New DataSet
daSQL.SelectCommand = m_cmdSQL
daSQL.Fill(m_dsSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Close() '--close connection
adoCon = Nothing
End If

[fniles]

Thank you.
Does it mean that if I set MultipleActiveResultSets to true, I can have more
than 1 DataReader open in the connection ?

Regarding using a local SQLConnection variable (instead of using a global
one) and set it to new, open and close the connection everytime I fill a
dataset, is it correct ?
Or, do I need to use a global SQLConnection variable
1. new and open it on form_load (and do not close this one until
form_Closed)
2. and in each subroutine use this global SQLConnection variable but new,
open and close the connection in this subroutine ?

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:um**************@TK2MSFTNGP06.phx.gbl...

>I get the same message a couple of weeks ago.

You can use the MultipleActiveResultSets on your connection string so you
don't have to close a DataReader (or whatever) first to be able to go for
the next one.

see this link with information about it:
http://msdn2.microsoft.com/en-us/lib...esultsets.aspx

Hope it helps.

Best regards,

Carlos Camargo

"fniles" <fn****@pfmail.comescribió en el mensaje
news:uy*************@TK2MSFTNGP05.phx.gbl...

>>I am using VB.NET 2003 and SQL 2005.
To use connection pooling and avoid the error "There is already an open
DataReader associated with this Connection which must be closed first." ,
I understand that I want to release/close connections in a timely
fashion. What I do is I declare the SqlClient.SqlConnection variable as a
local variable inside my subroutine (instead of a global variable). Right
before I Fill the dataset I open the connection, and right after I fill
the dataset I close the connection, like shown below. Do I do it
correctly ? Thank you.

sub MySub
Dim adoCon As SqlClient.SqlConnection
Dim cmdSQL As SqlClient.SqlCommand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDataAdapter

adoCon = New SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCommand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDataAdapter
dsSQL = New DataSet
daSQL.SelectCommand = m_cmdSQL
daSQL.Fill(m_dsSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Close() '--close connection
adoCon = Nothing
End If

[fniles]

Also, when I set my connection string to the following
Data Source=mySQL2005;Initial Catalog=myDB;User
ID=myUserID;Password=myPass;MultipleActiveResultSe ts=True
I got the error "Keyword not supported: 'multipleactiveresultsets'.

Dim adoCon As SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path -->>error here
.Open()
End With

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:um**************@TK2MSFTNGP06.phx.gbl...

>I get the same message a couple of weeks ago.

You can use the MultipleActiveResultSets on your connection string so you
don't have to close a DataReader (or whatever) first to be able to go for
the next one.

see this link with information about it:
http://msdn2.microsoft.com/en-us/lib...esultsets.aspx

Hope it helps.

Best regards,

Carlos Camargo

"fniles" <fn****@pfmail.comescribió en el mensaje
news:uy*************@TK2MSFTNGP05.phx.gbl...

>>I am using VB.NET 2003 and SQL 2005.
To use connection pooling and avoid the error "There is already an open
DataReader associated with this Connection which must be closed first." ,
I understand that I want to release/close connections in a timely
fashion. What I do is I declare the SqlClient.SqlConnection variable as a
local variable inside my subroutine (instead of a global variable). Right
before I Fill the dataset I open the connection, and right after I fill
the dataset I close the connection, like shown below. Do I do it
correctly ? Thank you.

sub MySub
Dim adoCon As SqlClient.SqlConnection
Dim cmdSQL As SqlClient.SqlCommand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDataAdapter

adoCon = New SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCommand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDataAdapter
dsSQL = New DataSet
daSQL.SelectCommand = m_cmdSQL
daSQL.Fill(m_dsSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Close() '--close connection
adoCon = Nothing
End If

[Carlos Camargo]

Regarding MultipleActiveResultSets, that's right, set it to True and your
may be able to have more than a DataReader open at the same time, just
remember to close them anyway so the system behaves nicely with resources
like memory usage, processor, network and so on.

Regarding the issue of using a local or global connection, it will depend on
your application, but if the application is moderate or intensive in its use
of database, then I will recomend to use a global connection.

You may want to take a look at the Database Application Block from the
Microsoft Enterprise Library 3.0 (it's free -$0.00- !!!). It makes things a
little bit easier to access the database and you don't have to control the
connection state yourself (it handles the connection state, a connection
pool, and so on):

http://msdn2.microsoft.com/en-us/library/aa480453.aspx

Hope this helps,

Carlos Camargo
"fniles" <fn****@pfmail.comescribió en el mensaje
news:uF****************@TK2MSFTNGP06.phx.gbl...

Thank you.
Does it mean that if I set MultipleActiveResultSets to true, I can have
more than 1 DataReader open in the connection ?

Regarding using a local SQLConnection variable (instead of using a global
one) and set it to new, open and close the connection everytime I fill a
dataset, is it correct ?
Or, do I need to use a global SQLConnection variable
1. new and open it on form_load (and do not close this one until
form_Closed)
2. and in each subroutine use this global SQLConnection variable but new,
open and close the connection in this subroutine ?

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:um**************@TK2MSFTNGP06.phx.gbl...

>>I get the same message a couple of weeks ago.

You can use the MultipleActiveResultSets on your connection string so you
don't have to close a DataReader (or whatever) first to be able to go for
the next one.

see this link with information about it:
http://msdn2.microsoft.com/en-us/lib...esultsets.aspx

Hope it helps.

Best regards,

Carlos Camargo

"fniles" <fn****@pfmail.comescribió en el mensaje
news:uy*************@TK2MSFTNGP05.phx.gbl...

>>>I am using VB.NET 2003 and SQL 2005.
To use connection pooling and avoid the error "There is already an open
DataReader associated with this Connection which must be closed first."
, I understand that I want to release/close connections in a timely
fashion. What I do is I declare the SqlClient.SqlConnection variable as
a local variable inside my subroutine (instead of a global variable).
Right before I Fill the dataset I open the connection, and right after I
fill the dataset I close the connection, like shown below. Do I do it
correctly ? Thank you.

sub MySub
Dim adoCon As SqlClient.SqlConnection
Dim cmdSQL As SqlClient.SqlCommand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDataAdapter

adoCon = New SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCommand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDataAdapter
dsSQL = New DataSet
daSQL.SelectCommand = m_cmdSQL
daSQL.Fill(m_dsSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Close() '--close connection
adoCon = Nothing
End If

[fniles]

Thank you.
If I use a global variable, does it mean that
1. I set it to new and open it on main form_load (and do not close this one
until form_Closed)
2. and in each subroutine use this global SQLConnection variable but set it
to new, open and close this connection in this subroutine ?
or,
simply in each subroutine use this global SQLConnection variable but set it
to new, open and close this connection in this subroutine, and do not need
to open it in the main form_load ?

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

Regarding MultipleActiveResultSets, that's right, set it to True and your
may be able to have more than a DataReader open at the same time, just
remember to close them anyway so the system behaves nicely with resources
like memory usage, processor, network and so on.

Regarding the issue of using a local or global connection, it will depend
on your application, but if the application is moderate or intensive in
its use of database, then I will recomend to use a global connection.

You may want to take a look at the Database Application Block from the
Microsoft Enterprise Library 3.0 (it's free -$0.00- !!!). It makes things
a little bit easier to access the database and you don't have to control
the connection state yourself (it handles the connection state, a
connection pool, and so on):

http://msdn2.microsoft.com/en-us/library/aa480453.aspx

Hope this helps,

Carlos Camargo
"fniles" <fn****@pfmail.comescribió en el mensaje
news:uF****************@TK2MSFTNGP06.phx.gbl...

>Thank you.
Does it mean that if I set MultipleActiveResultSets to true, I can have
more than 1 DataReader open in the connection ?

Regarding using a local SQLConnection variable (instead of using a global
one) and set it to new, open and close the connection everytime I fill a
dataset, is it correct ?
Or, do I need to use a global SQLConnection variable
1. new and open it on form_load (and do not close this one until
form_Closed)
2. and in each subroutine use this global SQLConnection variable but new,
open and close the connection in this subroutine ?

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:um**************@TK2MSFTNGP06.phx.gbl...

>>>I get the same message a couple of weeks ago.

You can use the MultipleActiveResultSets on your connection string so
you don't have to close a DataReader (or whatever) first to be able to
go for the next one.

see this link with information about it:
http://msdn2.microsoft.com/en-us/lib...esultsets.aspx

Hope it helps.

Best regards,

Carlos Camargo

"fniles" <fn****@pfmail.comescribió en el mensaje
news:uy*************@TK2MSFTNGP05.phx.gbl...
I am using VB.NET 2003 and SQL 2005.
To use connection pooling and avoid the error "There is already an open
DataReader associated with this Connection which must be closed first."
, I understand that I want to release/close connections in a timely
fashion. What I do is I declare the SqlClient.SqlConnection variable as
a local variable inside my subroutine (instead of a global variable).
Right before I Fill the dataset I open the connection, and right after
I fill the dataset I close the connection, like shown below. Do I do it
correctly ? Thank you.

sub MySub
Dim adoCon As SqlClient.SqlConnection
Dim cmdSQL As SqlClient.SqlCommand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDataAdapter

adoCon = New SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCommand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDataAdapter
dsSQL = New DataSet
daSQL.SelectCommand = m_cmdSQL
daSQL.Fill(m_dsSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Close() '--close connection
adoCon = Nothing
End If

[=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=]

fniles,

MARS is a .Net 2 feature. You would need to use VS 2005 to have access to it.

I will also mention that using MARS is more involved than just turning it on
in the connection string. MARS has all sorts of ramifications that you need
to understand really well before using it.

Kerry Moorman
"fniles" wrote:

Also, when I set my connection string to the following
Data Source=mySQL2005;Initial Catalog=myDB;User
ID=myUserID;Password=myPass;MultipleActiveResultSe ts=True
I got the error "Keyword not supported: 'multipleactiveresultsets'.

Dim adoCon As SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path -->>error here
.Open()
End With

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:um**************@TK2MSFTNGP06.phx.gbl...

I get the same message a couple of weeks ago.

You can use the MultipleActiveResultSets on your connection string so you
don't have to close a DataReader (or whatever) first to be able to go for
the next one.

see this link with information about it:
http://msdn2.microsoft.com/en-us/lib...esultsets.aspx

Hope it helps.

Best regards,

Carlos Camargo

"fniles" <fn****@pfmail.comescribió en el mensaje
news:uy*************@TK2MSFTNGP05.phx.gbl...

>I am using VB.NET 2003 and SQL 2005.
To use connection pooling and avoid the error "There is already an open
DataReader associated with this Connection which must be closed first." ,
I understand that I want to release/close connections in a timely
fashion. What I do is I declare the SqlClient.SqlConnection variable as a
local variable inside my subroutine (instead of a global variable). Right
before I Fill the dataset I open the connection, and right after I fill
the dataset I close the connection, like shown below. Do I do it
correctly ? Thank you.

sub MySub
Dim adoCon As SqlClient.SqlConnection
Dim cmdSQL As SqlClient.SqlCommand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDataAdapter

adoCon = New SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCommand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDataAdapter
dsSQL = New DataSet
daSQL.SelectCommand = m_cmdSQL
daSQL.Fill(m_dsSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Close() '--close connection
adoCon = Nothing
End If

[Carlos Camargo]

Open the connection in your application's entry point, for example in the
Load event for the main form, and store the resulting connection object in a
global variable.

Close it when that same form gets closed.

In individual procedures where you go for some info from the database or
update data there, just send the querys and get the results, don't open the
connection and close it again, because doing that (open and close
connections all the time) will make your application somehow slow because
opening the connection involves a relatively-heavy process for both, the
server computer and the client computer. Once a connection gets stablished
things will go faster.

Best regards,

Carlos Camargo

"fniles" <fn****@pfmail.comescribió en el mensaje
news:ew**************@TK2MSFTNGP03.phx.gbl...

Thank you.
If I use a global variable, does it mean that
1. I set it to new and open it on main form_load (and do not close this
one until form_Closed)
2. and in each subroutine use this global SQLConnection variable but set
it to new, open and close this connection in this subroutine ?
or,
simply in each subroutine use this global SQLConnection variable but set
it to new, open and close this connection in this subroutine, and do not
need to open it in the main form_load ?

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

>Regarding MultipleActiveResultSets, that's right, set it to True and your
may be able to have more than a DataReader open at the same time, just
remember to close them anyway so the system behaves nicely with resources
like memory usage, processor, network and so on.

Regarding the issue of using a local or global connection, it will depend
on your application, but if the application is moderate or intensive in
its use of database, then I will recomend to use a global connection.

You may want to take a look at the Database Application Block from the
Microsoft Enterprise Library 3.0 (it's free -$0.00- !!!). It makes things
a little bit easier to access the database and you don't have to control
the connection state yourself (it handles the connection state, a
connection pool, and so on):

http://msdn2.microsoft.com/en-us/library/aa480453.aspx

Hope this helps,

Carlos Camargo
"fniles" <fn****@pfmail.comescribió en el mensaje
news:uF****************@TK2MSFTNGP06.phx.gbl...

>>Thank you.
Does it mean that if I set MultipleActiveResultSets to true, I can have
more than 1 DataReader open in the connection ?

Regarding using a local SQLConnection variable (instead of using a
global one) and set it to new, open and close the connection everytime I
fill a dataset, is it correct ?
Or, do I need to use a global SQLConnection variable
1. new and open it on form_load (and do not close this one until
form_Closed)
2. and in each subroutine use this global SQLConnection variable but
new, open and close the connection in this subroutine ?

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:um**************@TK2MSFTNGP06.phx.gbl...
I get the same message a couple of weeks ago.

You can use the MultipleActiveResultSets on your connection string so
you don't have to close a DataReader (or whatever) first to be able to
go for the next one.

see this link with information about it:
http://msdn2.microsoft.com/en-us/lib...esultsets.aspx

Hope it helps.

Best regards,

Carlos Camargo

"fniles" <fn****@pfmail.comescribió en el mensaje
news:uy*************@TK2MSFTNGP05.phx.gbl...
>I am using VB.NET 2003 and SQL 2005.
To use connection pooling and avoid the error "There is already an
open DataReader associated with this Connection which must be closed
first." , I understand that I want to release/close connections in a
timely fashion. What I do is I declare the SqlClient.SqlConnection
variable as a local variable inside my subroutine (instead of a global
variable). Right before I Fill the dataset I open the connection, and
right after I fill the dataset I close the connection, like shown
below. Do I do it correctly ? Thank you.
>
sub MySub
Dim adoCon As SqlClient.SqlConnection
Dim cmdSQL As SqlClient.SqlCommand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDataAdapter
>
adoCon = New SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCommand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDataAdapter
dsSQL = New DataSet
daSQL.SelectCommand = m_cmdSQL
daSQL.Fill(m_dsSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Close() '--close connection
adoCon = Nothing
End If
>
>
>

[fniles]

I am using VB.NET 2003, so MultipleActiveResultSets is not available (it's
available in 2005 according to another user here).
In the individual procedures if I do not open and close the connection, I
will get the error "There is already an open DataReader associated with this
Connection which must be closed first."
You mentioned that if I open and close the connection the application will
be slow, but I thought with connection pooling this won't be slow, am I
wrong ?

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:Oc**************@TK2MSFTNGP05.phx.gbl...

Open the connection in your application's entry point, for example in the
Load event for the main form, and store the resulting connection object in
a global variable.

Close it when that same form gets closed.

In individual procedures where you go for some info from the database or
update data there, just send the querys and get the results, don't open
the connection and close it again, because doing that (open and close
connections all the time) will make your application somehow slow because
opening the connection involves a relatively-heavy process for both, the
server computer and the client computer. Once a connection gets stablished
things will go faster.

Best regards,

Carlos Camargo

"fniles" <fn****@pfmail.comescribió en el mensaje
news:ew**************@TK2MSFTNGP03.phx.gbl...

>Thank you.
If I use a global variable, does it mean that
1. I set it to new and open it on main form_load (and do not close this
one until form_Closed)
2. and in each subroutine use this global SQLConnection variable but set
it to new, open and close this connection in this subroutine ?
or,
simply in each subroutine use this global SQLConnection variable but set
it to new, open and close this connection in this subroutine, and do not
need to open it in the main form_load ?

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

>>Regarding MultipleActiveResultSets, that's right, set it to True and
your may be able to have more than a DataReader open at the same time,
just remember to close them anyway so the system behaves nicely with
resources like memory usage, processor, network and so on.

Regarding the issue of using a local or global connection, it will
depend on your application, but if the application is moderate or
intensive in its use of database, then I will recomend to use a global
connection.

You may want to take a look at the Database Application Block from the
Microsoft Enterprise Library 3.0 (it's free -$0.00- !!!). It makes
things a little bit easier to access the database and you don't have to
control the connection state yourself (it handles the connection state,
a connection pool, and so on):

http://msdn2.microsoft.com/en-us/library/aa480453.aspx

Hope this helps,

Carlos Camargo
"fniles" <fn****@pfmail.comescribió en el mensaje
news:uF****************@TK2MSFTNGP06.phx.gbl.. .
Thank you.
Does it mean that if I set MultipleActiveResultSets to true, I can have
more than 1 DataReader open in the connection ?

Regarding using a local SQLConnection variable (instead of using a
global one) and set it to new, open and close the connection everytime
I fill a dataset, is it correct ?
Or, do I need to use a global SQLConnection variable
1. new and open it on form_load (and do not close this one until
form_Closed)
2. and in each subroutine use this global SQLConnection variable but
new, open and close the connection in this subroutine ?

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:um**************@TK2MSFTNGP06.phx.gbl...
>I get the same message a couple of weeks ago.
>
You can use the MultipleActiveResultSets on your connection string so
you don't have to close a DataReader (or whatever) first to be able to
go for the next one.
>
see this link with information about it:
>
>
http://msdn2.microsoft.com/en-us/lib...esultsets.aspx
>
Hope it helps.
>
Best regards,
>
Carlos Camargo
>
"fniles" <fn****@pfmail.comescribió en el mensaje
news:uy*************@TK2MSFTNGP05.phx.gbl...
>>I am using VB.NET 2003 and SQL 2005.
>To use connection pooling and avoid the error "There is already an
>open DataReader associated with this Connection which must be closed
>first." , I understand that I want to release/close connections in a
>timely fashion. What I do is I declare the SqlClient.SqlConnection
>variable as a local variable inside my subroutine (instead of a
>global variable). Right before I Fill the dataset I open the
>connection, and right after I fill the dataset I close the
>connection, like shown below. Do I do it correctly ? Thank you.
>>
>sub MySub
>Dim adoCon As SqlClient.SqlConnection
>Dim cmdSQL As SqlClient.SqlCommand
>Dim dsSQL As DataSet
>Dim daSQL As SqlClient.SqlDataAdapter
>>
>adoCon = New SqlClient.SqlConnection
>With adoCon
> .ConnectionString = DB_Path
> .Open() '--open connection
>End With
>cmdSQL = New SqlClient.SqlCommand
>With cmdSQL
> .Connection = adoCon
> .CommandText = sSQL
>End With
>daSQL = New SqlClient.SqlDataAdapter
>dsSQL = New DataSet
>daSQL.SelectCommand = m_cmdSQL
>daSQL.Fill(m_dsSQL) '--fill dataset
>If Not adoCon Is Nothing Then
>adoCon.Close() '--close connection
>adoCon = Nothing
>End If
>>
>>
>>
>
>

[William \(Bill\) Vaughn]

Thanks Kerry,
For awhile there I thought that there was no rational people lurking on
this list.
I completely agree. Use MARS at your own risk. It is fraught with issues and
unintended side-effects. While it might mask the issue of an unclosed
operation on a connection, it simply delays the inevitable.
The ultimate solution to the problem is understanding how the
instantiation and tear-down of Connections works. You simply must close
connections when they are in scope--you can't depend on the finalizer or
garbage collector to do so.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Kerry Moorman" <Ke**********@discussions.microsoft.comwrote in message
news:A5**********************************@microsof t.com...

fniles,

MARS is a .Net 2 feature. You would need to use VS 2005 to have access to
it.

I will also mention that using MARS is more involved than just turning it
on
in the connection string. MARS has all sorts of ramifications that you
need
to understand really well before using it.

Kerry Moorman
"fniles" wrote:

>Also, when I set my connection string to the following
Data Source=mySQL2005;Initial Catalog=myDB;User
ID=myUserID;Password=myPass;MultipleActiveResultS ets=True
I got the error "Keyword not supported: 'multipleactiveresultsets'.

Dim adoCon As SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path -->>error here
.Open()
End With

"Carlos Camargo" <cc******@INVALID.yahoo.comwrote in message
news:um**************@TK2MSFTNGP06.phx.gbl...

>I get the same message a couple of weeks ago.

You can use the MultipleActiveResultSets on your connection string so
you
don't have to close a DataReader (or whatever) first to be able to go
for
the next one.

see this link with information about it:
http://msdn2.microsoft.com/en-us/lib...esultsets.aspx

Hope it helps.

Best regards,

Carlos Camargo

"fniles" <fn****@pfmail.comescribió en el mensaje
news:uy*************@TK2MSFTNGP05.phx.gbl...
I am using VB.NET 2003 and SQL 2005.
To use connection pooling and avoid the error "There is already an
open
DataReader associated with this Connection which must be closed
first." ,
I understand that I want to release/close connections in a timely
fashion. What I do is I declare the SqlClient.SqlConnection variable
as a
local variable inside my subroutine (instead of a global variable).
Right
before I Fill the dataset I open the connection, and right after I
fill
the dataset I close the connection, like shown below. Do I do it
correctly ? Thank you.

sub MySub
Dim adoCon As SqlClient.SqlConnection
Dim cmdSQL As SqlClient.SqlCommand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDataAdapter

adoCon = New SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCommand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDataAdapter
dsSQL = New DataSet
daSQL.SelectCommand = m_cmdSQL
daSQL.Fill(m_dsSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Close() '--close connection
adoCon = Nothing
End If