CODEWITHSUNDEEP
c#sqlsql-server
Michael Mahony
Michael Mahony

Reputation: 1350

Why is DataReader giving "Enumeration Yielded No Results"?

I am using a very simple stored procedure that has 2 parameters. It then fills up a DataReader, but when I run the code, the DataReader throws an error saying "Enumeration yielded no results" despite making it past the reader.Read() line.

My function is passed email as a parameter. It then establishes the connection:

SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["IdesignTriviaConnection"].ConnectionString);
            SqlCommand cmd = new SqlCommand("usp_GetCurrentLink", con);
            SqlDataReader reader;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@date", SqlDbType.DateTime, 100).Value = DateTime.Today;
            cmd.Parameters.Add("@email", SqlDbType.VarChar, 100).Value = email;

After this it opens the connection and establishes the reader:

try
            {
                if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection.Open();
                }
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    long LinkID = reader.GetInt32(0);
                    String URL = reader.GetString(1);
                    long Completed = reader.GetInt32(2);

if (LinkID == 0)
                    {
                        link = "0";
                    }
                    else
                    {
                        if (Completed == 1)
                        {
                            link = "1";
                        }
                        else
                        {
                            link = URL;
                        }
                    }
                }

As stated, the stored procedure works perfectly in query analyzer and I've even used the debugger to see that it is actually returning the expected values of:

So where am I going wrong here? Why, if the datareader is failing to enumerate any data, is it making it past the reader.Read() line? Why am I am I able to see that data in the debugger if I so choose?

Upvotes: 7

Views: 25347

Answers (2)

Jenna Leaf
Jenna Leaf

Reputation: 2452

I got that error from spelling the columnName wrong. I changed from

string stn_nm = _rdr["STN_NAME"].ToString();

to

string stn_nm = _rdr["STATION_NAME"].ToString(); //correct

Then, no more that error!

So, the easiest thing to check first is, all the spellings for your columns. Good luck!

Upvotes: 0

Michael Mahony
Michael Mahony

Reputation: 1350

So, the answer was to remove the length from the date parameter. That's what I get for copying and pasting and not noticing that issue. Even though it was returning the data, apparently the datareader thought there was no data. Thanks to those who suggested that fix.

Upvotes: 4

Related Questions