Failed to add Date to Access database

Question

Having a bit of trouble adding birthday data to an Access database. I have tried running the code without 'birthday' getting in the way and it works then. As per my research, 'DBDate' also takes in milliseconds into account and I've tried saving the input from the text-field into DateTime format, but alas, that too did not work.

Here's the code that I'm working with.

public partial class Records : System.Web.UI.Page
{
    OleDbConnection con;
    OleDbCommand cmd;

    protected void Page_Load(object sender, EventArgs e)
    {
        // Trace.Warn("2310", "Beginning of life Page_Load");
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString=@"Provider=Microsoft.ACE.OLEDB.12.0;
            Data Source=E:\Dropbox\Temporary\OOD Assignment\Employee Directory\AppData\Employee.accdb";

        String empName;
        String ebirth;
        String job;
        String location;
        String pnumber;
        String email;

        OleDbCommand cmd = new OleDbCommand("INSERT into EmployeeRecords ([ename],[job],[location],[phonenumber],[email],[birth])Values(@empName,@job,@location,@pnumber,@email,@ebirth)");
    cmd.Connection = conn;

        conn.Open();

        if(conn.State == ConnectionState.Open)
        {
            cmd.Parameters.Add("@empName", OleDbType.VarChar).Value = tbEName.Text;
            cmd.Parameters.Add("@ebirth", OleDbType.DBDate).Value = tbBirthDate.Text;
            cmd.Parameters.Add("@job", OleDbType.VarChar).Value = tbJobTitle.Text;
            cmd.Parameters.Add("@location", OleDbType.VarChar).Value = tbOfficeLocation.Text;
            cmd.Parameters.Add("@pnumber", OleDbType.Numeric).Value = tbPNumber.Text;
            cmd.Parameters.Add("@email", OleDbType.VarChar).Value = tbEmail.Text;

            try
            {
                cmd.ExecuteNonQuery();
                Label1.Text = "Data Added";
                conn.Close();
            }
            catch(OleDbException ex)
            {
                Label1.Text = "Exception" + ex;
                conn.Close();
            }
        } else
        {
            Label1.Text = "Connection Failed!";
        }
    }
}

Here's the error that I get:

ExceptionSystem.Data.OleDb.OleDbException (0x80040E07): Data type mismatch in criteria expression. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at Records.btnSubmit_Click(Object sender, EventArgs e) in e:\Dropbox\Temporary\OOD Assignment\Employee Directory\Records.aspx.cs:line 54

The field type of "birth" in the Employee.accdb file is Date/Time.

Is there any way to convert the user input from the text field into a format that can be understood by Access? Any help would be much appreciated.

Answer 1

DBDate mapped with DateTime in CLR side.

That means, you need parse your tbBirthDate.Text first to DateTime.

cmd.Parameters.Add("@ebirth", OleDbType.DBDate).Value = DateTime.Parse(tbBirthDate.Text);

If your textbox value is not a standard date and time format for your CurrentCulture, you can parse it with DateTime.ParseExact method with exact format of your string.

Also use using statement to dispose your connections and commands automatically instead of calling Close or Dispose methods manually.

By the way, OleDbCommand doesn't support named parameters. Actually, it supports but it doesn't just care. Only care about their parameter values.

OleDbCommand cmd = new OleDbCommand(@"INSERT into EmployeeRecords ([ename],[job],[location],[phonenumber],[email],[birth])
                                      Values(@empName,@job,@location,@pnumber,@email,@ebirth)");
....
cmd.Parameters.Add("@empName", OleDbType.VarChar).Value = tbEName.Text;
cmd.Parameters.Add("@job", OleDbType.VarChar).Value = tbJobTitle.Text;
cmd.Parameters.Add("@location", OleDbType.VarChar).Value = tbOfficeLocation.Text;
cmd.Parameters.Add("@pnumber", OleDbType.Numeric).Value = tbPNumber.Text;
cmd.Parameters.Add("@email", OleDbType.VarChar).Value = tbEmail.Text;
cmd.Parameters.Add("@ebirth", OleDbType.DBDate).Value = DateTime.Parse(tbBirthDate.Text);