Error while using NextResult fuction with datareader

Question

cannot get second table result and error on second NextResult line " invalid attempt to call nextresult when reader is closed "

using (SqlConnection myCon = DBCon)
{
    try
    {
       string Qry =  @"SELECT [OPSProcedure],[OPSInsertedOn],[OPSInsertedBy]
           FROM [Operation] where OPSID =  '" + opId + "';";

       Qry += @"SELECT  LKCPID  FROM dbo.ConcurrentProcedure  where CPOperationID = '" + opId + "';";

       Qry += @"SELECT  IOperaitonID  FROM  dbo.LkupIntraOperativeAdverseEvents   where IOperaitonID = '" + opId + "';";

        myCon.Open();
        SqlCommand myCommand = new SqlCommand(Qry, myCon);
        myCommand.CommandType = CommandType.Text;
        SqlDataReader sqlReader = myCommand.ExecuteReader();
        DataSet dr = new DataSet();
        if (sqlReader.HasRows)
        {
            dt1.Load(sqlReader);
            if(sqlReader.NextResult())
            {
            dt2.Load(sqlReader);
            }
            if (sqlReader.NextResult())
            {
            dt3.Load(sqlReader);
            }

        }
        sqlReader.Close();
   }
   catch (Exception ex)
   {

   }
}

What I have tried:

i have tried using below code for multiple result

Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Aug 12 '18 at 12:26

mjwills · Answer 1 · 2018-08-12T12:54:42.917

2

DataTable.Load closes the sqlReader if sqlReader.IsClosed is false and NextResults returns false as per this forum.

As such, instead of:

if (sqlReader.NextResult())

you need to use:

if (!sqlReader.IsClosed && sqlReader.NextResult() && sqlReader.HasRows)

edited Aug 12 '18 at 12:54

answered Aug 12 '18 at 12:30

mjwills

23,389
6
40
63

i am trying to copy three query data into Datatable could you help me with code sinppet – Sultan Aug 12 '18 at 12:52
Did you try the code replacement I suggest above @Sultan? i.e. replace all lines that say `if (sqlReader.NextResult())` with the line I suggested? – mjwills Aug 12 '18 at 12:53

score 1 · Accepted Answer · answered Aug 12 '18 at 13:01

In this context I would simply use an SqlDataAdapter to make one single call and fill all your tables

using (SqlConnection myCon = DBCon)
{
    try
    {
       string Qry =  @"SELECT [OPSProcedure],[OPSInsertedOn],[OPSInsertedBy]
                       FROM [Operation] where OPSID =  @id;
                       SELECT  LKCPID  FROM dbo.ConcurrentProcedure  
                       where CPOperationID = @id;
                       SELECT IOperaitonID FROM dbo.LkupIntraOperativeAdverseEvents   
                       where IOperaitonID = @id";
        myCon.Open();
        SqlDataAdapter da = new SqlDataAdapter(Qry, myCon);
        da.SelectCommand.Parameter.Add("@id", SqlDbType.NVarChar).Value = opID;
        DataSet ds = new DataSet();
        da.Fill(ds);

        // Test...
        Console.WriteLine(ds.Tables[0].Rows.Count);
        Console.WriteLine(ds.Tables[1].Rows.Count);
        Console.WriteLine(ds.Tables[2].Rows.Count);

Notice also that you should never concatenate strings to build sql commands. Always use parameters.

Error while using NextResult fuction with datareader

2 Answers2