«

Please can someone help.

I have a routine as follows

Public Function dbConnection() As SqlConnection

Dim dbConn As New SqlConnection
Dim errorH As New errorHandle

Try
Try
dbConn.ConnectionString =
ConfigurationManager.ConnectionStrings(globalapp.c urrentSite).ConnectionString.ToString
Catch ex As Exception
errorH.move(ex)
End Try
If dbConn.State = ConnectionState.Closed Then
dbConn.Open()
End If
Catch ex As SqlException
errorH.move(ex)
End Try

Return dbConn


End Function

This creates my sqlconnection. I call this from other functions in my
class in this format

Try
dim sqlconn as sqlconnection = dbconnection()

Catch ex as exception

Finally
If db.State = ConnectionState.Open Then
db.Close()
dbConnection.Dispose()
End If
End try

I recently only added the dbconnection.dispose because i read that i
should be doing that not sure if i should. But i'm getting the usual
error message of the maximum timeout has been reached or the maximum
pool size has been reached. I don't know what to do. Can anyone advise.

I don't know what you are closing, but it is not the connection your declare
in your Try block.

You need to declare the variable outside the Try block, but get the
connection in it. Something like:

Dim sqlconn as SqlConnection
Try
sqlconn = dbconnection()
Catch

Finally
If Not IsNothing(sqlconn) Then
sqlconn.Close()
End If
End Try
"bannaman" <> wrote in message
news: oups.com...
> Please can someone help.
>
> I have a routine as follows
>
> Public Function dbConnection() As SqlConnection
>
> Dim dbConn As New SqlConnection
> Dim errorH As New errorHandle
>
> Try
> Try
> dbConn.ConnectionString =
> ConfigurationManager.ConnectionStrings(globalapp.c urrentSite).ConnectionString.ToString
> Catch ex As Exception
> errorH.move(ex)
> End Try
> If dbConn.State = ConnectionState.Closed Then
> dbConn.Open()
> End If
> Catch ex As SqlException
> errorH.move(ex)
> End Try
>
> Return dbConn
>
>
> End Function
>
> This creates my sqlconnection. I call this from other functions in my
> class in this format
>
> Try
> dim sqlconn as sqlconnection = dbconnection()
>
> Catch ex as exception
>
> Finally
> If db.State = ConnectionState.Open Then
> db.Close()
> dbConnection.Dispose()
> End If
> End try
>
> I recently only added the dbconnection.dispose because i read that i
> should be doing that not sure if i should. But i'm getting the usual
> error message of the maximum timeout has been reached or the maximum
> pool size has been reached. I don't know what to do. Can anyone advise.
>

if you call close you do not need to call dispose.

-- bruce (sqlwork.com)


"bannaman" <> wrote in message
news: oups.com...
> Please can someone help.
>
> I have a routine as follows
>
> Public Function dbConnection() As SqlConnection
>
> Dim dbConn As New SqlConnection
> Dim errorH As New errorHandle
>
> Try
> Try
> dbConn.ConnectionString =
> ConfigurationManager.ConnectionStrings(globalapp.c urrentSite).ConnectionString.ToString
> Catch ex As Exception
> errorH.move(ex)
> End Try
> If dbConn.State = ConnectionState.Closed Then
> dbConn.Open()
> End If
> Catch ex As SqlException
> errorH.move(ex)
> End Try
>
> Return dbConn
>
>
> End Function
>
> This creates my sqlconnection. I call this from other functions in my
> class in this format
>
> Try
> dim sqlconn as sqlconnection = dbconnection()
>
> Catch ex as exception
>
> Finally
> If db.State = ConnectionState.Open Then
> db.Close()
> dbConnection.Dispose()
> End If
> End try
>
> I recently only added the dbconnection.dispose because i read that i
> should be doing that not sure if i should. But i'm getting the usual
> error message of the maximum timeout has been reached or the maximum
> pool size has been reached. I don't know what to do. Can anyone advise.
>

Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
should have been dim db as sqlconnection = dbconnection().

As i am creating a connection in another function and passing it back
to my routine does it still close the conneciton that was opened when
calling dbconnection. do i not need to do dbconnection.close or will
db.close be enough?

As Bruce said, close it. Don't dispose it.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.

"bannaman" <> wrote in message
news: ups.com...
> Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
> should have been dim db as sqlconnection = dbconnection().
>
> As i am creating a connection in another function and passing it back
> to my routine does it still close the conneciton that was opened when
> calling dbconnection. do i not need to do dbconnection.close or will
> db.close be enough?
>

First, I would dim your sqlconn outside the Try block like a poster
suggested.

Second, when you say db = dbconnection() you are not actually creating
a new connection object you are just getting a reference to the
conneciton object that was created inside dbconnection(). So, when you
say, db.close() you are actually closing the original connection that
you opened and there is no need to do dbconneciton.close().

