Passing a parameter value to SQL stored proc of an optional string in asp.net

Question

I have a search page, which has Date text field. The user may or may not populate this field. But the field is a parameter of a SQL stored proc that gets called to execute the search query.

When I walk through the code (and the Date field is blank), I get an error that the value could not be converted to DateTime

How can I convert a null value to DateTime, since the parameter is expected by the query?

cmdSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));
cmdSearch.Parameters["@StartDate"].Value = Convert.ToDateTime(txtStartDate.Text.Trim());

Answer 1

cmdSearch.Parameters["@StartDate"].Value = 
    String.IsNullOrEmpty(txtStartDate.Text) ? SqlDateTime.MinValue : 
        Convert.ToDateTime(txtStartDate.Text.Trim()); 

That will check to see if the Text is null or an empty string before trying to parse the value.

Be sure to use the SqlDateTime structure instead of DateTime since the two have vastly different MinValues.

Keep in mind that this will not fix cases where the TextBox has text that is not in proper date format. You will keep getting Exceptions in those cases!

Answer 2

Wrap an if statement around the whole thing and test to see if String.IsNullOrEmpty(txtStartDate.Text)

EDIT

This is what I meant by, "whole thing" - apologies for not being clearer

if (!String.IsNullOrEmpty(txtStartDate.Text))
    cmdSearch.Parameters["@StartDate"].Value = Convert.ToDateTime(txtStartDate.Text.Trim());
else
    cmdSearch.Parameters["@StartDate"].Value = DateTime.MinValue;

Answer 3

If you set the parameter in the SQL proc as an optional parameter, by supplying a default value

CREATE PROCEDURE MyProc
...
@StartDate datetime = null,
....

them you can just omit sending that parameter from the c# code

DateTime result;
if (DateTime.TryParse(txtStartDate.Text, out result))
{
   cmdSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));
   cmdSearch.Parameters["@StartDate"].Value = result;
}

If the parsing of the text field is unsuccessful, the parameter is not added in the parameter list, and the database uses the default value.

Also, using DateTime.TryParse(..) prevents dealing with exceptions.