CODEWITHSUNDEEP
c#ms-access
Ksaxes
Ksaxes

Reputation: 15

System.Data.OleDb.OleDbException: 'Data type mismatch in criteria expression.'

I am new at coding and currently trying to make this barcode tracker program. So far I searched and found what i need but now I couldn't find the answer of this problem (or couldn't understand what people tried to say). So here is my problem:

I am using Ms Access as database but, as numeric datatype Access allows me to enter maximum 10 digit numbers. Also, I know that some barcodes include alphabetic characters so I have to change the datatype to text. If the column's datatype which contains the barcodes is numeric, program works. When i change the column's datatype to text, program gives data type mismatch error.

Here is my code:

public partial class Form1 : Form
    {
        OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Barcode\\Database.accdb; Persist Security Info=False");
        OleDbDataReader DataReader;
        OleDbCommand command;
        bool flag = false;

        public Form1()
        {
            InitializeComponent();
        }
        public void Form1_Load(object sender, EventArgs e)
        {
        }
        public void txt_UPC_KeyDown(object sender, KeyEventArgs e)
        {           
            if ((txt_UPC.Text == "") && (e.KeyCode == Keys.Enter)) 
            {
                e.Handled = true;
            }
            else
            {
                if (e.KeyValue == (char)Keys.Enter)
                {
                    connection.Open();
                    string SelectQuery = "SELECT * FROM Products where [UPC]=" + txt_UPC.Text.ToString();
                    command = new OleDbCommand(SelectQuery, connection);                   
                    DataReader = command.ExecuteReader();  **//Error occurs here...**
                    while (DataReader.Read())
                    {
                        txtProduct.Text = DataReader["Product"].ToString();
                        txtPrice.Text = DataReader["Price"] + " ₺";        
                        if (DataReader["UPC"].ToString() == txt_UPC.Text)   
                        {
                           flag = true;
                        }
                    }
                    if (flag == true)
                    {
                            Test.Text = "Known Product";
                            txt_UPC.Text = "";
                        flag = false;
                    }
                    else                    
                    {                                                
                            Test.Text = "Unknown Product";                                               
                    }
                    connection.Close();
                }
            }
        }
    }

Upvotes: 0

Views: 1681

Answers (2)

Flydog57
Flydog57

Reputation: 7111

You need fully a formed SQL Server expression. You currently have

string SelectQuery = "SELECT * FROM Products where [UPC]=" + txt_UPC.Text.ToString();

Assuming that txt_UPC.Text is a string, you probably need to do this instead:

string SelectQuery = "SELECT * FROM Products where [UPC]= '" + txt_UPC.Text.ToString() + "'";

That encloses your string in the SQL-language mandated single quotes. For what it's worth, you probably don't need the .ToString() on that either.

And, it's a really bad idea to concatenate SQL like you are doing, particularly if you include user inputted text. Read up on "SQL Injection" and use parameterized SQL instead.

Upvotes: 0

June7
June7

Reputation: 21370

Suggest use apostrophe delimiters for text type field parameter.

string SelectQuery = "SELECT * FROM Products where [UPC]='" + txt_UPC.Text.ToString() + "'";

Upvotes: 1

Related Questions