Click here to Skip to main content
15,845,212 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hay guys having a big issue with this code, im trying to query a database and return all records which have the date inputted through the date time picker. No matter what I change and do (searched for a soultion for hours) nothing seems to work and keep getting Data type mismatch in criteria expression.

VB
Imports System.Data.OleDb

Public Class ViewBookings
    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDbDataAdapter
    Dim sql_1 As String
    Dim sql_2 As String

Private Sub btnDate_Click(sender As Object, e As EventArgs) Handles btnDate.Click
        Dim editdate As date 
        dtpView.Value = Format(dtpView.Value.Date, "dd/MM/yyyy")
        editdate = dtpView.Value
        dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"

        dbSource = "Data Source =" & Application.StartupPath & "/Driving.accdb"

        con.ConnectionString = dbProvider & dbSource
        con.Open()


        sql_2 = "SELECT * FROM tblBookings WHERE LessonDate = '#" & editdate & "#' ORDER BY LessonDate DESC"

        da = New OleDbDataAdapter(sql_2, con)

        da.Fill(ds)

        dgBookings.DataSource = ds.Tables("query2")
        con.Close()
    End Sub
End Class
Posted
Updated 27-Mar-15 9:40am
v2
Comments
ZurdoDev 27-Mar-15 14:38pm    
Remove the single quotes around your date in your where clause.
Member 11561229 27-Mar-15 14:42pm    
Thanks dude worked
ZurdoDev 27-Mar-15 14:45pm    
Glad to hear. I'll post as solution.
Richard Deeming 27-Mar-15 15:30pm    
Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Use parametrized query[^]:

VB
sql_2 = "PARAMETERS [editdate] DATE; SELECT * FROM tblBookings WHERE LessonDate = #[editdate]# ORDER BY LessonDate DESC;"


How to use it?
You need to use OledbCommand[^] with parameters[^]. Follow the links for sample code.

Note that OledbCommand usually uses no-named parameters, but MS Access database can use named parameters.
 
Share this answer
 
Comments
Richard Deeming 27-Mar-15 15:31pm    
Almost perfect, except the parameter placeholder is just "?":
... WHERE LessonDate = ? ORDER BY ...
As discussed in the comments, you'll want to remove the single quotes from around the date in the where clause.

Access uses #'s around dates but SQL will use single quotes but not both at the same time.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900