1

I'm trying to insert the data to MS Access through VB.net. But it is generating the 'Data type mismatch in criteria expression.' error. I have thoroughly cross checked the code but not able to catch the bug

cmdInsert.Connection = myConnection
        myConnection.Open()

        If txtEmpId.Text <> "" And txtName.Text <> "" And cmbSexAdd.Text <> "" And DateTimePicker1.Text <> "" And cmbDesigAdd.Text <> "" And cmbDeptIdAdd.Text <> "" And cmbPaybandAdd.Text <> "" And cmbGradepayAdd.Text <> "" And PictureBox2.Image IsNot Nothing And txtBasicAdd.Text <> "" And cmbSpAdd.Text <> "" And cmbAccoAdd.Text <> "" And cmbNehsAdd.Text <> "" Then
            cmdInsert.CommandText = "INSERT INTO employee_main VALUES (@emp_id, @emp_name, @emp_sex, @emp_doj, @emp_desig, @emp_payband, @emp_gradepay, @emp_dept_id, @emp_photo, emp_basic, @emp_sp, @emp_staff_quater, @emp_nehs)"
            cmdInsert.Parameters.AddWithValue("@emp_id", txtEmpId.Text.ToUpper)
            cmdInsert.Parameters.AddWithValue("@emp_name", txtName.Text.ToUpper)
            cmdInsert.Parameters.AddWithValue("@emp_sex", cmbSexAdd.Text.ToUpper)
            cmdInsert.Parameters.AddWithValue("@emp_doj", DateTimePicker1.Value)
            cmdInsert.Parameters.AddWithValue("@emp_desig", cmbDesigAdd.Text.ToUpper)
            cmdInsert.Parameters.AddWithValue("@emp_payband", cmbPaybandAdd.Text.ToUpper)
            cmdInsert.Parameters.AddWithValue("@emp_gradepay", Convert.ToInt32(cmbGradepayAdd.Text))
            cmdInsert.Parameters.AddWithValue("@emp_dept_id", cmbDeptIdAdd.Text.ToUpper)
            cmdInsert.Parameters.AddWithValue("@emp_photo", IIf(PictureBox2.Image IsNot Nothing, arrimage, DBNull.Value))
            cmdInsert.Parameters.AddWithValue("@emp_basic", Convert.ToInt32(txtBasicAdd.Text))
            cmdInsert.Parameters.AddWithValue("@emp_sp", Convert.ToInt32(cmbSpAdd.Text))
            cmdInsert.Parameters.AddWithValue("@emp_staff_quater", cmbAccoAdd.Text.ToUpper)
            cmdInsert.Parameters.AddWithValue("@emp_nehs", Convert.ToInt32(cmbNehsAdd.Text))

            cmdInsert.CommandType = CommandType.Text
            cmdInsert.Connection = myConnection
            cmdInsert.ExecuteNonQuery()


Access Table fields are as follows

emp_id                  Text
emp_name                Text
emp_sex                 Text
emp_doj                 Date/Time
emp_desig               Text
wmp_payband             Text
emp_gradepay            Number
emp_dept_id             Text
emp_photo               OLE Object
emp_basic               Number
emp_sp                  Number
emp_staff_quater        Text
emp_nehs                Number


[enter image description here][1]


  [1]: https://i.stack.imgur.com/KumdR.jpg
Mayank Chaurasia
  • 11
  • 4
  • 1
    Make sure the data type in access column match with the data type your passing here – Rohad Bokhar Feb 28 '23 at 07:59
  • 2
    ALWAYS specify the columns you're inserting into rather than accept the default order. That way you can see whether you got the order of the values correct by looking at the SQL. Also, don't use `AddWithValue`. Use `Add` and specify the data type of each parameter. Then you know that an incorrect data type will not be inferred. – jmcilhinney Feb 28 '23 at 08:17
  • Issue could be with trying to insert image into record. Is the field Attachment type? Access doesn't handle this with SQL INSERT. Review https://stackoverflow.com/questions/18764314/storing-an-image-into-an-attachment-field-in-an-access-database – June7 Feb 28 '23 at 08:17
  • 1
    Don't use `IIf` in VB.,NET unless the year is prior to 2008. You should be using the `If` operator. – jmcilhinney Feb 28 '23 at 08:18
  • 2
    emp_id is not an autonumber field? emp_dep_id is not a number field for a numeric FK? – June7 Feb 28 '23 at 08:24

0 Answers0