Data type mismatch in criteria expression when Query Access DB

Question

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

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Jul 25, 2014 at 7:56
Why bother to insert a date when you can default the field to Now() and save the trouble? It would only be useful to use a date if you were running an update query. — Fionnuala
– Fionnuala, Commented Jul 25, 2014 at 9:05

Soner Gönül · Accepted Answer · 2014-07-25 07:51:15Z

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.

Thanks for the solution! update.Parameters.AddWithValue("",DateTime.Now.ToString("G")) solved it!

Collectives™ on Stack Overflow

Data type mismatch in criteria expression when Query Access DB

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related