Enumeration yielded no results in c#

Question

Hi

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<string> lst = new List<string>();
    
    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

Sachin Singh · 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; }

Nitin Sontakke · Answer

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?