Problem with sqlserver connections not closing
-
I have an application which has dropdown list which is populated with values from a database table. The dropdown list is populated by a subroutine which in turn calls a function. The function uses a stored procedure to get the datat for the dropdown from the database table. Here is the code that is used: THIS IS THE SUBROUTINE Private Sub GetDepartments() '************************************************************ ' This method populates the Depts dropdown with a list ' of departments '************************************************************ MODULE_NAME = "GetDepartments" Dim oStaff As Staff Dim oDR As SqlDataReader Try oStaff = New Staff oDR = oStaff.GetDepartments() ddlSignedBy.Items.Add(New ListItem("", 0)) While oDR.Read() ddlSignedBy.Items.Add(New ListItem(oDR("Dept_tdp").ToString(), oDR("DeptId_tdp").ToString())) End While oDR.Close() Catch objException As Exception 'Display the exception message Dim strError As String = objException.Message If Not IsNothing(objException.InnerException) Then strError += objException.InnerException.Message End If Finally ' Clean Up oStaff = Nothing oDR = Nothing End Try End Sub THIS IS THE FUNCTION IT CALLS Public Function GetDepartments() As SqlDataReader '**************************************** ' This method gets list of departments '**************************************** MODULE_NAME = "GetDepartments" Dim Conn As New SqlConnection(sqlcon) Dim SqlCom As SqlCommand Dim Param As SqlParameter Try Conn.Open() SqlCom = New SqlCommand("sproc_GetDept", Conn) SqlCom.CommandType = CommandType.StoredProcedure Return SqlCom.ExecuteReader() Catch objException As Exception 'Raise an exception Dim strError As String = " [SubClass: " & CLASS_NAME & ", SubModule: " & MODULE_NAME & "] " & objException.Message If Not IsNothing(objException.InnerException) Then strError += objException.InnerException.Message End If Throw (New Exception(strError)) Finally ' Clean Up
-
I have an application which has dropdown list which is populated with values from a database table. The dropdown list is populated by a subroutine which in turn calls a function. The function uses a stored procedure to get the datat for the dropdown from the database table. Here is the code that is used: THIS IS THE SUBROUTINE Private Sub GetDepartments() '************************************************************ ' This method populates the Depts dropdown with a list ' of departments '************************************************************ MODULE_NAME = "GetDepartments" Dim oStaff As Staff Dim oDR As SqlDataReader Try oStaff = New Staff oDR = oStaff.GetDepartments() ddlSignedBy.Items.Add(New ListItem("", 0)) While oDR.Read() ddlSignedBy.Items.Add(New ListItem(oDR("Dept_tdp").ToString(), oDR("DeptId_tdp").ToString())) End While oDR.Close() Catch objException As Exception 'Display the exception message Dim strError As String = objException.Message If Not IsNothing(objException.InnerException) Then strError += objException.InnerException.Message End If Finally ' Clean Up oStaff = Nothing oDR = Nothing End Try End Sub THIS IS THE FUNCTION IT CALLS Public Function GetDepartments() As SqlDataReader '**************************************** ' This method gets list of departments '**************************************** MODULE_NAME = "GetDepartments" Dim Conn As New SqlConnection(sqlcon) Dim SqlCom As SqlCommand Dim Param As SqlParameter Try Conn.Open() SqlCom = New SqlCommand("sproc_GetDept", Conn) SqlCom.CommandType = CommandType.StoredProcedure Return SqlCom.ExecuteReader() Catch objException As Exception 'Raise an exception Dim strError As String = " [SubClass: " & CLASS_NAME & ", SubModule: " & MODULE_NAME & "] " & objException.Message If Not IsNothing(objException.InnerException) Then strError += objException.InnerException.Message End If Throw (New Exception(strError)) Finally ' Clean Up
-
I have an application which has dropdown list which is populated with values from a database table. The dropdown list is populated by a subroutine which in turn calls a function. The function uses a stored procedure to get the datat for the dropdown from the database table. Here is the code that is used: THIS IS THE SUBROUTINE Private Sub GetDepartments() '************************************************************ ' This method populates the Depts dropdown with a list ' of departments '************************************************************ MODULE_NAME = "GetDepartments" Dim oStaff As Staff Dim oDR As SqlDataReader Try oStaff = New Staff oDR = oStaff.GetDepartments() ddlSignedBy.Items.Add(New ListItem("", 0)) While oDR.Read() ddlSignedBy.Items.Add(New ListItem(oDR("Dept_tdp").ToString(), oDR("DeptId_tdp").ToString())) End While oDR.Close() Catch objException As Exception 'Display the exception message Dim strError As String = objException.Message If Not IsNothing(objException.InnerException) Then strError += objException.InnerException.Message End If Finally ' Clean Up oStaff = Nothing oDR = Nothing End Try End Sub THIS IS THE FUNCTION IT CALLS Public Function GetDepartments() As SqlDataReader '**************************************** ' This method gets list of departments '**************************************** MODULE_NAME = "GetDepartments" Dim Conn As New SqlConnection(sqlcon) Dim SqlCom As SqlCommand Dim Param As SqlParameter Try Conn.Open() SqlCom = New SqlCommand("sproc_GetDept", Conn) SqlCom.CommandType = CommandType.StoredProcedure Return SqlCom.ExecuteReader() Catch objException As Exception 'Raise an exception Dim strError As String = " [SubClass: " & CLASS_NAME & ", SubModule: " & MODULE_NAME & "] " & objException.Message If Not IsNothing(objException.InnerException) Then strError += objException.InnerException.Message End If Throw (New Exception(strError)) Finally ' Clean Up
I see now... you should initialize your connection object in Sub, pass it to Function as a parameter and close it in Sub after use. Or you can have your Function returning something else than SqlDataReader, DataTable for example, so you dont need to use connection in Sub and you can close it in Function. Pilo
-
Well, maybe I don't understand your problem correctly, but why you don't close your connection in finally {...} block? Pilo
If I close connection in finally I need to close it in the function, this solves that problem but this causes another problem that the datareader does not read in the Subroutine because the connection is closed. I cannot close the connection in the finally of the Subroutine as it has been opened in the function. I may just have to put everything in the Subroutine and just not bother splitting my code up into the Function. macca
-
I have an application which has dropdown list which is populated with values from a database table. The dropdown list is populated by a subroutine which in turn calls a function. The function uses a stored procedure to get the datat for the dropdown from the database table. Here is the code that is used: THIS IS THE SUBROUTINE Private Sub GetDepartments() '************************************************************ ' This method populates the Depts dropdown with a list ' of departments '************************************************************ MODULE_NAME = "GetDepartments" Dim oStaff As Staff Dim oDR As SqlDataReader Try oStaff = New Staff oDR = oStaff.GetDepartments() ddlSignedBy.Items.Add(New ListItem("", 0)) While oDR.Read() ddlSignedBy.Items.Add(New ListItem(oDR("Dept_tdp").ToString(), oDR("DeptId_tdp").ToString())) End While oDR.Close() Catch objException As Exception 'Display the exception message Dim strError As String = objException.Message If Not IsNothing(objException.InnerException) Then strError += objException.InnerException.Message End If Finally ' Clean Up oStaff = Nothing oDR = Nothing End Try End Sub THIS IS THE FUNCTION IT CALLS Public Function GetDepartments() As SqlDataReader '**************************************** ' This method gets list of departments '**************************************** MODULE_NAME = "GetDepartments" Dim Conn As New SqlConnection(sqlcon) Dim SqlCom As SqlCommand Dim Param As SqlParameter Try Conn.Open() SqlCom = New SqlCommand("sproc_GetDept", Conn) SqlCom.CommandType = CommandType.StoredProcedure Return SqlCom.ExecuteReader() Catch objException As Exception 'Raise an exception Dim strError As String = " [SubClass: " & CLASS_NAME & ", SubModule: " & MODULE_NAME & "] " & objException.Message If Not IsNothing(objException.InnerException) Then strError += objException.InnerException.Message End If Throw (New Exception(strError)) Finally ' Clean Up
Hi, We have to use the below overloaded method on the executereader method of the command obect to close the connection automatically, otherwise we have to cloase manually. objCommand.ExecuteReader(CommandBehavior.CloseConnection) Ram
-
Hi, We have to use the below overloaded method on the executereader method of the command obect to close the connection automatically, otherwise we have to cloase manually. objCommand.ExecuteReader(CommandBehavior.CloseConnection) Ram
-
I have an application which has dropdown list which is populated with values from a database table. The dropdown list is populated by a subroutine which in turn calls a function. The function uses a stored procedure to get the datat for the dropdown from the database table. Here is the code that is used: THIS IS THE SUBROUTINE Private Sub GetDepartments() '************************************************************ ' This method populates the Depts dropdown with a list ' of departments '************************************************************ MODULE_NAME = "GetDepartments" Dim oStaff As Staff Dim oDR As SqlDataReader Try oStaff = New Staff oDR = oStaff.GetDepartments() ddlSignedBy.Items.Add(New ListItem("", 0)) While oDR.Read() ddlSignedBy.Items.Add(New ListItem(oDR("Dept_tdp").ToString(), oDR("DeptId_tdp").ToString())) End While oDR.Close() Catch objException As Exception 'Display the exception message Dim strError As String = objException.Message If Not IsNothing(objException.InnerException) Then strError += objException.InnerException.Message End If Finally ' Clean Up oStaff = Nothing oDR = Nothing End Try End Sub THIS IS THE FUNCTION IT CALLS Public Function GetDepartments() As SqlDataReader '**************************************** ' This method gets list of departments '**************************************** MODULE_NAME = "GetDepartments" Dim Conn As New SqlConnection(sqlcon) Dim SqlCom As SqlCommand Dim Param As SqlParameter Try Conn.Open() SqlCom = New SqlCommand("sproc_GetDept", Conn) SqlCom.CommandType = CommandType.StoredProcedure Return SqlCom.ExecuteReader() Catch objException As Exception 'Raise an exception Dim strError As String = " [SubClass: " & CLASS_NAME & ", SubModule: " & MODULE_NAME & "] " & objException.Message If Not IsNothing(objException.InnerException) Then strError += objException.InnerException.Message End If Throw (New Exception(strError)) Finally ' Clean Up