Passing date from calendar into stored procedure but getting date as NULL

Question

I am passing fromdate and todate from ASP.NET code to SQL Server, but in the procedure, I am getting null value for both dates.

ASP.NET code:

using (SqlCommand cmd = new SqlCommand("PRRNASTIEDOWNLOAD_Client", sqlConn))
{
    cmd.Parameters.AddWithValue("@FROMDATE", System.DateTime.Now.ToString("yyyy-mm-dd", System.Globalization.CultureInfo.InvariantCulture));
    cmd.Parameters.AddWithValue("@TODATE", System.DateTime.Now.ToString("yyyy-mm-dd", System.Globalization.CultureInfo.InvariantCulture));

    if (sqlConn.State != ConnectionState.Open)
    {
        sqlConn.Open();
    }

    SqlDataReader rdr = cmd.ExecuteReader();
    dt.Load(rdr);
}

But I am getting date value in procedure as null

Please **DO NOT** convert your dates to **string** just to pass them into the stored procedure!!! Define the parameters as `DATE` or `DATETIME` in the stored procedure interface, and then pass them **as `DATETIME`** from C# ! — marc_s, Dec 24 '18 at 07:09
Unrelated tips: SqlDataReader is IDisposable so should be in a `using` block. You may want to read [can we stop using AddWithValue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). — Richardissimo, Dec 24 '18 at 07:36
Hi, I see you're new to SO. If you feel an answer solved the problem, please mark it as 'accepted' by clicking the gray check mark beside the answer. Check this link to know How does accepting an answer work: https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work — Salah Akbari, Jan 02 '19 at 06:51

Salah Akbari · Answer 1 · 2018-12-24T10:12:14.470

You need to specify the type of your command like this:

cmd.CommandType = CommandType.StoredProcedure;

You need to specify that you are calling a stored procedure and not a simple command text. See also: When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?

You also need to use Add instead of AddWithValue to be precise on the type of the parameter passed to the SP, something like this:

cmd.Parameters.Add("@FROMDATE", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;

The following article could be also interesting:

Can we stop using AddWithValue() already?

Passing date from calendar into stored procedure but getting date as NULL

1 Answers1