CODEWITHSUNDEEP
sqlvb.nettype-mismatch
Akbar
Akbar

Reputation: 21

OledbException Data type mismatch in criteria expression

I'm getting a OledbException Data type mismatch in the criteria expression, at ad.Fill(xDataset, "TblMaster").

I'm using an Access database and Telerik Reporting.

This is my code

Public Sub TanggalX()

    conn.Open()

    Dim str9 As String = "Select * From TblMaster Where Tanggal='" & Me.DateTimePicker1.Value.Date.ToString("yyyy/MM/dd")) & "'"
    ad = New OleDb.OleDbDataAdapter(str9, conn)
    xDataset.Clear()
    ad.Fill(xDataset, "TblMaster")
    obj_RepDoc = New Report1
    obj_RepDoc.DataSource = (xDataset)
    Me.ReportViewer1.Report = obj_RepDoc
    Me.ReportViewer1.RefreshReport()
    Me.Show()
    conn.Close()
End Sub

Please help me this is my last problem for this project.

Upvotes: 2

Views: 2010

Answers (3)

user1198117
user1198117

Reputation: 31

You should use # instead of ' for Date/Time fields.

 Dim str9 As String = "SELECT * FROM TblMaster WHERE Tanggal=#" & Me.DateTimePicker1.Value.Date.ToString("yyyy/MM/dd")) & "#"

Upvotes: 3

Sharbell Mouess
Sharbell Mouess

Reputation: 11

I think InBetween is right. You can also convert the string to date inside the SQL statement instead of using parameters by replacing your SQL statement with this:

Dim str9 As String = "Select * From TblMaster Where Tanggal=CDate('" & Me.DateTimePicker1.Value.ToString() & "')"

Upvotes: 0

InBetween
InBetween

Reputation: 32750

My guess is that TblMaster.Tangall is of type Date and you are specifying it as a string in the WHERE clause of your SQL select command.

Try the following:

using (OleDbCommand comm = new OleDbCommand())
{
    var parameter =comm.Parameters.AddWithValue("@tangall", Me.DateTimePicker1.Value);
    parameter.OleDbType = OleDbType.DBDate;

        using (var ad = new OleDbDataAdapter(comm))
        {
            ... //do your stuff here
        }
}

Upvotes: 1

Related Questions