Data type mismatch in criteria expression error when trying to insert into Access database

Question

I am trying to insert values entered by a user on a Winform into the following Access table:
enter image description here

The C# code is:

    OleDbCommand oleCmd = new OleDbCommand("INSERT INTO Projects (projectTitle,partyID,receiveDate,dueDate, projectTypeID, pages, "+
        "lines, words, prepay, cost, settled,projectComment)"
    + " VALUES (@projectTitle,@partyID,@receiveDate,@dueDate,@projectTypeID,@pages, @lines, @words, @prepay, @cost, @settled, @projectComment)", conn);


    PersianCalendar p=new PersianCalendar();
    DateTime thisDate=DateTime.Now;
    oleCmd.Parameters.Add("@projectTitle", OleDbType.VarChar).Value = txtProjectTitle.Text;
    oleCmd.Parameters.Add("@partyID", OleDbType.Numeric).Value = Convert.ToInt32(comboProjectType.SelectedValue.ToString());
    oleCmd.Parameters.AddWithValue("@receiveDate", string.Format( "{0}, {1}/{2}/{3} {4}:{5}:{6}",
        p.GetDayOfWeek(thisDate), p.GetYear(thisDate),p.GetMonth(thisDate),p.GetDayOfMonth(thisDate), p.GetHour(thisDate),p.GetMinute(thisDate),p.GetSecond(thisDate))) ;                        
    oleCmd.Parameters.Add("@dueDate", OleDbType.VarChar).Value = comboDay.Text + "/" + comboMonth.Text + "/" + comboYear.Text;          
    oleCmd.Parameters.Add("@projectTypeID", OleDbType.Numeric).Value = Convert.ToInt32(comboContractParty.SelectedValue.ToString());
    oleCmd.Parameters.AddWithValue("@pages", Convert.ToInt32(txtPages.Text));
    oleCmd.Parameters.AddWithValue("@lines", Convert.ToInt32(txtLines.Text));
    oleCmd.Parameters.AddWithValue("@words", Convert.ToInt32(txtWords.Text));
    oleCmd.Parameters.AddWithValue("@cost", Convert.ToDouble(txtWholeProjCost.Text));
    oleCmd.Parameters.AddWithValue("@prepay", Convert.ToDouble(txtPrePay.Text));
    oleCmd.Parameters.AddWithValue("@settled", chkSettled.CheckState); 
    oleCmd.Parameters.Add("@projectComment", OleDbType.VarChar).Value = txtComment.Text.ToString();
    try
    {
        conn.Open();
        oleCmd.ExecuteNonQuery();
        conn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
        return;
    }

The exception says:

Data type mismatch in criteria expression.

I rechecked the types, but don't know where the error is raised from.

Does your code work if you temporarily replace the current (string) value of the @receiveDate parameter with just thisDate? — Gord Thompson
@GordThompson, it shows this error after using just thisDate: - ************ Exception Text ************** System.FormatException: Input string was not in a correct format. at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt) at System.Convert.ToDouble(String value) — codezombie
@GordThompson the above error was due to null input that seems to be solved by setting default 0 value for the entries. However, I'm still getting the error "Data type mismatch". — codezombie
What happens if you use ... VALUES (@projectTitle,@partyID,Date(),@dueDate, ... and comment out the statement that creates the @receiveDate parameter? — Gord Thompson
what is that? I haven't seen like that before. What is Date() in the parentheses? — codezombie

Ňɏssa Pøngjǣrdenlarp Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2015-02-17T17:10:40

receiveDate and dueDate are defined as DateTime in the Database but you are passing text (string) to the parameter:

oleCmd.Parameters.AddWithValue("@receiveDate", string.Format(... 

oleCmd.Parameters.Add("@dueDate", OleDbType.VarChar).Value = ...

Pass actual DateTime vars and it should work. Since the date(s) are in pieces, I would create them first so they can be tested:

// why not use thisDate rather than chopping it up to recombine?
DateTime recDate = New DateTime(p.Getyear(thisDate)...);

// personally, I might use a DateTimePicker rather than 3 CBOs
DateTime dueDate  = New DateTime(ConvertToInt32(comboYear.Text), ...

Then pass them as params. If you use Add specify OleDbType.Date:

oleCmd.Parameters.AddWithValue("@receiveDate", recDate); 
...
oleCmd.Parameters.AddWithValue("@dueDate", dueDate);

Data type mismatch in criteria expression error when trying to insert into Access database

1 Answers