ASP.NET & SQL Server DateError

Question

This is my code :

protected void Page_Load(object sender, EventArgs e)
{
    DateOfBirth.Text = Session["DateOfBirth"].ToString();
    Member.Text = Session["Member"].ToString();    
}

protected void btn_Confirm_Click(object sender, EventArgs e)
{
    SqlConnection cnn;
    SqlCommand cmd;

    string sql = "INSERT INTO Member (MemberJoinDate,DateOfBirth) VALUES (@MemberJoinDate,@DateOfBirth)";

    cnn = new SqlConnection(SqlDataSource1.ConnectionString);

    try
    {
        cnn.Open();

        cmd = new SqlCommand(sql, cnn);
        cmd.Parameters.Add("@MemberJoinDate", SqlDbType.Date);
        cmd.Parameters["@MemberJoinDate"].Value = Member.Text;
        cmd.Parameters.Add("@DateOfBirth", SqlDbType.Date);
        cmd.Parameters["@DateOfBirth"].Value = MemberID.Text;

        cmd.ExecuteNonQuery();

        cmd.Dispose();
        cnn.Close();

        btn_Confirm.Visible = false;
    }
    catch (Exception ex)
    {
        Response.Write(ex);
    }

After inputting this :

 Name : Random 
 Members Join Date : 08/23/2015 
 Date of birth : 08/23/2015

I got this Error :

System.FormatException: Failed to convert parameter value from a String to a DateTime.

System.FormatException: String was not recognized as a valid DateTime.

at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
at System.Convert.ToDateTime(String value, IFormatProvider provider)
at System.String.System.IConvertible.ToDateTime(IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
at System.Data.SqlClient.SqlParameter.GetCoercedValue()
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Summary.btn_Confirm_Click(Object sender, EventArgs e)

You need to convert your textual input Member.Text to a .NET DateTime object first, then set it as value of the parameter. — marc_s
Is 08/23/2015 in MM/DD/YYYY format? That may not be the format that the current culture is using. Try 23/08/2015. — StingyJack
Is MemberID really the birth date? I suspect you are trying to use the value from the wrong control. It should be from your date of birth control, not the MemberID. — Sean Lange
MemberID is a typo .. Sorry . I changed back to dateofbirth.text. Same result — MongMien Ren

mason mason · Accepted Answer · 2015-08-24T13:06:45

The error message is clear. In order to convert a string to DateTime automatically, it must be in a certain format. You don't have it in that format, so it wasn't converted. You should validate the dates to make sure they're in the correct format, then parse them to a DateTime object, then supply that as a parameter to the command. I'll leave the validation part for you, but here's the parsing code:

DateTime MemberJoinDate = DateTime.Parse(Member.Text, "MM/dd/yyyy", System.Globalization.CultureInfo.CurrentCulture);
cmd.Parameters.Add("@MemberJoinDate", MemberJoinDate);

You may wish to make use of another variant of DateTime parsing if you want to test whether the supplied text was in the right format. Take a look at DateTime.ParseExact.

Additionally, you're wasting resources by opening the database connection and then adding the parameters. And you need to make absolutely sure the database connection gets closed, even in the event of an error. Here's how I'd write it:

var cnn = new SqlConnection(SqlDataSource1.ConnectionString);
var cmd = new SqlCommand("INSERT INTO Member (MemberJoinDate,DateOfBirth) VALUES (@MemberJoinDate,@DateOfBirth)", cnn);

cmd.Parameters.Add("@MemberJoinDate", DateTime.Parse(Member.Text, "MM/dd/yyyy", System.Globalization.CultureInfo.CurrentCulture));
cmd.Parameters.Add("@DateOfBirth", DateTime.Parse(DateOfBirth.Text, "MM/dd/yyyy", System.Globalization.CultureInfo.CurrentCulture));

try
{
    cnn.Open();             
    cmd.ExecuteNonQuery();               
}
catch (Exception ex)
{

    //Response.Write(ex); //probably shouldn't write directly to the response
    ClientScriptManager.RegisterStartupScript(this.GetType(), "InsertionError", "alert('Something went wrong while saving the data!')", true);
}
finally // this code will always get executed, even if there's a problem in the try block
{
    cmd.Dispose();
    cnn.Close();
}
btn_Confirm.Visible = false;

ASP.NET & SQL Server DateError

2 Answers