CODEWITHSUNDEEP
c#sql.netdatabasems-access
JustinCase
JustinCase

Reputation: 43

Data type mismatch in criteria expression when Query Access DB

I'm having an issue inserting a row into my access db. I keep getting a "Data type mismatch in criteria expression". I've tried tons of different formatted queries and can't seem to figure out where I'm going wrong.

    public void addUserToDB(string username)
    {
        string updateStr = "Insert Into Users ([Username], [Currency], [Joined], [Online], [Notes]) "
                         + "Values ( ?, '0', ?, 'Yes', '')";
        OleDbCommand update = new OleDbCommand(updateStr, con);
        update.Parameters.Add("", OleDbType.VarChar).Value = username;
        update.Parameters.AddWithValue("","'#" + DateTime.Now.ToString("G") + "#'");
        update.Parameters.AddWithValue("","'#" + DateTime.Now.ToString("G") + "#'");
        execute(update);
    }

It's not my connection string or anything else since all my other queries work just fine. It has to be something in here. I'm assuming is may have something to due with the date time.

Access DB:
Username: ShortText
Currency: Number
Joined: Date/Time in "General Date" Format
Online: Yes/No
Notes: ShortText

Upvotes: 1

Views: 1146

Answers (1)

Soner Gönül
Soner Gönül

Reputation: 98740

Since your Currency column is Number and Online seems Yes/No (It stores 1 bit), you don't need to use single quotes with them. Using single quotes threat them as a character.

string updateStr = "Insert Into Users ([Username], [Currency], [Joined], [Online], [Notes]) "
                     + "Values ( ?, 0, ?, Yes, '')";
                                   ^^^    ^^^

And your Joined is DateTime, you shouldn't try to insert string representation of your DateTime.Now. Just insert it as a DateTime as ODBC canonical date format.

From documentation;

Date values must be either delimited according to the ODBC canonical date format or delimited by the datetime delimiter ("#"). Otherwise, Microsoft Access will treat the value as an arithmetic expression and will not raise a warning or error.

Upvotes: 1

Related Questions