After spending quite some time working on my site I finally went live (http://www.withcharacter.com). I recently realized an error that "TIMEOUT EXPIRED."
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
I am not exactly sure what is causing this problem. Does anyone know anything about it and how to get around it?
Your database can handle some number of connection at a time. If the number of connection exceeds its pre-chosen value, it will give the mentioned error. Make sure that in your code each and every connection is closed explicitly after accessing db.
However, the only place where I establish a connection to my database is in my Components - and it is practically always through a stored procedure. I usually do something like
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("CMRC_ProductsByCategory", myConnection)
'add parameters.....
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
However, sometimes I want to return the entire results of a select query so I do something like....
myConnection.Open()
Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return result
Is my syntax is the latter (green) example correct? Does this line
Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
actually close the connection or do I need to enter a
myConnection.Close() afterwards?
This is really the only thing I can think of that would leave an open database connection. Are there any other possibilites?
myConnection.Open()
Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return result
After using this code, whenever you fetch data from "result", write a code like
if not result.IsClosed then
result.close()
end if
and check it out in debug mode, whether the connection was closed or was still open.
The other thing might be, you have right to open only few number of connection with your database. and if you are reaching to max no of connection, the next connection will time out. but i dont think any hosting provider will do like this.
wabs27
Member
278 Points
107 Posts
Live Site - Timeout expired!
Nov 07, 2005 09:23 PM|LINK
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
I am not exactly sure what is causing this problem. Does anyone know anything about it and how to get around it?
P.S. What do you all think of the site?
monu245
Participant
904 Points
177 Posts
Re: Live Site - Timeout expired!
Nov 14, 2005 03:27 PM|LINK
http://www.codaddict.com - A new way to share/store/bookmark your code snippets
wabs27
Member
278 Points
107 Posts
Re: Live Site - Timeout expired!
Nov 14, 2005 10:05 PM|LINK
Thanks for answering me.
However, the only place where I establish a connection to my database is in my Components - and it is practically always through a stored procedure. I usually do something like
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("CMRC_ProductsByCategory", myConnection)
'add parameters.....
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
However, sometimes I want to return the entire results of a select query so I do something like....
myConnection.Open()
Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return result
Is my syntax is the latter (green) example correct? Does this line Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection) actually close the connection or do I need to enter a myConnection.Close() afterwards?
This is really the only thing I can think of that would leave an open database connection. Are there any other possibilites?
Thanks.
monu245
Participant
904 Points
177 Posts
Re: Live Site - Timeout expired!
Dec 03, 2005 05:35 AM|LINK
Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return result
After using this code, whenever you fetch data from "result", write a code like
if not result.IsClosed then
result.close()
end if
and check it out in debug mode, whether the connection was closed or was still open.
The other thing might be, you have right to open only few number of connection with your database. and if you are reaching to max no of connection, the next connection will time out. but i dont think any hosting provider will do like this.
http://www.codaddict.com - A new way to share/store/bookmark your code snippets