DataTable and DataReader

[dotNetDummi]

Hi all, I want to retrieve some data from the database and return as a datatable.
However, it does not work. I tried both methods that I could find in the Internet, but it's not working. May I know what have I done wrong? The datatable is returned as null {} .

public static DataTable retrieveView2(string pID)
    {
        SqlConnection conn = new SqlConnection(connString);       
             
        SqlCommand comm = new SqlCommand(
            "Select apname, designation, email, watcherEmail, e.status, reason, responsedate "
            + "from aparty ap, estatus e, endpack ep "
            + "where ap.projectid = ep.projectid "
            + "and e.apid = ap.apid "
            + "and ap.ep = 't' "
            + "and e.status !='Pending' "
            + "and ep.status ='Completed' "
            + "and e.projectID = @pID");


        comm.Connection = conn;
        conn.Open();

        comm.Parameters.AddWithValue("@pID", pID);

        SqlDataReader dr = comm.ExecuteReader();
        DataTable dt = new DataTable();
      
        //method 1 
        dt.load(dr);

        //method 2
        //while (dr.Read())
        //{
        //    DataRow row = dt.NewRow();
        //    row["apname"] = dr["apname"].ToString();
              //continue other rows.
        //    dt.Rows.Add(row);
        //}
        
        conn.Close();
        return dt;
    }

By the way, I will prefer to present the data similiar like SQLServer.
Something like that
column/row | apname | designation | email | watcherEmail | status | reason | responsedate
row 1 | name1 | designation1 | email1 | watcherEmail1 | status1 | reason1 | responsedate1
row 2 | name2 | designation2 | email2 | watcherEmail2 | status2 | reason2 | responsedate2

Please help thanks!

[alc6379]

Run the code through a debugger, and set a breakpoint at the line where you make that new SqlCommand. Get the .CommandText value of it, and run that actual text against your SQL server. See if it returns any rows, for starters.

[dotNetDummi]

Yes. It does return a row. which is correct.

[alc6379]

Ok... when you use method 2, does it actually go through the loop, or does it say no rows are returned there?

[dotNetDummi]

Hi , thanks for your reply.
Yes. It went into the loop once, which is correct because I have only one record.

[quangvo]

I find that this is the best way to load it http://www.itjungles.com/dotnet/function-to-load-datatable-from-sqldatareader.