Multiple datareader for multiple gridview

Question

User1948389125 posted

Hi

I need to show 2 gridview on the page, my stored procedure return 2 set of data to the datareader.

But when I bind the data to 2nd gridview, i got an error "Invalid attempt to call NextResult when reader is closed."

May I know how to do this multiple dr to multiple gridview? if only bind 1 gridview, this code got no problem, the problem is when bind to 2nd grid.

try
{
string _ConnectionString = ConfigurationSettings.AppSettings[GI.Utilities.Constants.ConnectionStringKey];
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter("@StartDate", SqlDbType.Date);
parameters[0].Value = txtStartDate.Text.ToString();
parameters[1] = new SqlParameter("@EndDate", SqlDbType.Date);
parameters[1].Value = txtEndDate.Text.ToString();
SqlDataReader dr = SqlHelper.ExecuteReader(_ConnectionString, CommandType.StoredProcedure, "sp_GetAttendanceReport", parameters);

if (dr != null)
{
if (dr.HasRows)
{
/*
while (dr.Read())
{
System.Diagnostics.Debug.Print(dr.GetName(5).ToString().ToString());
dr.NextResult();
}
*/
gvAttendanceClock.DataSource = dr;
gvAttendanceClock.DataBind();
dr.NextResult();
gvAttendanceReport.DataSource = dr;
gvAttendanceReport.DataBind();
}
dr.Close();
}
}
catch (Exception ex)
{
throw ex;
}

Answer 1

User-2054057000 posted

DataReader needs open connection to work. See 

Retrieve data using a DataReader

Answer 2

User61956409 posted

Hi arctura,

error "Invalid attempt to call NextResult when reader is closed."

The error indicates that data reader has been closed before you call NextResult(). But based on the code you provided, I do not find you close reader before call NextResult(). To troubleshoot the issue, I suggest you could debug your code step by step and trace the IsClosed property of data reader.

Besides, I made a sample about binding multiple GridView(s) via data reader, which work as expected on my side, you can refer to it.

SqlConnection conn = new SqlConnection(@"{connection_string_here}");
SqlCommand comm = new SqlCommand("select * from UserInfo where [Name]='Fei Han';select [Name],[Email] from UserInfo where [Name]='Fei Han'", conn);

conn.Open();

SqlDataReader reader = comm.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();

reader.NextResult();
GridView2.DataSource = reader;
GridView2.DataBind();

reader.Close();

conn.Close();

Test Result:

With Regards,

Fei Han

Answer 3

User1948389125 posted

Hi all

it works now, I create connection manually within the function and now it is working fine.

Thanks all for helping.