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.SqlCo nnection 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.SqlCo nnection
Dim cmdSQL As SqlClient.SqlCo mmand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDa taAdapter

adoCon = New SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCo mmand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDa taAdapter
dsSQL = New DataSet
daSQL.SelectCom mand = m_cmdSQL
daSQL.Fill(m_ds SQL) '--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 MultipleActiveR esultSets 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******** *****@TK2MSFTNG P05.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.SqlCo nnection 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.SqlCo nnection
Dim cmdSQL As SqlClient.SqlCo mmand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDa taAdapter

adoCon = New SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCo mmand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDa taAdapter
dsSQL = New DataSet
daSQL.SelectCom mand = m_cmdSQL
daSQL.Fill(m_ds SQL) '--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 MultipleActiveR esultSets 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******@INVAL ID.yahoo.comwro te in message
news:um******** ******@TK2MSFTN GP06.phx.gbl...

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

You can use the MultipleActiveR esultSets 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******** *****@TK2MSFTNG P05.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.SqlCo nnection 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.SqlCo nnection
Dim cmdSQL As SqlClient.SqlCo mmand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDa taAdapter

adoCon = New SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCo mmand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDa taAdapter
dsSQL = New DataSet
daSQL.SelectCo mmand = m_cmdSQL
daSQL.Fill(m_d sSQL) '--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=mySQL200 5;Initial Catalog=myDB;Us er
ID=myUserID;Pas sword=myPass;Mu ltipleActiveRes ultSets=True
I got the error "Keyword not supported: 'multipleactive resultsets'.

Dim adoCon As SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path -->>error here
.Open()
End With

"Carlos Camargo" <cc******@INVAL ID.yahoo.comwro te in message
news:um******** ******@TK2MSFTN GP06.phx.gbl...

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

You can use the MultipleActiveR esultSets 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******** *****@TK2MSFTNG P05.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.SqlCo nnection 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.SqlCo nnection
Dim cmdSQL As SqlClient.SqlCo mmand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDa taAdapter

