Omi Omi - 2 months ago 14
SQL Question

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value?

I am trying to insert date in my database table through a textbox. But even if I am converting the string into Datetime I am still getting this error :


"The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value".


I have taken datetime datatype in my database. This is my code :

try

{
SqlCommand cmd = new SqlCommand(@"INSERT INTO tblProject(project_starting_date,project_ending_date)values(@projectstartingdate,@projectendendingdate)", objconn);
// cmd.Parameters.AddWithValue("@projectstartingdate", DateTime.Parse(txtStartingdate.Text).ToString());
//cmd.Parameters.AddWithValue("@projectendendingdate", DateTime.Parse(txtProjectendingdate.Text).ToString());
DateTime stdate;
if(DateTime.TryParse(txtStartingdate.Text, out stdate))
{
//cmd.Parameters.AddWithValue("@projectstartingdate",stdate);
SqlParameter projstrtdate = new SqlParameter("@projectstartingdate", SqlDbType.DateTime);
projstrtdate.Value = stdate;
cmd.Parameters.Add(projstrtdate);
}
DateTime enddate;
if (DateTime.TryParse(txtProjectendingdate.Text, out enddate))
{
// cmd.Parameters.AddWithValue("@projectendendingdate", enddate);
SqlParameter projenddate = new SqlParameter("@projectendendingdate", SqlDbType.DateTime);
projenddate.Value = enddate;
cmd.Parameters.Add(projenddate);

}
if (objconn.State == ConnectionState.Closed)
{

objconn.Open();
}


norowaffected = cmd.ExecuteNonQuery();
objconn.Close();
}
catch (Exception ex)
{
Response.Write( ex.ToString());
}
}


Please guide me where I am doing wrong?

Answer

Probably your locale configuration is trying to convert the data string in a wrong unexpected format.

Try with the following:

if(DateTime.TryParse(txtStartingdate.Text, out stdate)
{
    SqlParameter projectStartingDateParam = new SqlParameter("@projectstartingdate", SqlDbType.DateTime);
        projectStartingDateParam.Value = stdate;
    cmd.Parameters.Add(projectStartingDateParam);
}

Do the same with "projectendingdate". Create a SqlParameter with SqlDbType equals to SqlDbType.DateTime and add it to the query command (cmd variable).

If this dosen't work, double check your table structure if it's in DateTime format. Do a manually insert directly in database via SQL Server Management Studio.