System.Data.OleDb.OleDbException：'条件表达式中的数据类型不匹配。'

问题描述

 为什么我的编码显示此错误：" System.Data.OleDb.OleDbException：'条件表达式中的数据类型不匹配。'" ？

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

谢谢。

以下是我的代码： 

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 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）

       试试¥b $ b            connection.Open（）

        C ex ex As Exception

            MsgBox（"警告：连接问题！"）

       结束尝试



  &NBSP; &NBSP; &NBSP; Dim sqlQuery As String =" Select * from Table1 Where check_in> ='" &安培; DateTimePicker1.Value& "'和check_in< ='" &安培; DateTimePicker2.Value& "'"

  &NBSP; &NBSP; &NBSP; Dim Command As OleDbCommand

  &NBSP; &NBSP; &NBSP; Dim dr As OleDbDataReader

  &NBSP; &NBSP; &NBSP;昏暗的结果As Boolean = False

  &NBSP; &NBSP; &NBSP; '试试¥b $ b  &NBSP; &NBSP; &NBSP; Command = New OleDbCommand（sqlQuery，connection）

  &NBSP; &NBSP; &NBSP; dr = Command.ExecuteReader（）

  &NBSP; &NBSP; &NBSP;如果dr.Read（）然后

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;结果=真'在日期之间得到了¥b $ b  &NBSP; &NBSP; &NBSP;结束如果



  &NBSP; &NBSP; &NBSP; sqlQuery =" SELECT * from Table1 Where check_out> ='" &安培; DateTimePicker1.Value& "'和check_out< ='" &安培; DateTimePicker2.Value& "'"

  &NBSP; &NBSP; &NBSP; Command = New OleDbCommand（sqlQuery，connection）

  &NBSP; &NBSP; &NBSP; dr = Command.ExecuteReader（）

  &NBSP; &NBSP; &NBSP;如果dr.Read（）然后

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; result = True'介于日期之间

  &NBSP; &NBSP; &NBSP;结束如果



  &NBSP; &NBSP; &NBSP;如果result = True则为
  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; MsgBox（"数据库中的日期是/在日期之间"）

  &NBSP; &NBSP; &NBSP;否则

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; MsgBox（"数据库中的日期不在日期之间"）

  &NBSP; &NBSP; &NBSP;结束如果

        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

推荐答案

最好使用任何SQL语句的参数，如图所示下面是我的第一个假设，因为你抛出了一个例外。如果这不能解决问题，请仔细检查数据库表中的那些字段是否为日期（您可能已经知道但是需要提及此字段）。 

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). 

请注意我如何改变一些周围的代码，例如使用将处理已使用对象并在没有If语句的情况下设置变量结果的语句。

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

这篇关于System.Data.OleDb.OleDbException：'条件表达式中的数据类型不匹配。'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！