There is already an open DataReader associated with this Command which must be closed first

can you please show code for this

WTPTableAdapter.sp_ScalarQuery_CountMandatory

or put a breakpoint here and "step into" here when prg hits this to find where the exact problem is...

I put a breakpoint there and the program loops through it a couple of times then stops with the open DataReader exception right there on the lblMandatory.Text = "Count..." line in the WTPDataGridView_RowStateChanged event handler.  The whole .vb file is <150 lines.  I can post it if you want but here is the stored procedure for setting the label to the count of "Y"s in the GridView with the filters set.

WTPTableAdapter.sp_ScalarQuery_CountMandatory :

ALTER PROCEDURE dbo.sp_ScalarQuery_CountMandatory
(
	@OneStop varchar(50),
	@CaseManagerLName varchar(50),
	@FromDate smalldatetime,
	@ToDate smalldatetime,
	@ReviewerName varchar(50),
	@ReviewType varchar(50)
)
AS
	SET NOCOUNT ON;
SELECT        COUNT(1) 
FROM            WTP
WHERE        (OneStop LIKE @OneStop) AND (CaseManagerLName LIKE @CaseManagerLName) AND (ReviewDate BETWEEN @FromDate AND @ToDate) AND 
                         (ReviewerName LIKE @ReviewerName) AND (ReviewType LIKE @ReviewType) AND (Mandatory LIKE 'Y')

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

have you read my short article? by default you can only have a single DataReader opened at a single time. my article shows you how to modify your connection string to be able to open more then one.

I'm reading through it now.  If I *only* modify the connection string will it allow that line to work or do I have to throw in the BeginExecuteXXX and EndExecuteXXX somewhere?  (I'm not quite finished reading the article)

The BeginExecuteXXX is for asynchronous read which is not what you are trying to do. Are you using SQL 2005? Can you show your new connection string?

I'm not sure how to tell the procedure to use the connection string from the app.config so I put it in the code.

I haven't modified my connection string yet to try your method because I wasn't sure how much I would have to change my code to implement it.  I'm just learning, so the more changes I have to make to something the more likely I'll either break it or get to a point that I don't understand what it's doing and can't maintain it.

So, for now I tried doing it like this.  It seems to work but this is all to count the results of only one question.  I have 43 questions to report the counts of so this doesn't look like the best solution:

    Private Sub WTPDataGridView_RowStateChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowStateChangedEventArgs) Handles WTPDataGridView.RowStateChanged
        lblCount.Text = "Count: " & CStr(WTPDataGridView.RowCount - 1)
        If (Not IsNothing(cbCaseManagers.SelectedValue)) And (Not IsNothing(cbOneStop.SelectedValue)) Then
            If TypeOf (cbCaseManagers.SelectedValue) Is String Then
                'Dim sConnStr As String = ConfigurationManager.ConnectionStrings("CSPConnectionString1").ConnectionString
                Dim DBConn As New SqlClient.SqlConnection("Data Source=BETASERVE;Initial Catalog=SigmaTools;Integrated Security=True;")
                Dim SQLCmd As New SqlClient.SqlCommand("sp_ScalarQuery_CountMandatory", DBConn)
                SQLCmd.CommandType = Data.CommandType.StoredProcedure
                DBConn.Open()
                Try
                    SQLCmd.Parameters.Add("@OneStop", SqlDbType.VarChar).Value = CStr(cbOneStop.SelectedValue)
                    SQLCmd.Parameters.Add("@CaseManagerLName", SqlDbType.VarChar).Value = CStr(cbCaseManagers.SelectedValue)
                    SQLCmd.Parameters.Add("@FromDate", SqlDbType.SmallDateTime).Value = CStr(FromDate.Value)
                    SQLCmd.Parameters.Add("@ToDate", SqlDbType.SmallDateTime).Value = CStr(ToDate.Value)
                    SQLCmd.Parameters.Add("@ReviewerName", SqlDbType.VarChar).Value = CStr(cbReviewer.SelectedValue)
                    SQLCmd.Parameters.Add("@ReviewType", SqlDbType.VarChar).Value = CStr(cbReviewType.Text)
                    Dim varCount As Integer = CInt(SQLCmd.ExecuteScalar())
                    lblMandatory.Text = "Count of Mandatory: " & CStr(varCount)
                Catch ex As Exception
                    System.Windows.Forms.MessageBox.Show(ex.Message)
                End Try
 
                DBConn.Close()
 
            End If
        End If
    End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

Well, using the method in the code box just above, I don't have the exception problem because I'm opening a new connection anyway.

If I just add "MultipleActiveResultSets=True" to the connection string in my app.config file can I then go back to the method where I only have the line:

lblMandatory.Text = "Count of Mandatory: " & CStr(WTPTableAdapter.sp_ScalarQuery_CountMandatory(CStr(cbOneStop.SelectedValue), CStr(cbCaseManagers.SelectedValue), FromDate.Value, ToDate.Value, CStr(cbReviewer.SelectedValue), cbReviewType.Text))

...instead of that whole block of code in the two nested If/Then blocks?

I have no idea! It is really hard for me to follow you when I don't have the complete picture.

­­>>I don't have the exception problem because I'm opening a new connection anyway.

Don't forget that ADO.net is pooling the connections. Just add the additional parameter to all your connection strings and your problem will be solved.

I added "MultipleActiveResultSets=True" to the connection string in the app.config, removed that whole block of code (all the DBConn, SQLCmd stuff) and put the on line back in:

"lblMandatory.Text = "Count of Mandatory: " & CStr(WTPTableAdapter.sp_ScalarQuery_CountMandatory..."

and it works.

Now I think I can add a stored procedure for each of the 43 questions (ugh!) and a similar line to run each of them unless there is a better way.

you can surely do a query (or a stored proc) that will return all the data you need in a single request to the server.

Could you elaborate on that a little?  Like I said, I'm just learning this stuff.  If there's a simple solution I'd like to learn how to do it.

Are you saying that it's possible to return the counts of all the questions in a single query?

Right now the stored procedure (in comment ID:25569820) is just returning the count of "Y" values in the "Mandatory" column.  There are 42 additional columns I need to do the same with. (count "Y"s currently displaying in the GridView as a result of the filters selected by the combo boxes)

The database contains records created by the submission of a questionaire like form that asks for FirstName, LastName (unimportant), CaseManager, Reviewer, OneStop, FromDate, ToDate, ReviewType.  Those six fields are used as parameters in the stored procedure to filter the data displayed in the GridView.  In addition to those fields are 43 questions.  Each question is a field or column in the record.  "Mandatory" is one of the 43 question columns.  The question can be answered either "Y" or "N" so when the GridView is filtered using the combo boxes the  stored procedure counts the displayed "Y" answers in that one column (of 43), currently only "Mandatory".

Yes, I think the UNION query is exactly what I would need.  Possibly two of them because I may also need to count the "N" responses to each question.

Yes, each part of the UNION query would be identical except for the 'YourSecondField', 'YourThirdField' would be different for each, just like you've shown.