data type mismatch in criteria expression, error persist even when the code is perfect

Question

This is the code I have made to update my table doctorss. The code has no errors and I have made sure that the table data types are ok; Doctor_ID data type is put to Autonumber and the others to Short Text.

Further I know this code will have impact on sql injections, and I should use parameters to do this, but as this way is possible and there's an error that I cannot solve, I need to know the solution for the error that I get when I update the table.

Error:

data type mismatch in criteria expression

I am using Access 2013 and Visual studio 2013.

  private void button6_Click(object sender, EventArgs e)
    {
        try
        {

            OleDbConnection cone = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\dev\\assignt_soft\\healthline_\\healthline_\\healthline_db.accdb");
            OleDbCommand come = new OleDbCommand("UPDATE doctorss set  Doctor_Name='"+textBox3.Text+"', dspecial='"+comboBox4.Text+"', dday= '"+comboBox3.Text+"',  dtime= '"+textBox2.Text+"' where Doctor_ID='" + textBox4.Text+"';");
            come.Connection = cone;
            cone.Open();
            come.ExecuteNonQuery();
            cone.Close();
            MessageBox.Show("Doctor updated");

            OleDbConnection cont = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\dev\\assignt_soft\\healthline_\\healthline_\\healthline_db.accdb");
            OleDbCommand comt = new OleDbCommand("select * from doctorss", cont);
            DataTable dte = new DataTable();
            cont.Open();
            dte.Load(comt.ExecuteReader());
            dataGridView1.DataSource = dte;
            cont.Close();

            return;

        }

        catch (OleDbException expee)
        { MessageBox.Show(expee.Message); }
    }

If Doctor_ID is a number why do you have quotes around the value you are comparing it to? SQL parameters help you avoid this type of issue as well as sql injection. — juharr
Break your SQL update down to one or two fields at a time and see which one is causing the error. My bet is on the dtime field being formatted incorrectly. — AlG

Rohit Rohit · Accepted Answer · 2015-09-30T12:03:51

I have made sure that the table data types are ok

since Doctor_ID is Autonumber you should not use quotes

     OleDbCommand come = new OleDbCommand("UPDATE doctorss set  
                             Doctor_Name='"+textBox3.Text+"', 
                             dspecial='"+comboBox4.Text+"',
                             dday= '"+comboBox3.Text+"',  
                             dtime= '"+textBox2.Text+"' 
                             where Doctor_ID=" + textBox4.Text+";");

data type mismatch in criteria expression, error persist even when the code is perfect

1 Answers