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

  • Question

  •  Hi, Why my coding show this error: " System.Data.OleDb.OleDbException: 'Data type mismatch in criteria expression.'" ?

    Thank you.

    Below are my code: 

    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Desktop\Db.accdb;Persist Security Info=False;"

            Dim connection As OleDbConnection = New OleDbConnection(connectionString)
            Try
                connection.Open()
            Catch ex As Exception
                MsgBox("ALERT : Problem on the connection !")
            End Try

            Dim sqlQuery As String = "Select * from Table1 Where check_in >= '" & DateTimePicker1.Value & "' And check_in <= '" & DateTimePicker2.Value & "'"
            Dim Command As OleDbCommand
            Dim dr As OleDbDataReader
            Dim result As Boolean = False
            'Try
            Command = New OleDbCommand(sqlQuery, connection)
            dr = Command.ExecuteReader()
            If dr.Read() Then
                result = True 'got in the between dates
            End If

            sqlQuery = "SELECT * from Table1 Where check_out >= '" & DateTimePicker1.Value & "' And check_out <= '" & DateTimePicker2.Value & "'"
            Command = New OleDbCommand(sqlQuery, connection)
            dr = Command.ExecuteReader()
            If dr.Read() Then
                result = True 'got in the between the dates
            End If

            If result = True Then
                MsgBox("Date in database are/ is in the between of the dates")
            Else
                MsgBox("Date in database are/ is not in the between of the dates")
            End If

    Saturday, April 7, 2018 8:28 AM

All replies

  • Hello,

    It's best to use parameters for any SQL statements as shown below which is would my first assumption for the reason you have an exception thrown. If this does not fix the issue, double check that those fields in the database table are Dates (which you may already know but need to mention this). 

    Also note how I altered some of the surrounding code e.g. Using statements which will dispose of used objects and setting the variable result without a If statement.

    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                        "Data Source=C:\Users\Desktop\Db.accdb;" & 
                                        "Persist Security Info=False;"
    
    Dim sqlQuery As String = "SELECT * FROM Table1 WHERE check_in >= ? AND check_in <= ?"
    Dim result As Boolean = False
    
    Using connection As New OleDbConnection(connectionString)
        Using Command As New OleDbCommand(sqlQuery, connection)
            Command.Parameters.AddWithValue("?",DateTimePicker1.Value)
            Command.Parameters.AddWithValue("?",DateTimePicker2.Value)
            Try
                connection.Open()
                Dim reader = Command.ExecuteReader
                result = reader.HasRows
            Catch ex As Exception
                '
                ' Handle execption
                '
            End Try
        End Using            
    End Using


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by Cor Ligthert Saturday, April 7, 2018 10:46 AM
    Saturday, April 7, 2018 9:35 AM
    Moderator
  • Hi ben10chia,

    If you want to query some data for a period of time in Access, you suggest you use Between ... And.., then you need to add # before and after data.

     Private Sub loaddate()
            Dim dt As New DataTable
            Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\database\Database1.accdb"
            Dim sql As String = "Select * from test2 where Birthday between #" & DateTimePicker1.Value.ToString("yyyy-MM-dd") & "# and #" & DateTimePicker2.Value.ToString("yyyy-MM-dd") & "#"
            Using conn As New OleDbConnection(str)
                conn.Open()
                Using cmd As New OleDbCommand(sql, conn)
                    Dim adapter As New OleDbDataAdapter(cmd)
                    adapter.Fill(dt)
                End Using
                conn.Close()
            End Using
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Tuesday, April 17, 2018 9:35 AM
    Monday, April 9, 2018 6:26 AM
    Moderator