locked
Multiple datareader for multiple gridview RRS feed

  • 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;
    }

    Wednesday, January 9, 2019 8:36 AM

Answers

  • User-2054057000 posted

    DataReader needs open connection to work. See 

    Retrieve data using a DataReader

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 9, 2019 10:42 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 10, 2019 5:40 AM

All replies

  • User-2054057000 posted

    DataReader needs open connection to work. See 

    Retrieve data using a DataReader

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 9, 2019 10:42 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 10, 2019 5:40 AM
  • User1948389125 posted

    Hi all

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

    Thanks all for helping.

    Thursday, January 10, 2019 6:17 AM