Missing operator in query expression?

Question

This code is giving me a syntax error.

DataGridView1.DataSource = ""
dAdapt = New OleDb.OleDbDataAdapter("select CustName, CustMobileNo, SMSDate, OilChangeStatus from invoiceHeader_tb where SMSDate =" & DTPDueDate.Value & " order by SMSDate", con)
Dim dt As New DataTable
dAdapt.Fill(dt)
Me.DataGridView1.DataSource = dt
Me.DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
Me.DataGridView1.ReadOnly = True
con.Close()

Error:

syntax error (missing operator) in query expression 'SMSDate =25-Feb-16 4:05PM'

`New OleDb.OleDbDataAdapter("select CustName, CustMobileNo, SMSDate, OilChangeStatus from invoiceHeader_tb where SMSDate ='" & DTPDueDate.Value & "' order by SMSDate", con)` use this — Jaydip Jadhav, Feb 25 '16 at 11:15

score 1 · Answer 1 · answered Feb 25 '16 at 11:15

1

Add single quote here

SMSDate = '" & DTPDueDate.Value & "'

So it will be like this

dAdapt = New OleDb.OleDbDataAdapter("select CustName, CustMobileNo, SMSDate, OilChangeStatus from invoiceHeader_tb where SMSDate ='" & DTPDueDate.Value & "' order by SMSDate", con)

answered Feb 25 '16 at 11:15

Ubiquitous Developers

3,637
6
33
78

still facing data type mismatch in criteria expression – Abdul Khaliq Feb 28 '16 at 17:43
SMSDate is datetime or string? – Ubiquitous Developers Feb 29 '16 at 05:41
smsDate is a short date(format = 2/23/2016), I am storing through DateTimePicker tools. – Abdul Khaliq Mar 01 '16 at 11:58

score 0 · Answer 2 · answered Feb 25 '16 at 11:15

0

You're missing apostrophes (single quotes) around your date.

where SMSDate ='" & DTPDueDate.Value & "' order

answered Feb 25 '16 at 11:15

Phiter

14,570
14
50
84

still facing data type mismatch in expression – Abdul Khaliq Feb 28 '16 at 17:41

score 0 · Answer 3 · edited May 23 '17 at 10:28

0

Your raw SQL is missing single quotes around the date value, but that still may not fix the error because the db may be expecting the date in a certain format.

You haven't specified the db you are using but most expect the date in 'yyy-MM-dd' format

Also this way of executing sql also leaves you open to SQL Injection Vulnerabilities so you should do this properly with a parameterised query:

For exmaple:

Public Function GetBarFooByBaz(ByVal Baz As String) As String
    Dim sql As String = "SELECT foo FROM bar WHERE baz= @Baz"

    Using cn As New SqlConnection("Your connection string here"), _
        cmd As New SqlCommand(sql, cn)

        cmd.Parameters.AddWithValue("@Baz", Baz)
        Return cmd.ExecuteScalar().ToString()
    End Using
End Function

Reference from this answer:How do I create a parameterized SQL query? Why Should I?

Doing it this way means you don't have to set the date in a specific format and you also don't have to remember whether you need quotes around a value or not.

edited May 23 '17 at 10:28

Community

1
1

answered Feb 25 '16 at 12:15

Matt Wilko

26,994
10
93
143

Parameters are important and is a must and great to do now if he isn't. However `you don't have to set the date in a specific format` I disagree with that statement. There are different types for dates, looking at the `SqlDBType` enumeration currently there are three and each one is different in context. Depending on his column type and what he passes in `will make a difference`. On another note wrapping the command in a using statement is best as well, it prevents a call to the finalizer which when the command is disposed is an enhancement. – Trevor Feb 25 '16 at 12:51
@Codexer - I have changed the answer to give more detail and also to use `AddWithValue` which does not require you to specify the `SqlDbType` – Matt Wilko Feb 25 '16 at 13:34
Fair enough, but `AddWithValue` ***isn't any better***; it relies on the DB to implicitly cast that value passed in being it's an object at that point. I like to specify my types to what they are and not solely rely on the implicit casting of SQL to do this. Furthermore `AddWithValue` is a performance thing, it is a anti-pattern because of the performance issues related to the parameter type and it's size. – Trevor Feb 25 '16 at 13:54

Missing operator in query expression?

3 Answers3