Click here to Skip to main content
11,803,495 members (71,764 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: C# Access
I am trying to get a value from my database, I get the following error: “OleDbException (0x80040e07): Data type mismatch in criteria expression. System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior). My code is as follows:
private void stockOutWardsDetailsDataGridView_CellValueChanged(object sender, DataGridViewCellEventArgs e)
           int itemcolindex = 1;
           int unitcolindex = 3;
           int ratecolindex = 4;

           if (stockOutWardsDetailsDataGridView.Columns[e.ColumnIndex].Name == "dataGridViewTextBoxColumn5")

               itemid = Convert.ToString(stockOutWardsDetailsDataGridView.CurrentRow.Cells[itemcolindex].Value);
               unitid = Convert.ToString(stockOutWardsDetailsDataGridView.CurrentRow.Cells[unitcolindex].Value);
                   OleDbConnection con = new OleDbConnection(constring);
                   string getRate = "SELECT ItemRates.Rate FROM UnitMaster INNER JOIN (ItemMaster INNER JOIN ItemRates ON ItemMaster.ItemCode = ItemRates.ItemCode) ON UnitMaster.UnitId = ItemRates.UnitId WHERE (((ItemMaster.ItemCode)=" + itemid + ") AND ((UnitMaster.UnitId)= " + unitid + "));";
                   OleDbCommand com1 = new OleDbCommand(getRate, con);
                   OleDbDataReader datareader;
                   datareader = com1.ExecuteReader(); // The error occors at this line

                   rate = Convert.ToDouble(datareader);
                   stockOutWardsDetailsDataGridView.CurrentRow.Cells[ratecolindex].Value = rate;
                   catch (Exception err) { MessageBox.Show(err.ToString()); }

Posted 1-Nov-12 19:42pm
Edited 1-Nov-12 20:00pm
Kuthuparakkal at 2-Nov-12 1:48am
check to see your query works
Firdoshi at 2-Nov-12 1:59am
yes the query works perfect
Kuthuparakkal at 2-Nov-12 2:18am
see my answer too
Firdoshi at 2-Nov-12 3:01am
hey Kuthuparakkal, Thanks. but after making changes to the query it throws another exception error saying "Syntax error (missing operator) in query expression.
Kuthuparakkal at 2-Nov-12 3:05am
try now, query updated.
Firdoshi at 2-Nov-12 3:20am
Same exception error mesg again my friend. "Syntax error missing operator in query
Kuthuparakkal at 2-Nov-12 3:25am
I assumed ItemCode and UnitId are int.
If not enclose them within single quote for WHERE Clause.
WHERE ItemMaster.ItemCode= '"+ itemid + "' AND UnitMaster.UnitId= '" + unitid + "';";

between : I created the tables and executed my query and it works... are you using my query or what ?
Firdoshi at 2-Nov-12 3:29am
hi, you were right, i was missing the 'quote' marks. it works like a charm now thanks my friend. and yes yor query & my query both work now. thanks. :)
Kuthuparakkal at 2-Nov-12 3:30am
Gald to know that it works, please mark answer!
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Change your query like this :
string getRate = "SELECT ItemRates.Rate
FROM UnitMaster
ON UnitMaster.UnitId = ItemRates.UnitId
ON ItemMaster.ItemCode = ItemRates.ItemCode
WHERE ItemMaster.ItemCode= "+ itemid + " AND UnitMaster.UnitId= " + unitid + ";";

Also the reading from reader should be like this:
rate = Convert.ToDouble(datareader[0]);
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

If you would like to return only one record from your database, instead of ExecuteReader(), use ExecuteScalar()[^] method.

CommandText = "SELECT MAX(IR.Rate) " +
"FROM UnitMaster AS UM INNER JOIN ItemRates AS IR ON UM.UnitId = IR.UnitId " +
"INNER JOIN ItemMaster AS IM ON IM.ItemCode = IR.ItemCode " +
"WHERE IM.ItemCode= "+ itemid + " AND UM.UnitId= " + unitid + ";";
Int32 myrate = (int32) ExecuteScalar();

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 375
1 CPallini 290
2 F-ES Sitecore 270
3 Sergey Alexandrovich Kryukov 234
4 CHill60 220
0 OriginalGriff 3,000
1 Maciej Los 1,910
2 KrunalRohit 1,862
3 CPallini 1,755
4 Richard MacCutchan 1,187

Advertise | Privacy | Mobile
Web02 | 2.8.151002.1 | Last Updated 2 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100