Reading Results From a Stored Procedure

[thecodewhore]

OK, so I have a stored procedure that ends like this ....

select @PulpMill as pulp_mill, @Supplier + ' - weighted' as supplier, @Species as species
 
select interval_date, weight_multiplied_cqi / total_weight as average_cqi
from @WeightedIntervalTable

when you actually run the procedure in the Query Analizer it works fine. However im trying to build an Interface in VB.NET that can get the results from it. So far I have....

        Try
            myConnection.Open()
            'opening the connection
            myCommand = New SqlCommand( _
            "exec fax_cstr_chart_data 'Hopewell', 'Pine'", myConnection)
 
            'executing the command and assigning it to connection 
            dr = myCommand.ExecuteReader()
            While dr.Read 'reading from the datareader
                'Build Row Data From DataReader
                Dim NewRow As String() = {dr.Item(0).ToString, dr.Item(1).ToString, dr.Item(2).ToString}
                Me.dgHP.Rows.Add(NewRow) 'Add New Row
            End While
            dr.Close()
            myConnection.Close()
        Catch ex As Exception
            MsgBox("Error : " & ex.Message)
        End Try

However it only returns the first Select statements result, then the reader closes and it doesnt return the second select statements values.... How do you setup a reader to do this???

[ilearneditonline]

You will need to use SqlDataReader.NextResult.


Link

[thecodewhore]

THANK YOU!! I knew it would be simple.