CODEWITHSUNDEEP
vb.netms-access-2007
Venu GoPal
Venu GoPal

Reputation: 113

Data type mismatch in criteria expression (vb.net, access)

I am trying to take data from the database to the grid. The condition is SELECT * FROM entries WHERE edate='" & Me.dtpDate.Value.Date & "'" But I am getting the error message Data type mismatch in criteria expressionenter image description here. Please see the code below. Also I have attached a screenshot of the error message.

    Private Sub dtpDate_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtpDate.Leave
    'GetDayBookOpeningBalance()
    If Me.lblHeading1.Text <> "Daybook entry" Then
        Using MyConnection As OleDb.OleDbConnection = FrmCommonCodes.GetConnection(),
            MyAdapter As New OleDb.OleDbDataAdapter("SELECT * FROM entries WHERE edate='" & Me.dtpDate.Value.Date & "'", MyConnection)
            'Format(Me.dtpDate.Value.Date, "dd/MM/yyyy"))
            If MyConnection.State = ConnectionState.Closed Then MyConnection.Open()
            Using MyDataSet As New DataSet
                MyAdapter.Fill(MyDataSet, "entries")
                Me.grdDayBook.DataSource = MyDataSet.Tables("entries")
                Dim DataSetRowCount As Integer = MyDataSet.Tables("entries").Rows.Count
                If DataSetRowCount > 0 Then
                    SetGridProperty()
                Else
                    ShowBlankGrid()
                    FrmCommonCodes.MessageDataNotFound()
                End If
            End Using
        End Using
    Else
        ShowBlankGrid()
    End If
End Sub

Upvotes: 1

Views: 4634

Answers (1)

Steve
Steve

Reputation: 216343

This is exactly what could happen for not using parameterized queries.
I bet that your column edate is a column of type Date/Time but you concatenate your Me.dtpDate.Value.Date to the remainder of your sql string command.
This forces an automatic conversion from DateTime to String but the conversion is not as your database would like to see.

If you use a parameter there is no conversion and the database engine understand exactly what you are passing. 

Dim sqlText = "SELECT * FROM entries WHERE edate=@dt"
MyAdapter As New OleDb.OleDbDataAdapter(sqlText, MyConnection)
MyAdapter.SelectCommand.Parameters.Add("@dt", OleDbType.Date).Value = Me.dtpDate.Value.Date
....

Upvotes: 3

Related Questions