Lastly, step through your code in debug mode to see if the connection
is closing.

re:
> As Bruce said, close it. Don't dispose it.

Interesting point for discussion, Kevin.

There's a question as to the *unmanaged* resources which need freeing,
over and above the need for freeing the *managed* .net resources.

See Rocky Lhotka's article :

http://www.lhotka.net/WeBlog/Dispose...ndObjects.aspx

"However, it also turns out that some Command objects really do have non-managed
resources that need to be disposed. Some don't. How do you know which do and
which don't? You need to ask the dev that wrote the code.

It turns out that SqlCommand has no un-managed resources, which is why most of us
have gotten away with this so far. However, OleDbCommand and OdbcCommand
do have un-managed resources and must be disposed to be safe."


Comments ?

btw, I just finished a fix for a client's application, developed by someone else,
and for which I was brought in as trouble-shooter, which was having resource problems.

It had 117 undisposed of OleDbCommand objects.

I disposed all of them...and the problem vanished.





Juan T. Llibre, asp.net MVP
aspnetfaq.com : http://www.aspnetfaq.com/
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
===================================

"Kevin Spencer" <> wrote in message
news:...
> As Bruce said, close it. Don't dispose it.
>
> --
> HTH,
>
> Kevin Spencer
> Microsoft MVP
> Professional Numbskull
>
> Show me your certification without works,
> and I'll show my certification
> *by* my works.
>
> "bannaman" <> wrote in message
> news: ups.com...
>> Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
>> should have been dim db as sqlconnection = dbconnection().
>>
>> As i am creating a connection in another function and passing it back
>> to my routine does it still close the conneciton that was opened when
>> calling dbconnection. do i not need to do dbconnection.close or will
>> db.close be enough?
>>
>
>

I'll take your word for it, Juan. I haven't done any OleDb stuff in a couple
of years. I would not be surprised if you were correct!

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.

"Juan T. Llibre" <> wrote in message
news:%...
> re:
>> As Bruce said, close it. Don't dispose it.
>
> Interesting point for discussion, Kevin.
>
> There's a question as to the *unmanaged* resources which need freeing,
> over and above the need for freeing the *managed* .net resources.
>
> See Rocky Lhotka's article :
>
> http://www.lhotka.net/WeBlog/Dispose...ndObjects.aspx
>
> "However, it also turns out that some Command objects really do have
> non-managed
> resources that need to be disposed. Some don't. How do you know which do
> and
> which don't? You need to ask the dev that wrote the code.
>
> It turns out that SqlCommand has no un-managed resources, which is why
> most of us
> have gotten away with this so far. However, OleDbCommand and OdbcCommand
> do have un-managed resources and must be disposed to be safe."
>
>
> Comments ?
>
> btw, I just finished a fix for a client's application, developed by
> someone else,
> and for which I was brought in as trouble-shooter, which was having
> resource problems.
>
> It had 117 undisposed of OleDbCommand objects.
>
> I disposed all of them...and the problem vanished.
>
>
>
>
>
> Juan T. Llibre, asp.net MVP
> aspnetfaq.com : http://www.aspnetfaq.com/
> asp.net faq : http://asp.net.do/faq/
> foros de asp.net, en español : http://asp.net.do/foros/
> ===================================
>
> "Kevin Spencer" <> wrote in message
> news:...
>> As Bruce said, close it. Don't dispose it.
>>
>> --
>> HTH,
>>
>> Kevin Spencer
>> Microsoft MVP
>> Professional Numbskull
>>
>> Show me your certification without works,
>> and I'll show my certification
>> *by* my works.
>>
>> "bannaman" <> wrote in message
>> news: ups.com...
>>> Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
>>> should have been dim db as sqlconnection = dbconnection().
>>>
>>> As i am creating a connection in another function and passing it back
>>> to my routine does it still close the conneciton that was opened when
>>> calling dbconnection. do i not need to do dbconnection.close or will
>>> db.close be enough?
>>>
>>
>>
>
>

oledbcommand doesn't have a close so you must call its dispose to release
unmanged resources.

with SqlConnection, it has a Close method, which you can call instead of
dispose (all dispose does is call Close), which can make the code more
readable. also you can call open again, while it not recommened to reinit
disposed objects.

if you are using c# (or vb.net 2.0), you probably shoudl switch to using
(which will call dispose even on an error)


using (SqlConnection conn = new SqlConnection())
{
// my sql code here
}


-- bruce (sqlwork.com)




