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

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.

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;