Executing multiple readers in ASP.Net c#

Question

I am trying to use iteration in C# to clean up what would be otherwise a lengthy repetitive process of writing the same query but with one different parameter and binding data etc..

However I am having a problem with using the same reader.

This is my code at the moment:

private void bindData()
{
    string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    using (SqlConnection connection = new SqlConnection(cs))
    {
        connection.Open();

        string[] days = { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" };

        for (int i = 0; i < 7; i++)
        {
            SqlCommand command = new SqlCommand("SELECT Name AS 'Class Name', FirstName AS 'Instructor First Name', LastName AS 'Instructor Last Name', LessonDuration AS 'Lesson Duration (hrs)', LessonTime AS 'Lesson Start' FROM Lesson, Class, Instructor WHERE Lesson.ClassID = Class.ClassID AND Lesson.InstructorID = Instructor.InstructorID AND LessonDay = '@Day';", connection);
            command.Parameters.AddWithValue("@Day", days[i]);

            using(SqlDataReader dr = command.ExecuteReader())
            {
                switch (days[i])
                {
                    case "Monday":
                        GridView1.DataSource = dr;
                        DataBind();
                        break;
                    case "Tuesday":
                        GridView2.DataSource = dr;
                        DataBind();
                        break;
                    case "Wednesday":
                        GridView3.DataSource = dr;
                        DataBind();
                        break;
                    case "Thursday":
                        GridView4.DataSource = dr;
                        DataBind();
                        break;
                    case "Friday":
                        GridView5.DataSource = dr;
                        DataBind();
                        break;
                    case "Saturday":
                        GridView6.DataSource = dr;
                        DataBind();
                        break;
                    case "Sunday":
                        GridView7.DataSource = dr;
                        DataBind();
                        break;
                }
                dr.Close();

            }


        }
        connection.Close();
    }
}

As you can see, the point is to bind different queries to different grid views that represent different days of the week.

I keep getting errors of either "Invalid fieldcols data reader is closed" or "Please close before reading again" (if the dr.close(); is removed).

Is there a solution to this problem? Thank you.

score 0 · Answer 1 · answered Oct 29 '17 at 13:31

You are calling the DataBind() method of the parent class in all cases of your switch statement. This method will call the DataBind() method of all databound controls that it contains.

Therefore The DataBind() method of all GridViews will be called for each day of the week. If you close the data reader each time, you will get the close it before reading it again message, if you don't you will get the no data returned from those that have already been read.

Either move the DataBind() call to outside your for loop or call GridView[X].DataBind() instead. e.g.

case "Monday": GridView1.DataSource = dr; GridView1.DataBind(); break; case "Tuesday": GridView2.DataSource = dr; GridView2.DataBind(); break;

I tried doing this, it removed the errors but no data showed up in my gridviews. Is there some sort of problem with the indexing or the query? — Joe Law, Oct 29 '17 at 13:39
Interesting... If you look at this answer https://stackoverflow.com/a/16381518/395675 the OP says the same thing. The OP is directed to this blog https://weblogs.asp.net/gurusarkar/binding-list-of-custom-class-to-gridview-or-listview-control to set up columns in the GridView — Philip Smith, Oct 29 '17 at 13:43

Executing multiple readers in ASP.Net c#

1 Answers1