Why is no data found after calling SqlDataReader.NextResult?

Question

My problem is that this does not work;

while (reader.Read())
{
   if (reader.NextResult() == true)
   {
      json.AppendFormat("{{\"AvgDate\": \"{0}\"}},{{\"MarkerID\": \"{1}\"}},", reader["AvgDate"], reader["MarkerID"]);
   }

But this works;

while (reader.Read())
{
    json.AppendFormat("{{\"AvgDate\": \"{0}\"}},{{\"MarkerID\": \"{1}\"}},", reader["AvgDate"], reader["MarkerID"]);
}

The problem with the first one is that the reader doesn't find any data to read. I get;

"Invalid attempt to read when no data is present."

Can anyone see why?

Please change the title of this question to a question. "SqlDataReader problem" is extremely uninformative. How about "Why is no data found after calling SqlDataReader.NextResult?" or similar? — Kenny Evitt
– Kenny Evitt, Commented May 17, 2012 at 14:46
@KennyEvitt What is the original question if you remember? Good call to make it more search friendly. — Mukus
– Mukus, Commented Apr 23, 2014 at 0:28

tvanfosson · Accepted Answer · 2010-02-23 14:28:38Z

17

NextResult() makes the reader advance to the next result set coming back from the query. The way you have it written, it would skip the first result set (likely the only one).

The pattern I think you want is:

if (reader.HasRows)
{
     do
     {
        while (reader.Read())
        {
             ...
        }
     }
     while (reader.NextResult());
}

This will check if there are any results, and if so, read the results in each result set until there are no more left to read.

EDIT: Based on comment:

For JSON, consider using a list of temporary objects, then a DataContractJsonSerializer:

 public class DateClass
 {
      public string AvgDate { get; set; }
      public int MarkerID { get; set; }
 }

 ...

 var dates = new List<DateClass>();
 if (reader.HasRows)
 {
       while (reader.Read())
       {
           var date = new DateClass { AvgDate = reader["AvgDate"].ToString(), MarkerID = (int)reader["MarkerID"] };
            dates.Add( date );
       }
 }

 var stream = new MemoryStream();
 var serializer = new DataContractJsonSerializer( typeof(DateClass) );
 serializer.WriteObject( stream, dates );
 stream.Seek( 0, SeekOrigin.Begin );
 return stream.ToString();

edited Feb 23, 2010 at 14:28

answered Feb 22, 2010 at 22:49

tvanfosson

534k102 gold badges703 silver badges801 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

David Over a year ago

+1. Clarifying, a "resultset" can be thought of as a table. NextResult() is only necessary when your SQL statement returns multiple tables.

cc0 Over a year ago

Ahh, I see. That explains things. However there is no HasResults method for this class. Any suggestion on how to proceed without one? I can't find any useful method actually, but maybe I'm overlooking something

jaywon Over a year ago

@cc0 - Yea the correct function is reader.HasRows() not HasResults(). It tells you if your resultset returned any rows or not. If no rows are returned and you do a reader.Read() you will get an exception, so always have to check for reader.HasRows() before calling the reader.Read() function.

tvanfosson Over a year ago

@jaywon - nice catch. It's been awhile since I've used SqlDataReader and I neglected to look it up when typing my answer. It' also a property, not a method. So much for my memory.

cc0 Over a year ago

Ah, thanks guys, this clarifies things. However what I'm trying to do is avoid that a comma is added at the end, when the final reader entry is printed. I'm still not sure how to accomplish that. Any ideas?

|

Jacob G · Accepted Answer · 2010-02-22 22:49:26Z

4

NextResult takes you to the next Result Set from the Reader. You probably only have one Result Set.

answered Feb 22, 2010 at 22:49

Jacob G

3,6651 gold badge23 silver badges25 bronze badges

Comments

Gerard ONeill · Accepted Answer · 2012-09-13 20:13:41Z

4

the code from tvanfosson's answer above:

if (reader.HasRows) 
{
     do
     {
        while (reader.Read())
        {
         ...
        }
     }
     while (reader.NextResult());
}

should instead be:

do
{
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            ...
        }
    }
} while (reader.NextResult());

There may be result sets without rows that would cause the previous code example to throw an exception. Worse, the first result set may have no rows, and the later result sets would then not be read.

But thanks for letting me know why and how HasRows is used.

answered Sep 13, 2012 at 20:13

Gerard ONeill

4,17043 silver badges25 bronze badges

Collectives™ on Stack Overflow

Why is no data found after calling SqlDataReader.NextResult?

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related