asp.net error: "Conversion failed when converting character string to smalldatetime data type"

Question

I am getting an error when attempting to call a stored proc from my asp.net page.

e.Message = "Conversion failed when converting character string to smalldatetime data type."

The stored proc is being called by:

The asp.net code that is calling the stored proc is:

//Display search results in GridView; 
SqlConnection con = new SqlConnection(strConn); 
//string sqlItemSearch = "usp_Item_Search"; 
SqlCommand cmdItemSearch = new SqlCommand(sqlItemSearch, con); 
cmdItemSearch.CommandType = CommandType.StoredProcedure; 

cmdItemSearch.Parameters.Add(new SqlParameter("@Item_Num", SqlDbType.VarChar, 30)); 
cmdItemSearch.Parameters["@Item_Num"].Value = txtItemNumber.Text.Trim(); 

cmdItemSearch.Parameters.Add(new SqlParameter("@Search_Type", SqlDbType.Int)); 
cmdItemSearch.Parameters["@Search_Type"].Value = ddlSearchType.SelectedItem.Value; 

cmdItemSearch.Parameters.Add(new SqlParameter("@Vendor_Num", SqlDbType.VarChar, 10)); 
cmdItemSearch.Parameters["@Vendor_Num"].Value = txtVendorNumber.Text.Trim(); 

cmdItemSearch.Parameters.Add(new SqlParameter("@Search_User_ID", SqlDbType.Int)); 
cmdItemSearch.Parameters["@Search_User_ID"].Value = ddlSeachUser.SelectedItem.Value; 

if (!string.IsNullOrEmpty(txtStartDate.Text)) 
{ 
    cmdItemSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime)); 
    cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime(txtStartDate.Text.Trim()); 
} 
else 
{ 
    cmdItemSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime)); 
    cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime("01/01/1996"); 
} 

if (!string.IsNullOrEmpty(txtEndDate.Text)) 
{ 
    cmdItemSearch.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime)); 
    cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(txtEndDate.Text.Trim()); 
} 
else 
{ 
    cmdItemSearch.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime)); 
    cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(DateTime.Now); 
} 
con.Open(); 

SqlDataAdapter ada = new SqlDataAdapter(cmdItemSearch); 
DataSet ds = new DataSet(); 
ada.Fill(ds); 

    gvSearchDetailResults.DataSource = ds; 
    gvSearchDetailResults.DataBind(); 
    pnlSearchResults.Visible = true; 

Any ideas what would be causing the error?

Answer 1

My guess would be the lines of code here:

cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime(txtStartDate.Text.Trim());

And here:

cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(txtEndDate.Text.Trim());

Check the values that are being put into these two Parameters and verify that they're valid dates that you're trying to convert.

Convert.ToDateTime(somedatehere) will return the default DateTime.MinValue if it receives a null value, but an improperly formatted value will throw an exception.

Answer 2

Which SQL parameter is supposed to be a smalldatetime? I don't see any being defined as such.

Answer 3

Assuming the parameters are either datetime/smalldatetime (although you've mentioned datetime) and not varchar, then one of the values is < 01 Jan 1900 or > 06 Jun 2079 based on the error message.

Answer 4

try this please. For examle your textBox1.Text value equals 20100101

DateTime newDateTime = new DateTime(Convert.ToInt32(textBox1.Text.Substring(0,4)),// Year
                                Convert.ToInt32(textBox1.Text.Substring(4,2)),// Month
                                Convert.ToInt32(textBox1.Text.Substring(6,2)))// Day

You cant make a newDateTime from TextBox like your code. you must write a format after your text like "yyyymmdd". But my code is absolute solution.

Answer 5

The problem is caused by one of these lines:

cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(txtEndDate.Text.Trim());

or

cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime(txtStartDate.Text.Trim());

or

cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime("01/01/1996");

It may be caused by incorrectly entered values or locale problems. If you do not need to output validation messages, then you can use DateTime.TryParse() static function to parse the entered date and time.

I would do it this way:

DateTime date;
cmdItemSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime)); // note this line was unnecessary duplicated 
if (!string.IsNullOrEmpty(txtStartDate.Text) && DateTime.TryParse(txtStartDate.Text.Trim(), out date)) 
    cmdItemSearch.Parameters["@StartDate"].Value = date; 
else 
    cmdItemSearch.Parameters["@StartDate"].Value = DateTime.MinValue;