1

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();
            }

        }

        }

    }
HXD
  • 506
  • 1
  • 7
  • 26
  • 1
    Why would any `Id` be larger than the product of `maxValue` and `RAND()` ? – Alex May 04 '15 at 23:41
  • 1
    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 May 04 '15 at 23:54
  • you will have to clamp the value returned by rand not multiply it – RadioSpace May 05 '15 at 00:02

2 Answers2

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.

Community
  • 1
  • 1
Demetris Leptos
  • 1,560
  • 11
  • 12
  • 1
    thank you. using Use SqlCommand.ExecuteScalar Method instead helped me fix the issue. – HXD May 05 '15 at 14:02
  • 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. – Demetris Leptos May 07 '15 at 21:15
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

jason
  • 91
  • 1
  • 4