adoCon = New SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCo mmand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDa taAdapter
dsSQL = New DataSet
daSQL.SelectCo mmand = m_cmdSQL
daSQL.Fill(m_d sSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Close () '--close connection
adoCon = Nothing
End If

[Carlos Camargo]

Regarding MultipleActiveR esultSets, 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******** ********@TK2MSF TNGP06.phx.gbl. ..

Thank you.
Does it mean that if I set MultipleActiveR esultSets 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******@INVAL ID.yahoo.comwro te in message
news:um******** ******@TK2MSFTN GP06.phx.gbl...

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

You can use the MultipleActiveR esultSets 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******* ******@TK2MSFTN GP05.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.SqlCo nnection 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.SqlCo nnection
Dim cmdSQL As SqlClient.SqlCo mmand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDa taAdapter

adoCon = New SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCo mmand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDa taAdapter
dsSQL = New DataSet
daSQL.SelectC ommand = 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******@INVAL ID.yahoo.comwro te in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..

Regarding MultipleActiveR esultSets, 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******** ********@TK2MSF TNGP06.phx.gbl. ..

>Thank you.
Does it mean that if I set MultipleActiveR esultSets 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******@INVAL ID.yahoo.comwro te in message
news:um******* *******@TK2MSFT NGP06.phx.gbl.. .

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

You can use the MultipleActiveR esultSets 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****** *******@TK2MSFT NGP05.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.SqlCo nnection 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.SqlCo nnection
Dim cmdSQL As SqlClient.SqlCo mmand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDa taAdapter

adoCon = New SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCo mmand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDa taAdapter
dsSQL = New DataSet
daSQL.Select Command = 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=mySQL200 5;Initial Catalog=myDB;Us er
ID=myUserID;Pas sword=myPass;Mu ltipleActiveRes ultSets=True
I got the error "Keyword not supported: 'multipleactive resultsets'.

Dim adoCon As SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path -->>error here
.Open()
End With

"Carlos Camargo" <cc******@INVAL ID.yahoo.comwro te in message
news:um******** ******@TK2MSFTN GP06.phx.gbl...

I get the same message a couple of weeks ago.

You can use the MultipleActiveR esultSets 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******** *****@TK2MSFTNG P05.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.SqlCo nnection 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.SqlCo nnection
Dim cmdSQL As SqlClient.SqlCo mmand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDa taAdapter

adoCon = New SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCo mmand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDa taAdapter
dsSQL = New DataSet
daSQL.SelectCom mand = m_cmdSQL
daSQL.Fill(m_ds SQL) '--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******** ******@TK2MSFTN GP03.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******@INVAL ID.yahoo.comwro te in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..

>Regarding MultipleActiveR esultSets, 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******* *********@TK2MS FTNGP06.phx.gbl ...

>>Thank you.
Does it mean that if I set MultipleActiveR esultSets 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******@INVAL ID.yahoo.comwro te in message
news:um****** ********@TK2MSF TNGP06.phx.gbl. ..
I get the same message a couple of weeks ago.

You can use the MultipleActiveR esultSets 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***** ********@TK2MSF TNGP05.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.SqlCo nnection
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.SqlCo nnection
Dim cmdSQL As SqlClient.SqlCo mmand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDa taAdapter
>
adoCon = New SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCo mmand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDa taAdapter
dsSQL = New DataSet
daSQL.Selec tCommand = m_cmdSQL
daSQL.Fill( m_dsSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Clos e() '--close connection
adoCon = Nothing
End If
>
>
>

[fniles]

I am using VB.NET 2003, so MultipleActiveR esultSets 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******@INVAL ID.yahoo.comwro te in message
news:Oc******** ******@TK2MSFTN GP05.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******** ******@TK2MSFTN GP03.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******@INVAL ID.yahoo.comwro te in message
news:%2******* *********@TK2MS FTNGP03.phx.gbl ...

>>Regarding MultipleActiveR esultSets, 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****** **********@TK2M SFTNGP06.phx.gb l...
Thank you.
Does it mean that if I set MultipleActiveR esultSets 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_Close d)
2. and in each subroutine use this global SQLConnection variable but
new, open and close the connection in this subroutine ?

"Carlos Camargo" <cc******@INVAL ID.yahoo.comwro te in message
news:um***** *********@TK2MS FTNGP06.phx.gbl ...
>I get the same message a couple of weeks ago.
>
You can use the MultipleActiveR esultSets 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**** *********@TK2MS FTNGP05.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.SqlCo nnection
>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.SqlCo nnection
>Dim cmdSQL As SqlClient.SqlCo mmand
>Dim dsSQL As DataSet
>Dim daSQL As SqlClient.SqlDa taAdapter
>>
>adoCon = New SqlClient.SqlCo nnection
>With adoCon
> .ConnectionStri ng = DB_Path
> .Open() '--open connection
>End With
>cmdSQL = New SqlClient.SqlCo mmand
>With cmdSQL
> .Connection = adoCon
> .CommandText = sSQL
>End With
>daSQL = New SqlClient.SqlDa taAdapter
>dsSQL = New DataSet
>daSQL.Sele ctCommand = m_cmdSQL
>daSQL.Fill (m_dsSQL) '--fill dataset
>If Not adoCon Is Nothing Then
>adoCon.Clo se() '--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**********@d iscussions.micr osoft.comwrote in message
news:A5******** *************** ***********@mic rosoft.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=mySQL200 5;Initial Catalog=myDB;Us er
ID=myUserID;Pa ssword=myPass;M ultipleActiveRe sultSets=True
I got the error "Keyword not supported: 'multipleactive resultsets'.

Dim adoCon As SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path -->>error here
.Open()
End With

"Carlos Camargo" <cc******@INVAL ID.yahoo.comwro te in message
news:um******* *******@TK2MSFT NGP06.phx.gbl.. .

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

You can use the MultipleActiveR esultSets 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******** *****@TK2MSFTNG P05.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.SqlCo nnection 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.SqlCo nnection
Dim cmdSQL As SqlClient.SqlCo mmand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDa taAdapter

adoCon = New SqlClient.SqlCo nnection
With adoCon
.ConnectionStri ng = DB_Path
.Open() '--open connection
End With
cmdSQL = New SqlClient.SqlCo mmand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDa taAdapter
dsSQL = New DataSet
daSQL.SelectCo mmand = m_cmdSQL
daSQL.Fill(m_d sSQL) '--fill dataset
If Not adoCon Is Nothing Then
adoCon.Close () '--close connection
adoCon = Nothing
End If