Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Access WinForm
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);
 
                try
                {
                    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);
                   
                    con.Open();
                    
                    OleDbDataReader datareader;
                 
                    datareader = com1.ExecuteReader(); // The error occors at this line
                    
                    datareader.Read();
 
                    rate = Convert.ToDouble(datareader);
 
                    stockOutWardsDetailsDataGridView.CurrentRow.Cells[ratecolindex].Value = rate;
 
                    datareader.Close();
                    con.Close();
 
                     }
 
                    catch (Exception err) { MessageBox.Show(err.ToString()); }
 

                }
 
            }
Posted 1-Nov-12 20:42pm
Edited 1-Nov-12 21:00pm
v2
Comments
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
good
Please Sign up or sign in to vote.

Solution 1

Change your query like this :
string getRate = "SELECT ItemRates.Rate
FROM UnitMaster
INNER JOIN ItemRates 
ON UnitMaster.UnitId = ItemRates.UnitId
INNER JOIN ItemMaster
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]);
  Permalink  
v3
Rate this: bad
good
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.
 
Example:
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();
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 230
1 PIEBALDconsult 150
2 DamithSL 125
3 Andreas Gieriet 90
4 Jochen Arndt 90
0 OriginalGriff 5,790
1 DamithSL 4,601
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,195


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 2 Nov 2012
Copyright © CodeProject, 1999-2014
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