Click here to Skip to main content
13,046,176 members (91,396 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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
Updated 1-Nov-12 20:00pm
Kuthuparakkal 2-Nov-12 1:48am
check to see your query works
Firdoshi 2-Nov-12 1:59am
yes the query works perfect
Kuthuparakkal 2-Nov-12 2:18am
see my answer too
Firdoshi 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 2-Nov-12 3:05am
try now, query updated.
Firdoshi 2-Nov-12 3:20am
Same exception error mesg again my friend. "Syntax error missing operator in query
Kuthuparakkal 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 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 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
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 2 Nov 2012
Copyright © CodeProject, 1999-2017
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