0

I'm struggling threw an exception while trying to update the database. The function goes like this:

internal static string SaveTripleMail(TripleMail mailSett, OleDbConnection cnn)
    {
        string response;
        StringBuilder sb = new StringBuilder();
        string PRM_MAIL_TO_1 = "@mailTo1";
        string PRM_MAIL_TO_2 = "@mailTo2";
        string PRM_MAIL_TO_3 = "@mailTo3";
        string PRM_SEND_DAILY = "@sendDaily";
        string PRM_SEND_MONTHLY = "@sendMonthly";
        string PRM_SEND_TIME = "@sendDailyTime";
        string PRM_MONTHLY_TIME = "@sendMonthlyTime";
        try
        {
            //Reopen connection to database
            ReopenConnection(cnn);
            sb.Append("UPDATE " + AccessDbConstants.TABLE_MAIL + " ");
            sb.Append("SET " + AccessDbConstants.SEND_DAILY + " = " + PRM_SEND_DAILY + ", ");
            sb.Append(AccessDbConstants.SEND_DAILY_TIME + " = " + PRM_SEND_TIME + ", ");
            sb.Append(AccessDbConstants.SEND_MONTHLY + " = " + PRM_SEND_MONTHLY + ", ");
            sb.Append(AccessDbConstants.SEND_MONTHLY_TIME + " = " + PRM_MONTHLY_TIME + ", ");
            sb.Append(AccessDbConstants.MAIL_TO_1 + " = " + PRM_MAIL_TO_1 + ", ");
            sb.Append(AccessDbConstants.MAIL_TO_2 + " = " + PRM_MAIL_TO_2 + ", ");
            sb.Append(AccessDbConstants.MAIL_TO_3 + " = " + PRM_MAIL_TO_3 + " ");

            cmdText = sb.ToString();
            cmd = new OleDbCommand(cmdText, cnn);

            cmd.Parameters.Add(new OleDbParameter(PRM_MAIL_TO_1, mailSett.mailTo1));
            cmd.Parameters.Add(new OleDbParameter(PRM_MAIL_TO_2, mailSett.mailTo2));
            cmd.Parameters.Add(new OleDbParameter(PRM_MAIL_TO_3, mailSett.mailTo3));
            cmd.Parameters.Add(new OleDbParameter(PRM_MONTHLY_TIME, Convert.ToDateTime(mailSett.sendMonthly)));
            cmd.Parameters.Add(new OleDbParameter(PRM_SEND_DAILY, Convert.ToBoolean(mailSett.bSendDaily)));
            cmd.Parameters.Add(new OleDbParameter(PRM_SEND_MONTHLY, Convert.ToBoolean(mailSett.bSendMonthly)));
            cmd.Parameters.Add(new OleDbParameter(PRM_SEND_TIME, Convert.ToDateTime(mailSett.sendDaily)));

            cmd.ExecuteNonQuery();
            response = "Settings saved successfully";
        }
        catch (Exception ex)
        {
            response = "There was an error: " + ex.Message;
        }
        return response;
    }

The TriplMail class is:

class TripleMail
{
    public string mailTo1;
    public string mailTo2;
    public string mailTo3;
    public DateTime sendDaily;
    public bool bSendDaily;
    public DateTime sendMonthly;
    public bool bSendMonthly;
}

And from some reason I bump into the same error no matter what solution I try to apply.

Data type mismatch in criteria expression.

Anyone has any idea of how to solve the issue?

Mustafa Özçetin
  • 1,893
  • 1
  • 14
  • 16
Ofir Kadosh
  • 19
  • 6
  • 2
    Why are you converting to datetime `Convert.ToDateTime(mailSett.sendDaily)` when `sendMonthly` is already a `DateTime`? And what does your database look like ? Can you provide more info about the table you are trying to update ? – CodeJunkie Jul 10 '23 at 11:32
  • Do these ([link1](https://stackoverflow.com/questions/25751424/c-sharp-access-oledb-data-type-mismatch-in-criteria-expression) and [link2](https://stackoverflow.com/questions/16217464/trying-to-insert-datetime-now-into-date-time-field-gives-data-type-mismatch-er)) answer your question ? – CodeJunkie Jul 10 '23 at 11:35
  • Let's try to narrow down the problem: *I suspect the two DateTime columns*. Can you try removing (commenting out) the `DateTime` columns in your update statetement? If this trial works without error then it means the problem is related with the `DateTime` columns. – Mustafa Özçetin Jul 10 '23 at 12:55
  • Side note: do not cache connection objects. Create when needed and dispose with `using`. – Charlieface Jul 10 '23 at 13:01

0 Answers0