syntax error missing operator in query expression "Username = and level like '%instructor%'

Question

this code below"" is working if its in "search button" but I would like to use this in "Load form" that when i run it should automatically display data into datagridview which giving error on the above mentioned. any suggestion would be appreciated.

 Private Sub Search_Record()
    Dim conn As New OleDbConnection
    Dim cmd As New OleDbCommand
    Dim da As New OleDbDataAdapter
    Dim dt As New DataTable
    Dim sSQL As String = String.Empty
    Try
        conn = New OleDbConnection(Get_Constring)
        conn.Open()
        cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        sSQL = "SELECT Username, lname + ', ' + fname + ' ' + mname as name, password FROM Instructor"
        If Me.cboSearchBy.Text = "Name" Then
            sSQL = sSQL & " where lname + ', ' + fname + ' ' + mname like '%" & Me.txtSearch.Text & "%'"
            sSQL = sSQL & " and  level like '%instructor%'"
        Else
            sSQL = sSQL & " where Username =" & Me.txtSearch.Text
            sSQL = sSQL & " and  level like '%instructor%'"
        End If
        cmd.CommandText = sSQL
        da.SelectCommand = cmd
        da.Fill(dt)
        Me.dtgResult.DataSource = dt
        If dt.Rows.Count = 0 Then
            MsgBox("No record found!")
        End If
    Catch ex As Exception
        MsgBox(ErrorToString)
    Finally
        conn.Close()
    End Try
End Sub

Read up about SQL injections and use prepared statements rather than concatenating SQL queries. — Konrad Rudolph, Oct 04 '13 at 13:43

score 4 · Accepted Answer · edited May 23 '17 at 12:29

When the form starts and there is no text in the txtSearch textbox your query becomes syntactically wrong. If you had used a parameterized query you would have avoided this error.
(Not to mention the famigerate Sql Injection problem)

Using conn = New OleDbConnection(Get_Constring)
Using cmd = new OleDbComman()
    conn.Open()
    cmd.Connection = conn
    sSQL = "SELECT Username, lname + ', ' + fname + ' ' + mname as name, password FROM Instructor"
    If Me.cboSearchBy.Text = "Name" Then
       sSQL = sSQL & " where lname + ', ' + fname + ' ' + mname like ? and  level like ?"
    Else
       sSQL = sSQL & " where Username = ? and  level like ?"
    End If
    cmd.CommandText = sSQL
    cmd.Parameters.AddWithValue("@1", "%" & txtSearch.Text & "%")
    cmd.Parameters.AddWithValue("@2", "%instructor%")
    Using da = new OleDbDataAdapter(cmd)
       da.Fill(dt)
       Me.dtgResult.DataSource = dt
       If dt.Rows.Count = 0 Then
           MsgBox("No record found!")
       End If
    End Using
End Using

Also, if you are using an MS-Access database keep in mind that PASSWORD is a reserved keyword and you need to encapsulate it between square brackets when used in query like the one above.

SELECT ......., [Password] ........

Thanks, sorry for bad code :) im still new to this language and confused on what codes it should :) — user2715202, Oct 04 '13 at 13:55
ahhh, my bad :D, its working!! :D im happy since im currently developing computerized grading system with multi user :D its really my first time to used vb.net hehe really thanks Mr. Steve :) — user2715202, Oct 04 '13 at 14:14

syntax error missing operator in query expression "Username = and level like '%instructor%'

1 Answers1