DataTable is always returns empty values

Question

I'm .NET newbie and I have to write function which executes stored procedure and return DataTable.

my SqlConnection is already initialized and while running in debugger I can see DataAdapter returns values, but my DataTable is empty.

 public DataTable ExecStoredProcedure(String spName, String uId)
        {
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(spName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UnitIDList", uId);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.StackTrace + ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return dt;
        }

Please help.

Answer 1

I can't see anything wrong with this code and I've run it myself using my own sproc. Seems fine.

When you're stepping thru your code in the debugger and you get to the line:

da.Fill(dt);

let it run then before the next line is executed check the value of dt.Rows.Count in the Watch or Immediate windows. What is the number reported?

If dt.Rows.Count > 0 then you need to check the code in which you're doing something with the DataTable you get from your method - that's where the issue is.

If dt.Rows.Count ==0 then its not returning any data from the Stored proc - (this doesn't seem to be the problem you have) and you'll need to look at the SQL inside your sproc. Have a look and post what you find ...

Answer 2

I had the same problem with the SqlDataAdapter returning an empty DataTable as a a result, but in my case the reason was that I had two of the parameters ("CheckDate1" and "CheckDate2", the stored procedure is not mine btw.) declared as being of type SqlDbType.DateTime. However, someone very clever defined them as VARCHAR(10) which I overlooked at the beginning.

Unfortunetly this didn't result in an error message but only in an empty result set being returned. As soon as I changed the parameter type I got my expected result.

Hope this might be helpful for someone else.

G.