Database connections problem
-
I have a page with two drop-down lists, several labels, and a few DB connections. There is a BindData() subroutine that after populating two dropdown lists and a few labels it calls two methods: BindLabels1() and BindLabels2(). The BindData() sub opens and closes a DB connection without problems. The two methods BindLabels1() and BindLabels2() also open and close DB connections, but when they get to execute a data reader, the server throws the following exception: "System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is Closed. at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean executing) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() " Even though it is clear in the code (if I'm not mistaken) that I do have the statements needed to open and close connections properly, for some reason it doesn't. It's possible I'm opening connections unnecessarily and I should manage it differently. In any case, I'm open to suggestions. Thanx, b-d CODE: Sub Page_Load( s as Object, e as EventArgs ) If Not IsPostBack Then BindData() End If End Sub Sub BindData() Dim conMeta As SqlConnection Dim cmdSelect As SqlCommand Dim drCon As SqlDataReader Dim drDoc As SqlDataReader Dim intApplicationID as Integer = Int32.Parse( Request.QueryString( "id" ) ) conMeta = New SqlConnection( " Server='dbserver';trusted_connection=true;Database ='database'" ) cmdSelect = New SqlCommand( "sDdlContDoc", conMeta ) cmdSelect.CommandType = CommandType.StoredProcedure cmdSelect.Parameters.Add( "@applicationID", intApplicationID ) Try conMeta.open() drCon = cmdselect.ExecuteReader( ) ddlCont.DataSource = drCon ddlCont.DataTextField = "Contact_ID" ddlCont.selectedindex = "0" ddlCont.DataBind() drCon.Close() drDoc = cmdselect.ExecuteReader( ) ddlDocs.DataSource = drDoc ddlDocs.DataTextField = "Document_ID" ddlDocs.selectedindex = "0" ddlDocs.DataBind() Catch exc As Exception Response.Write(exc) Finally If Not drDoc Is Nothing Then drDoc.close() End If cmdSelect = Nothing If conMeta.State = ConnectionState.Open Then conMeta.close() End If conMeta.Dispose() End Try Dim drApp As SqlDataReader conMeta = New SqlConnection( " Server='dbserver';trusted_connection=true;Database ='databaseV2'" ) cmdSelect = New SqlCommand( "sAppGenInfoDetail", conMeta ) cmdSelect.Comman
-
I have a page with two drop-down lists, several labels, and a few DB connections. There is a BindData() subroutine that after populating two dropdown lists and a few labels it calls two methods: BindLabels1() and BindLabels2(). The BindData() sub opens and closes a DB connection without problems. The two methods BindLabels1() and BindLabels2() also open and close DB connections, but when they get to execute a data reader, the server throws the following exception: "System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is Closed. at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean executing) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() " Even though it is clear in the code (if I'm not mistaken) that I do have the statements needed to open and close connections properly, for some reason it doesn't. It's possible I'm opening connections unnecessarily and I should manage it differently. In any case, I'm open to suggestions. Thanx, b-d CODE: Sub Page_Load( s as Object, e as EventArgs ) If Not IsPostBack Then BindData() End If End Sub Sub BindData() Dim conMeta As SqlConnection Dim cmdSelect As SqlCommand Dim drCon As SqlDataReader Dim drDoc As SqlDataReader Dim intApplicationID as Integer = Int32.Parse( Request.QueryString( "id" ) ) conMeta = New SqlConnection( " Server='dbserver';trusted_connection=true;Database ='database'" ) cmdSelect = New SqlCommand( "sDdlContDoc", conMeta ) cmdSelect.CommandType = CommandType.StoredProcedure cmdSelect.Parameters.Add( "@applicationID", intApplicationID ) Try conMeta.open() drCon = cmdselect.ExecuteReader( ) ddlCont.DataSource = drCon ddlCont.DataTextField = "Contact_ID" ddlCont.selectedindex = "0" ddlCont.DataBind() drCon.Close() drDoc = cmdselect.ExecuteReader( ) ddlDocs.DataSource = drDoc ddlDocs.DataTextField = "Document_ID" ddlDocs.selectedindex = "0" ddlDocs.DataBind() Catch exc As Exception Response.Write(exc) Finally If Not drDoc Is Nothing Then drDoc.close() End If cmdSelect = Nothing If conMeta.State = ConnectionState.Open Then conMeta.close() End If conMeta.Dispose() End Try Dim drApp As SqlDataReader conMeta = New SqlConnection( " Server='dbserver';trusted_connection=true;Database ='databaseV2'" ) cmdSelect = New SqlCommand( "sAppGenInfoDetail", conMeta ) cmdSelect.Comman
Hello: beacon-dartmouth wrote: BindLabels1() and BindLabels2() also open and close DB connections Actually you forgot to open them in both methods. So , write your conMeta.Open() right before calling the ExecuteReader just as you did inside the BindData(). And yes you can ease your life using a single variable for a connection... Create it once, and open it and close it when you need(better to put it in a base page from which inherit all the others with all the common behavior).
-
Hello: beacon-dartmouth wrote: BindLabels1() and BindLabels2() also open and close DB connections Actually you forgot to open them in both methods. So , write your conMeta.Open() right before calling the ExecuteReader just as you did inside the BindData(). And yes you can ease your life using a single variable for a connection... Create it once, and open it and close it when you need(better to put it in a base page from which inherit all the others with all the common behavior).
Thank you killer, You made my day, man. thanx again. I knew there was a more efficient way of handling connectivity but I blanked out. I like your suggestion of using a single variable for a connection. Can you point me to a good example I could look at?? have a good weekend. b-d