Member Avatar for sushilsth 
Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click
        Dim sqlquery As String = "INSERT INTO firstfloor_house_rent (datee,amt) VALUES ('" & DateTimePicker8.Text & "', " & TextBox24.Text & ")"
        Dim sqlcommand As New OleDbCommand
        With sqlcommand
            .CommandText = sqlquery
            .Connection = conn
        End With
        ans = sqlcommand.ExecuteNonQuery()
        If (ans = True) Then
            'message added
            MessageBox.Show("Sucessfully added", "Sucess", MessageBoxButtons.OK)
        Else
            MessageBox.Show("failed to add record", "Error!!!", MessageBoxButtons.OK)   '' error

        End If
    End Sub

what is data type mismatch in this query. it gives data type mismatch in criteria expression.. plz help...

  • 3 Contributors
  • 3 Replies
  • 303 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

This error means Access is getting a value whose type doesn’t match the column. Common causes in this thread: concatenating DateTimePicker8.Text / TextBox24.Text into SQL (string formatting, locale or empty-string problems), quoting a numeric column, or using the wrong date literal syntax. was right to suggest parameters—they avoid all of the above—but note ExecuteNonQuery returns the number of rows affected (an Integer), not a Boolean.

A safe, minimal pattern to use with OleDb: pass strongly-typed values (DateTime for dates, numeric type for numbers), validate the TextBox first, use parameter placeholders (?) with OleDb, and check rowsAffected > 0. Example (adjust OleDbType to match your Access field — Integer/Double/Currency as needed):

Using cmd As New OleDbCommand("INSERT INTO firstfloor_house_rent ([datee], [amt]) VALUES (?, ?)", conn)
    cmd.Parameters.Add(New OleDbParameter("pDate", OleDbType.Date)).Value = DateTimePicker8.Value.Date

    Dim amount As Integer
    If Not Integer.TryParse(TextBox24.Text.Trim(), amount) Then
        MessageBox.Show("Enter a valid integer amount")
        Return
    End If
    cmd.Parameters.Add(New OleDbParameter("pAmt", OleDbType.Integer)).Value = amount

    Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
    If rowsAffected > 0 Then MessageBox.Show("Successfully added") Else MessageBox.Show("Insert failed")
End Using

Notes and troubleshooting:

  • OleDb parameter names are ignored; order matters. Add parameters in the same order as the ? placeholders.
  • Use DateTimePicker.Value (not .Text) so you send a real DateTime.
  • If amt is decimal/currency change to OleDbType.Decimal/Currency.
  • If you must build SQL literals (not recommended), Access uses # around dates (e.g. #1/2/2013#).
  • Wrap DB calls in Try/Catch and confirm the table schema in Access (field types, reserved names).
    Thanks to for the pointer to an example — using that approach will eliminate the data type mismatch in almost every case.

Recommended Answers

All 3 Replies

Member Avatar for Member #1042208

1) You might want to check your query again.
Dim sqlquery As String = "INSERT INTO firstfloor_house_rent (datee,amt) VALUES ('" & DateTimePicker8.Text & "', " & TextBox24.Text & ")"

here you forgot to use '' for textbox24.text (String data should be used with single quotes in SQL query)

2) Your data might be mismatching here.
ans = sqlcommand.ExecuteNonQuery() If (ans = True) Then

As type returned by ExecuteNonQuery is bool i.e 1 or 0 so you might want to compare it with 1 or 0 in If Condition.

3) Free Advice: Better to use parametrized query.

Member Avatar for sushilsth

textbox24 hold integer data n data type is also same in access.
thanx rahul for reply.. plz can u modify this code using parametrized query because i dont have much knowledge of parametrized query...

Member Avatar for Reverend Jim

You can find an example here

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.