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.

If the parameter expects a DateTime then pass a DateTime to its value, not a string, you should convert your user input in a valid datetime — Steve, Aug 08 '15 at 10:05

Soner Gönül · Accepted Answer · 2015-08-08T11:11:19.883

5

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);

edited Aug 08 '15 at 11:11

answered Aug 08 '15 at 10:05

Soner Gönül

97,193
102
206
364

I tried what you asked but unfortunately, it did not work. I also tried the following - I converted the String with the user input into DateTime and put it into - cmd.Parameters.Add("@ebirth", OleDbType.DBDate).Value = dt; where dt is the converted String. Also, should I be using DateTime.ParseExact if the date format in the text field is - mm/dd/yyyy? There's a calendar extender attached to that particular text field. – Raj Saha Aug 08 '15 at 11:06
@RajSaha You should provide your parameter values with the same order that you defined in your command. Updated my answer. Take a look. – Soner Gönül Aug 08 '15 at 11:11
@RajSaha Also _that_ calendar should have some `SelectedValue` or `Value` properties or something. You can use them either. – Soner Gönül Aug 08 '15 at 11:13
You're the best! Thank you so much. That worked. I had no idea the order was so important. Thank you again! – Raj Saha Aug 08 '15 at 11:20

score 0 · Answer 2 · answered Aug 08 '15 at 10:09

0

You cant pass string to Date/Time. You need to convert date to DateTime:

DateTime value = new DateTime(year, month, day);

Other way is to use DateTimePicker instead of TextBox. Be aware, because from what i heard OleDB cannot handle milliseconds.

answered Aug 08 '15 at 10:09

titol

999
13
25

Failed to add Date to Access database

2 Answers2