Connection Timeout

[mana2]

Hi,

I'm getting a connection timeout. Every page on the site calls a function in a class that uses a dataset and I also have a user control that is used for navigation that has 3 functions on it. I am opening a connection for each of the functions and wondered how I could combine it and whether this might be a reason for the connection leak. This is 2 of the functions. Each function returns a different variable:

Function GetLessonID(ByVal courseNumber As Integer, ByVal lessonNumber As Integer, ByVal pageNumber As Integer) As IDataReader
Dim strConnection As String
strConnection = ConfigurationSettings.AppSettings("ConnectionString")

Dim dbConnection As New SqlConnection(strConnection)

Dim queryString As String = "SELECT [tblPage].[LessonID] FROM [tblPage], [tblLesson], [tblCourse] WHERE (([tblPage].[Pa"& _
"geNumber] = @PageNumber) AND ([tblLesson].[LessonNumber] = @LessonNumber) AND (["& _
"tblCourse].[CourseNumber] = @CourseNumber)) AND tblPage.lessonID = tblLesson.lessonID AND tblLesson.CourseID = tblCourse.CourseID"

Dim dbCommand As New SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_courseNumber As IDataParameter = New SqlParameter
dbParam_courseNumber.ParameterName = "@CourseNumber"
dbParam_courseNumber.Value = courseNumber
dbParam_courseNumber.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam_courseNumber)
Dim dbParam_lessonNumber As IDataParameter = New SqlParameter
dbParam_lessonNumber.ParameterName = "@LessonNumber"
dbParam_lessonNumber.Value = lessonNumber
dbParam_lessonNumber.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam_lessonNumber)
Dim dbParam_pageNumber As IDataParameter = New SqlParameter
dbParam_pageNumber.ParameterName = "@PageNumber"
dbParam_pageNumber.Value = pageNumber
dbParam_pageNumber.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam_pageNumber)

dbConnection.Open

Dim dataReader As IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

If (dataReader.Read = True) Then
LessonID=dataReader("LessonID")
End If

Return dataReader
End Function


Function GetMaxPage(ByVal lessonID As Integer) As IDataReader

Dim strConnection As String
strConnection = ConfigurationSettings.AppSettings("ConnectionString")

Dim dbConnection As New SqlConnection(strConnection)


Dim queryString As String = "SELECT TOP 1 MAX([tblPage].[PageNumber]) FROM [tblPage] WHERE ([tblPage].[LessonID] = @Lesso"& _
"nID)"
Dim dbCommand As New SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_lessonID As IDataParameter = New SqlParameter
dbParam_lessonID.ParameterName = "@LessonID"
dbParam_lessonID.Value = lessonID
dbParam_lessonID.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam_lessonID)

dbConnection.Open
Dim dataReader As IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

If (dataReader.Read = True) Then
MaxNumber=dataReader(0)
End If

Return dataReader
End Function

Thanks so much.

 

[WildT]

You have to dispose and null your connections before you re-use them. So if you have every time you establish a new connection you should say:

Dim dbConnection As New SqlConnection(strConnection)

and so on

and MORE IMPORTANTLY every time you are finished with a connection you should say:

dbConnection.Dispose();
dbConnection=null;

this should keep the database from locking up...

if you are intrested in stopping other resource leeks you should also do this with any datasets and other objects that tend to contain alot of data.

 

[mana2]

Thank You so much. I'm trying the Dispose to see if that works better. It didn't recognize null.

 

[checkai]

if you're using vb.net then use dbConnection = nothing

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[mana2]

Great. Thanks.