Member Avatar for sushilsth 
Private Sub btnupdate_Click(sender As Object, e As EventArgs) Handles btnupdate.Click
        Dim myindex As Integer
        Dim reg As Integer
        Dim roll As Integer
        Dim nepaliw As Integer
        Dim nepalio As Integer
        Dim mathw1 As Integer
        Dim matho As Integer
        Dim mathw2 As Integer
        Dim engw As Integer
        Dim engo As Integer
        Dim pe As Integer
        Dim creative As Integer

        myindex = DataGridView1.CurrentRow.Index
        reg = DataGridView1.Item(0, myindex).Value
        roll = DataGridView1.Item(1, myindex).Value
        nepaliw = DataGridView1.Item(2, myindex).Value
        nepalio = DataGridView1.Item(3, myindex).Value
        mathw1 = DataGridView1.Item(4, myindex).Value
        matho = DataGridView1.Item(5, myindex).Value
        mathw2 = DataGridView1.Item(6, myindex).Value
        engw = DataGridView1.Item(7, myindex).Value
        engo = DataGridView1.Item(8, myindex).Value
        pe = DataGridView1.Item(9, myindex).Value
        creative = DataGridView1.Item(10, myindex).Value

        Dim sqlquery As String = "update nursery set rollno='" & roll & "',[nepali(writing)]='" & nepaliw & "',[nepali(oral)]='" & nepalio & "',[maths(writing1)]='" & mathw1 & "',[maths(oral)]='" & matho & "',[maths(writing2)]='" & mathw2 & "',[english(writing)]='" & engw & "',[english(oral)]='" & engo & "',[physical_edu(oral)]='" & pe & "',[creative(oral)]='" & creative & "' where regno='" & reg & "'"
        Dim sqlcommand As New OleDbCommand
        With sqlcommand
            .CommandText = sqlquery
            .Connection = conn
            .ExecuteNonQuery()

        End With
    End Sub
  • 2 Contributors
  • 4 Replies
  • 198 Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by M.Waqas Aslam

Dani AI

Generated

The error "data type mismatch in criteria expression" almost always means the values you send to Access don't match the column types. In this thread already noted regno is an Autonumber and the other fields are integers; was right to suggest converting values to integers. The frequent causes are: numeric columns being given quoted strings, DBNull/empty/non-numeric cell text, or using string concatenation instead of typed parameters.

A safe fix is to validate/convert each DataGridView cell to an Integer and use a parameterized UPDATE (OleDb uses positional ? parameters). Example pattern (validate first, then add parameters with explicit OleDbType):

' small helper - returns 0 for non-numeric/empty cells
Function GetIntCell(cellVal As Object) As Integer
    Dim s = If(cellVal Is Nothing OrElse IsDBNull(cellVal), "", cellVal.ToString().Trim())
    Dim n As Integer
    If Integer.TryParse(s, n) Then Return n
    Return 0
End Function

Dim sql = "UPDATE nursery SET rollno = ?, [nepali(writing)] = ?, [nepali(oral)] = ?, [maths(writing1)] = ?, [maths(oral)] = ?, [english(writing)] = ?, [physical_edu(oral)] = ?, [creative(oral)] = ? WHERE regno = ?"

Using cmd As New OleDbCommand(sql, conn)
    cmd.Parameters.Add("?", OleDbType.Integer).Value = GetIntCell(DataGridView1.Item(1, myindex).Value)
    cmd.Parameters.Add("?", OleDbType.Integer).Value = GetIntCell(DataGridView1.Item(2, myindex).Value)
    ' ... add remaining parameters in the exact same order as placeholders ...
    cmd.Parameters.Add("?", OleDbType.Integer).Value = GetIntCell(DataGridView1.Item(0, myindex).Value) ' regno for WHERE
    cmd.ExecuteNonQuery()
End Using

Practical tips:

  • Do not wrap numeric values in single quotes — that turns them into strings for Access.
  • Prefer Integer.TryParse over Val or blind CInt so you can handle empty/invalid cells.
  • Avoid AddWithValue; use Parameters.Add(..., OleDbType.Integer) so the type is explicit.
  • Remember OleDb parameters are positional: the order you add them must match the ? placeholders.
  • If the error persists, inspect the actual cell contents and the Access table column types (Autonumber = numeric).

Recommended Answers

All 4 Replies

Member Avatar for sushilsth

plz help me i get this error while updating.. regno is autonumber n all fields are integer

Member Avatar for M.Waqas Aslam

for integer datatype , you have to conver your values into integer first , like this

Dim myindex As Integer
myindex = val(DataGridView1.CurrentRow.Index)

Regards

Member Avatar for sushilsth

thanx waqas aslam

Member Avatar for M.Waqas Aslam

if problem is solved , please mark this thread solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.