DataTable Is Empty

Question

I am attempting to run a stored procedure and add the results to a data table. My stored procedure executes as it should, bc if I query the table the results are stored in from SSMS - the accurate results are there. However, my code below will produce numberofrecords = 0 everytime!

What did I set-up incorrectly in this syntax below?

using (conn = new SqlConnection(SQLConn))
{
using (cmd = new SqlCommand(storedprocname, conn))
{
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100);
  cmd.Parameters.Add("d1", SqlDbType.Date, 100);
  cmd.Parameters.Add("d2", SqlDbType.Date, 100);
  cmd.Parameters["@Name"].Value = cboNames.Text.ToString();
  cmd.Parameters["d1"].Value = dtpd1.Value.ToString("MM/dd/yyyy");
  cmd.Parameters["d2"].Value = dtpd2.Value.ToString("MM/dd/yyyy");
  cmd.Parameters.Add("@Dolla", SqlDbType.VarChar, 100);
  cmd.Parameters["@Dolla"].Value = cboDolla.Text.ToString();

  using (da = new SqlDataAdapter(cmd))
  {
    da.Fill(dt);
  }

  int numberOfRecords = 0;
  numberOfRecords = dt.Select().Length;
  MessageBox.Show(numberOfRecords.ToString());
}

And these are my class variable declarations:

    public static SqlCommand cmd;
    public static SqlDataAdapter da;
    public static DataSet ds = new DataSet();
    public static DataTable dt = new DataTable();

EDIT
And this is my stored proc which produces roughly 32 rows

ALTER Procedure [dbo].[TestParamQuery]
(
  @Name varchar(max)
  ,@d1 varchar(100)
  ,@d2 varchar(100)
  ,@dolla varchar(500)
)
As

Select 
EmployeeName
,EmployeeNumber
,CAST(hiredate As Date) [hire date]
,saleamount
FROM [TestDB].[dbo].[SalesFigs]
WHERE employeename = @Name
AND hiredate between @d1 AND @d2
AND saleamount >= @dolla

EDIT 2
This is how I execute the stored procedure to ensure it is returning the results I want from directly inside SSMS

USE [TestDB]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[TestParamQuery]
        @Name = N'Sam Wise',
        @d1 = N'03/01/2016',
        @d2 = N'01/30/2016',
        @Dolla = N'1000'

SELECT  'Return Value' = @return_value

GO

Answer 1

Not sure why you cannot get values. But anyway please try this approach:

using (conn = new SqlConnection(SQLConn))
using (cmd = new SqlCommand(storedprocname, conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Name", cboNames.Text);
    cmd.Parameters.AddWithValue("d1", dtpd1.Value.ToShortDateString();
    cmd.Parameters.AddWithValue("d2", dtpd2.Value.ToShortDateString();
    cmd.Parameters.AddWithValue("@Dolla", cboDolla.Text);

    using (da = new SqlDataAdapter(cmd))
    {
        da.Fill(dt);
    }

    var numberOfRecords = dt.Rows.Count;
    MessageBox.Show(numberOfRecords);
}

Answer 2

Unfortunately, SqlCommand does not have an easy method or property for converting the command to a string, with all parameters and values included. I have used a method like this in the past to make debugging commands easier:

public static string PrintCommand(this SqlCommand command){
    if (command == null) throw new ArgumentNullException("command");

    var sb = new StringBuilder();
    sb.AppendLine(command.CommandText); 
    foreach (var p in command.Parameters){
        sb.AppendLine("\t" + p.ParameterName + ": " + p.Value); 
    }
    return sb.ToString();
}

It should output a string like this:

"dbo.MyCommandName
    @Name: myNameParameterValue
    d1: 01/01/2016
    d2: 02/02/2016
    @Dolla: myDollaValue"

You can then invoke it like this, and check the value in a step-thru debugger.

using (conn = new SqlConnection(SQLConn))
using (cmd = new SqlCommand(storedprocname, conn)) {
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100);
  cmd.Parameters.Add("d1", SqlDbType.Date, 100);
  cmd.Parameters.Add("d2", SqlDbType.Date, 100);
  cmd.Parameters["@Name"].Value = cboNames.Text.ToString();
  cmd.Parameters["d1"].Value = dtpd1.Value.ToString("MM/dd/yyyy");
  cmd.Parameters["d2"].Value = dtpd2.Value.ToString("MM/dd/yyyy");
  cmd.Parameters.Add("@Dolla", SqlDbType.VarChar, 100);
  cmd.Parameters["@Dolla"].Value = cboDolla.Text.ToString();

  //Get a text representation here:
  var text = cmd.PrintCommand();

  //Put a breakpoint here to check the value:
  using (da = new SqlDataAdapter(cmd))
  {
    da.Fill(dt);
  }

  int numberOfRecords = 0;
  numberOfRecords = dt.Select().Length;
  MessageBox.Show(numberOfRecords.ToString());
}