Database connections nightmare, help!

[beacon-dartmouth]

I have this page with two drop-down lists and several labels.

There is a BindData() subroutine that calls two methods: BindLabels1() and BindLabels2(). The BindData() sub opens and closes a connection to the DB, but when the BindLabels1() and BindLabels2() try 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 thinks the current state is CLOSED.

It may be I'm opening connections unnecessarily and I should manage it differently. In any case, I'm open to suggestions. Thanx, b-d


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.CommandType = CommandType.StoredProcedure
cmdSelect.Parameters.Add( "@applicationID", intApplicationID )
Try
conMeta.Open()
drApp = cmdSelect.ExecuteReader( )
' Display in Label Controls
If drApp.Read Then
lblAppID.Text = drApp( "Application_ID" )
lblName.Text = drApp( "Name" )
lblAcronym.Text = drApp( "Acronym" ).ToSTring()
lblProgArea.Text = drApp( "Program_Area_Name" ).ToString()
lblStartDate.Text = drApp( "Usage_Start_Date" ).ToString()
lblDesc.Text = drApp( "Description" ).ToString()
lblFieldDet.Text = drApp( "Field_Detail" ).ToString()
lblDivisionID.Text = drApp( "DivisionName" )
lblBureauID.Text = drApp( "BureauName" )
End If
Catch exc As Exception
Response.Write(exc)
Finally

If Not drApp Is Nothing Then
drApp.Close()
End If
CmdSelect = Nothing
If conMeta.State = ConnectionState.Open Then
conMeta.Close()
End If
conMeta.Dispose()
End Try
BindLabels1()
BindLabels2()
End Sub

Sub BindLabels1()

'Declare connection variables
Dim conMeta As SqlConnection
Dim cmdSelect As SqlCommand
Dim drCon As SqlDataReader
'Retrieve Application ID
Dim intApplicationID as Integer = Int32.Parse( Request.QueryString( "id" ) )
Try
conMeta = New SqlConnection( "Server='dbServer';trusted_connection=true;Database='databaseV2'" )
' Get Detail Information for Contact and Related/Child Tables
cmdSelect = New SqlCommand( "sAppConDetail", conMeta )
cmdSelect.CommandType = CommandType.StoredProcedure
cmdSelect.Parameters.Add( "@applicationID", intApplicationID )
drCon = cmdSelect.ExecuteReader( )
' Contact Information

If drCon.Read Then
lblAppConID.Text = drCon( "Application_Contact_ID" ).ToString()
lblFName.Text = drCon( "First_Name" ).ToString()
lblLName.Text = drCon( "Last_Name" ).ToString()
lblAddress.Text = drCon( "Street_Address" ).ToString()
lblCity.Text = drCon( "City" ).ToString()
lblState.Text = drCon( "State" ).ToString()
lblZip.Text = drCon( "Zip" ).ToString()
lblSite.Text = drCon( "Site" ).ToString()
lblSuncom.Text = drCon( "Suncom" ).ToString()
lblPhone.Text = drCon( "Phone_Number" ).ToString()
lblPExt.Text = drCon( "Phone_Extension" ).ToString()
lblEmail.Text = drCon( "Email_Address" ).ToString()
lblOwner.Text = drCon( "Owner" ).ToString()
lblDateC.Text = drCon( "Date_Contacted" ).ToString()
lblVerified.Text = drCon( "Verified" ).ToString()
End If

Catch exc As Exception
Response.Write(exc)
Finally
If Not drCon Is Nothing Then
drCon.Close()
End If
CmdSelect = Nothing
If conMeta.State = ConnectionState.Open Then
conMeta.Close()
End If
conMeta.Dispose()
End Try

End Sub

Sub BindLabels2()

'Declare connection variables
Dim conMeta As String
Dim cmdSelect As SqlCommand
Dim drDoc As SqlDataReader
'Retrieve Application ID
Dim intApplicationID as Integer = Int32.Parse( Request.QueryString( "id" ) )

Try
conMeta = New SqlConnection( "Server='dbServer';trusted_connection=true;Database='databaseV2'" )
cmdSelect = New SqlCommand( "sAppDocDetail", conMeta )
cmdSelect.CommandType = CommandType.StoredProcedure
cmdSelect.Parameters.Add( "@applicationID", intApplicationID )
drDoc = cmdSelect.ExecuteReader( )
' Documentation
If drDoc.Read Then
lblAuthor.Text = drDoc( "Authors" ).ToString()
lblTypeID.Text = drDoc( "DocType" ).ToString()
lblFilePath.Text = drDoc( "File_Path" ).ToString()
lbldDesc.Text = drDoc( "dDescription" ).ToString()
lblComments.Text = drDoc( "Comments" ).ToString()
End If

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
End Sub

Public Sub ddl_Changed(s As Object, e As EventArgs)
If s Is ddlCont Then
BindLabels1()
Else
BindLabels2()
End If
End Sub