c# oledb exception data type mismatch occured in select query

Question

I'm trying to create a form to edit the datagridview which is data-bind to an access database, but when I was querying the database it threw an exception "Data type mismatch in criteria expression". Here's my code/select query:

OleDbCommand cmd = conn.CreateCommand();
string query = "SELECT * FROM DataServisan WHERE NomorNota=?";

conn.Open();

cmd.CommandText = query;

cmd.Parameters.Add(new OleDbParameter { Value = nomorNota, DbType = DbType.String });

OleDbDataReader dr = cmd.ExecuteReader();

"WHERE NomorNota=?" This is not correct..!! You need to have a Value to compare in where clause — Prateek Shrivastava
My Bad - I didnt notice.... So NomorNota is a string/varchar column in DB? — Prateek Shrivastava

Tetsuya Yamamoto Tetsuya Yamamoto · Accepted Answer · 2018-05-16T02:36:32

There is no DbType.String in OleDbType enumeration definition. You can use VarChar, VarWChar, LongVarChar or LongVarWChar depending on length of string value when using OleDbParameter constructor:

var parameter = new OleDbParameter("ParamName", OleDbType.VarWChar);
parameter.Value = nomorNota;
cmd.Parameters.Add(parameter);

Alternative way to define parameter value:

cmd.Parameters.Add("ParamName", OleDbType.VarWChar);
cmd.Parameters["ParamName"].Value = nomorNota;

References:

OleDbParameter Class (MSDN Docs)

OleDbParameterCollection.Add Method (MSDN Docs)

OleDbType Enumeration vs. Microsoft Access Data Types (MS Support)

c# oledb exception data type mismatch occured in select query

1 Answers