Stone Coast Web Design's Weblog

One of the problems with the DBDataReader object is that it keeps an open server-side connection while you are looping through the results of a query.  If you try to execute another command while the first command is still executing, you receive an 'InvalidoperationException' stating that "There is already an open DataReader associated with this connection which must be closed first."  You can avoid this exception by setting the MultipleActiveResultSets connection string option to True when connecting to SQL Server 2005 and above.

<configuration> 
<connectionStrings> 
<clear /> 
<add name="PublishersConnectionString" connectionString="Data Source=localhost;Initial Catalog=Publishers;User ID=xxx;Password=xxx" 
providerName="System.Data.SqlClient; MultipleActiveResultSets=True" />  
</connectionStrings> 

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("PublishersConnectionString").ConnectionString)

        'Create the command object, passing in the SQL string
        Dim strSQL As String = "SELECT au_id, LastName FROM Publishers"
        Dim cmd As New SqlCommand(strSQL)

        Try
            con.Open()
            cmd.Connection = con
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                Dim cmdSales As SqlCommand
                cmdSales.CommandType = CommandType.Text
                cmdSales.CommandText = "SELECT  SUM(royaltyper) FROM TitleAuthor WHERE (au_id = @auID)"
                cmdSales.Parameters.AddWithValue("@au_id", reader("au_id"))
                Dim qtySales As Object = cmdSales.ExecuteScalar()
                lbl.Text += reader("LastName").ToString() + ": " + String.Format("{0:C}", qtySales) + "<br />"
            End While
        Finally
            con.Close()
            con.Dispose()
            End
        End Try
    End Sub

Another example and working demo using a TreeView:

http://stonecoastwebdesign.com/code/ShowMARS.aspx