SqlDataReader Reader.Read() shows Enumeration yielded no results

Question

I am Trying to generate random Ids from a given table. I can see the random number generated in debug but when I reach to reader.Read() line it shows Enumeration yielded no results.

I couldn't quite get what I am missing.

  private static void GetRandomId(int maxValue)
    {
        string connectionString =
        "Data Source=local;Initial Catalog=Test;user id=Test;password=Test123;";

        string queryString = @"SELECT TOP 1 Id from Pointer WHERE Id > (RAND()  * @max);";


        using (var connection = new SqlConnection(connectionString))
        {
        var command = new SqlCommand(queryString, connection);
        command.Parameters.AddWithValue("@max", maxValue);

        connection.Open();

        using (var reader = command.ExecuteReader()) <--  // Here I can see the randon value generated
        {
            while (reader.Read())
            {

           //Here reader shows : Enumeration yielded no results
            Console.WriteLine("Value", reader[1]);

            reader.Close();
            }

        }

        }

    }

Why would any Id be larger than the product of maxValue and RAND() ? — Alex
– Alex, Commented May 4, 2015 at 23:41
Also, reader[int] is zero-based. So your data (if you get any) will be in reader[0]. Btw if you get to the Console.WriteLine line, that means that there is at least 1 row of results. And finally, calling reader.Close() is not necessary (and would in fact break it if there was more than 1 line returned). The using takes care of that. Maybe you wanted to use break;? — vesan
– vesan, Commented May 4, 2015 at 23:54
you will have to clamp the value returned by rand not multiply it — RadioSpace
– RadioSpace, Commented May 5, 2015 at 0:02

Community · Accepted Answer · 2017-05-23 12:08:16Z

5

Since you are basically searching for a random Id of an existing record, I believe this may cover what you are trying to do:

Random record from a database table (T-SQL)

SELECT TOP 1 Id FROM Pointer ORDER BY NEWID()

Use SqlCommand.ExecuteScalar Method instead

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar%28v=vs.110%29.aspx

var dbRandomId = command.ExecuteScalar();
var randomId = Convert.IsDbNull(dbRandomId) ? (int?)null : (int)dbRandomId;
// you now also know if an id was returned with randomId.HasValue

https://msdn.microsoft.com/en-us/library/system.convert.isdbnull%28v=vs.110%29.aspx

Issues with your example:

Issue 1: Couldn't you have @max be computed with a SELECT @max = MAX(Id) FROM Pointer? No need to pass it in a parameter. Or am I missing the point? Is that a deliberate limit?

Issue 2: Shouldn't it be reader[0] or reader["Id"]? I believe columns are zero based and your selected column's name is "Id".

Issue 3: Be careful not to enumerate somehow the reader via the Debugger because you'll actually consume (some of?) the results right there (I'm guessing you are doing this by your comment "// Here I can _see_ the random value generated") and by the time the reader.Read() is encountered there will be no results left since the reader has already been enumerated and it won't "rewind".

https://msdn.microsoft.com/en-us/library/aa326283%28v=vs.71%29.aspx

DataReader cursor rewind

Issue 4: Why do you close the reader manually when you've already ensured the closing & disposal with using? You also already know it's going to be one record returned (the most) with the TOP 1.

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered May 5, 2015 at 0:01

Demetris Leptos

1,68012 silver badges12 bronze badges

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

2 Comments

HXD Over a year ago

thank you. using Use SqlCommand.ExecuteScalar Method instead helped me fix the issue.

Demetris Leptos Over a year ago

Great! You are welcome. For academic reasons, you should be able to get what you wanted with your example so the question remains as to what was truly going wrong over there. Nevertheless, ExecuteScalar suits better in this case since it is specifically designed for that and there is no need getting into handling a data reader yourself.

jason · Accepted Answer · 2017-11-09 13:09:12Z

0

If you check the results of the sqlDataReader in the debugger, the results are then gone, and wont be found by the Read() event

answered Nov 9, 2017 at 13:09

jason

911 silver badge4 bronze badges

Comments

Ndifreke James Okpo · Accepted Answer · 2025-11-22 13:08:57Z

0

So I had this same problem where the data would show up and then while trying to read it would throw this error. The funny thing is the while function runs fine and this shows up in the debugger when it tries to go to the next item (which is unavailable) then it exits the loop. The underlying reason was the fact that I was reading the data from the reader into an uninitialized (aka null) object leading to the error.

This error only shows up in the debugger after the while function has run and there's usually a more pressing issue such as "Object reference not set to an instance of an object" that will lead you to the debugger and then to this. Solve that one first and then your problems go away.

I hope this helps someone.

edited 4 hours ago

answered 13 hours ago

Ndifreke James Okpo

12710 bronze badges

Collectives™ on Stack Overflow

SqlDataReader Reader.Read() shows Enumeration yielded no results

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related