Enumeration yielded no results in c#

Ritu

I have one simple query

I want to retrieve all distinct PO's from table but when I execute a query it give recordsaffected -1

below is my query

public static string GetPO()
{
    List lst = new List();
    
    SqlConnection connDB = new SqlConnection(constr);
    try
    {
        connDB.Open();
        SqlDataReader reader = null;
        
        string selectcmd = " SELECT distinct PO as PurchaseOrder FROM ABC";
        SqlCommand cmd = new SqlCommand(selectcmd, connDB);
        cmd.CommandType = CommandType.Text;
        reader = cmd.ExecuteReader(); 
            while (reader.Read())
            {
                 lst.Add(reader.ToString());

            }
            reader.Close();
    }

    catch (Exception e)
    {
        Console.WriteLine(e.Message.ToString());
    }
    finally
    {
        if (connDB.State != ConnectionState.Closed)
            connDB.Close();
    }
    return lst.ToString();
}

NO records in reader, please help. thanks in advance

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted answer
you need to read the column value from reader object
public static List GetPO()
{
    List lst = new List();


    try
    {
   using( SqlConnection connDB = new SqlConnection(constr);)
{
  string selectcmd = " SELECT distinct PO as PurchaseOrder FROM ABC";
        SqlCommand cmd = new SqlCommand(selectcmd, connDB);
  connDB.Open();
SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                 lst.Add(reader[0].ToString());

            }
  return lst;
}
   }

    catch (Exception e)
    {
        Console.WriteLine(e.Message.ToString());
    }
return null;

}
0
Nitin Sontakke4y ago
I see some obvious errors in your code. Please review the following:

public static List GetPO()
{
List lst = new List();

SqlConnection connDB = new SqlConnection(constr);
try
{
connDB.Open();

string selectcmd = " SELECT distinct PO as PurchaseOrder FROM ABC";
SqlCommand cmd = new SqlCommand(selectcmd, connDB);
cmd.CommandType = CommandType.Text;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
lst.Add(reader.GetValue(0).ToString());
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message.ToString());
}
finally
{
if (connDB.State != ConnectionState.Closed)
connDB.Close();
}
return lst;
}
Also, are you 100% sure that your table has data?
0