"Juan T. Llibre" <> wrote in message
news:%...
> re:
>> As Bruce said, close it. Don't dispose it.
>
> Interesting point for discussion, Kevin.
>
> There's a question as to the *unmanaged* resources which need freeing,
> over and above the need for freeing the *managed* .net resources.
>
> See Rocky Lhotka's article :
>
> http://www.lhotka.net/WeBlog/Dispose...ndObjects.aspx
>
> "However, it also turns out that some Command objects really do have
> non-managed
> resources that need to be disposed. Some don't. How do you know which do
> and
> which don't? You need to ask the dev that wrote the code.
>
> It turns out that SqlCommand has no un-managed resources, which is why
> most of us
> have gotten away with this so far. However, OleDbCommand and OdbcCommand
> do have un-managed resources and must be disposed to be safe."
>
>
> Comments ?
>
> btw, I just finished a fix for a client's application, developed by
> someone else,
> and for which I was brought in as trouble-shooter, which was having
> resource problems.
>
> It had 117 undisposed of OleDbCommand objects.
>
> I disposed all of them...and the problem vanished.
>
>
>
>
>
> Juan T. Llibre, asp.net MVP
> aspnetfaq.com : http://www.aspnetfaq.com/
> asp.net faq : http://asp.net.do/faq/
> foros de asp.net, en español : http://asp.net.do/foros/
> ===================================
>
> "Kevin Spencer" <> wrote in message
> news:...
>> As Bruce said, close it. Don't dispose it.
>>
>> --
>> HTH,
>>
>> Kevin Spencer
>> Microsoft MVP
>> Professional Numbskull
>>
>> Show me your certification without works,
>> and I'll show my certification
>> *by* my works.
>>
>> "bannaman" <> wrote in message
>> news: ups.com...
>>> Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
>>> should have been dim db as sqlconnection = dbconnection().
>>>
>>> As i am creating a connection in another function and passing it back
>>> to my routine does it still close the conneciton that was opened when
>>> calling dbconnection. do i not need to do dbconnection.close or will
>>> db.close be enough?
>>>
>>
>>
>
>

heh, heh...

I wouldn't be surprised if Rocky was correct!

I practically worship the ground he walks on.






Juan T. Llibre, asp.net MVP
aspnetfaq.com : http://www.aspnetfaq.com/
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
===================================
"Kevin Spencer" <> wrote in message
news:...
> I'll take your word for it, Juan. I haven't done any OleDb stuff in a couple of years. I would not
> be surprised if you were correct!
>
> --
> HTH,
>
> Kevin Spencer
> Microsoft MVP
> Professional Numbskull
>
> Show me your certification without works,
> and I'll show my certification
> *by* my works.
>
> "Juan T. Llibre" <> wrote in message
> news:%...
>> re:
>>> As Bruce said, close it. Don't dispose it.
>>
>> Interesting point for discussion, Kevin.
>>
>> There's a question as to the *unmanaged* resources which need freeing,
>> over and above the need for freeing the *managed* .net resources.
>>
>> See Rocky Lhotka's article :
>>
>> http://www.lhotka.net/WeBlog/Dispose...ndObjects.aspx
>>
>> "However, it also turns out that some Command objects really do have non-managed
>> resources that need to be disposed. Some don't. How do you know which do and
>> which don't? You need to ask the dev that wrote the code.
>>
>> It turns out that SqlCommand has no un-managed resources, which is why most of us
>> have gotten away with this so far. However, OleDbCommand and OdbcCommand
>> do have un-managed resources and must be disposed to be safe."
>>
>>
>> Comments ?
>>
>> btw, I just finished a fix for a client's application, developed by someone else,
>> and for which I was brought in as trouble-shooter, which was having resource problems.
>>
>> It had 117 undisposed of OleDbCommand objects.
>>
>> I disposed all of them...and the problem vanished.
>>
>>
>>
>>
>>
>> Juan T. Llibre, asp.net MVP
>> aspnetfaq.com : http://www.aspnetfaq.com/
>> asp.net faq : http://asp.net.do/faq/
>> foros de asp.net, en español : http://asp.net.do/foros/
>> ===================================
>>
>> "Kevin Spencer" <> wrote in message
>> news:...
>>> As Bruce said, close it. Don't dispose it.
>>>
>>> --
>>> HTH,
>>>
>>> Kevin Spencer
>>> Microsoft MVP
>>> Professional Numbskull
>>>
>>> Show me your certification without works,
>>> and I'll show my certification
>>> *by* my works.
>>>
>>> "bannaman" <> wrote in message
>>> news: ups.com...
>>>> Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
>>>> should have been dim db as sqlconnection = dbconnection().
>>>>
>>>> As i am creating a connection in another function and passing it back
>>>> to my routine does it still close the conneciton that was opened when
>>>> calling dbconnection. do i not need to do dbconnection.close or will
>>>> db.close be enough?
>>>>
>>>
>>>
>>
>>
>
>