missing operator in query expression vb.net

Question

I'm receiving the following error from the query below:

Syntax error (missing operator) in query expression 'LTC_FBS.DBS_NM = LTC_FBS_EMP.EMP_ID INNER JOIN LTC_FBS_REV on LTC_FBS.FBS_REV = LTC_FBS_REV.REV_I'.

     Dim sSQL As String = "SELECT FBS_ID, CLM_NUM, EMP_NM, REV_NM, DATE_SUBMIT FROM 
    LTC_FBS f INNER JOIN LTC_FBS_EMP e on f.DBS_NM = e.EMP_ID INNER JOIN LTC_FBS_REV r on 
   f.FBS_REV = r.REV_ID WHERE "

    If OptClaimNo.Checked = True Then 
        sSQL &= "CLM_NUM Like ('" & txtClaimNo.Text & "%')"

This is all the code:

        Private sConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\UA\FINANCE\LTC\MIS\Department\Databases\FBS.accdb"
Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click
    Me.lstResults.DataSource = Nothing
    Me.lstResults.Refresh()






    Dim OptControlArray() As Control = {OptClaimNo, OptDate, OptCustodian}


    Dim sSQL As String = "SELECT FBS_ID, CLM_NUM, EMP_NM, REV_NM, DATE_SUBMIT FROM LTC_FBS INNER JOIN LTC_FBS_EMP on LTC_FBS.DBS_NM = LTC_FBS_EMP.EMP_ID INNER JOIN LTC_FBS_REV on LTC_FBS.FBS_REV = LTC_FBS_REV.REV_ID WHERE "
    sSQL &= "CLM_NUM Like ('" & txtClaimNo.Text & "%')"
    ' If OptClaimNo.Checked = True Then
    'sSQL &= "CLM_NUM Like ('" & txtClaimNo.Text & "%')"

    'sSQL &= "Last Like ('" & txtLastName.Text & "%') AND First Like ('" & txtFirstName.Text & "%')"
    ' ElseIf OptCustodian.Checked = True Then
    ' sSQL &= "DBS_NM Like ('" & cboCustodian.SelectedValue & "%')"
    'ElseIf OptDate.Checked = True Then
    ' sSQL &= "DATE_SUBMIT = ('" & txtDateRec.Text & "')"
    ' End If

    Dim conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
    conn.Open()

    Dim dat As OleDbDataAdapter = New OleDbDataAdapter(sSQL, conn)
    Dim dt As New DataTable()

    dat.Fill(dt)
    dat.Dispose()

    If dt.Rows.Count <> 0 Then
        lstResults.DataSource = dt
        Me.lstResults.Columns("FBS_ID").Visible = False
        Me.lstResults.Columns("REV_NM").Width = 125
        Me.lstResults.Columns("EMP_NM").Width = 100
        Me.lstResults.Columns("DATE_SUBMIT").Width = 150

    Else
        MsgBox("No records were found.")
    End If

    dt.Dispose()
    conn.Close()

   End Sub
   End Class

The select statement and your error message don't match. Can you verify that the error is occurring with the statement you have specified and if, so, update the error message? — competent_tech
you mean where rev_i gets cut off? it's throwing the error at dat.fill where it goes to run this. This is essentially the only code i have behind the form. — gfuller40
Your code is vulnerable to sql injection. This is a big deal. Please fix it. — Joel Coehoorn

competent_tech competent_tech · Accepted Answer · 2011-12-09T20:14:04

The problem is the two inner joins. You need to surround the first one with parens in order for access to process it correctly.

Change:

Dim sSQL As String = "SELECT FBS_ID, CLM_NUM, EMP_NM, REV_NM, DATE_SUBMIT FROM LTC_FBS INNER JOIN LTC_FBS_EMP on LTC_FBS.DBS_NM = LTC_FBS_EMP.EMP_ID INNER JOIN LTC_FBS_REV on LTC_FBS.FBS_REV = LTC_FBS_REV.REV_ID WHERE "

to:

Dim sSQL As String = "SELECT FBS_ID, CLM_NUM, EMP_NM, REV_NM, DATE_SUBMIT FROM (LTC_FBS INNER JOIN LTC_FBS_EMP on LTC_FBS.DBS_NM = LTC_FBS_EMP.EMP_ID) INNER JOIN LTC_FBS_REV on LTC_FBS.FBS_REV = LTC_FBS_REV.REV_ID WHERE "

missing operator in query expression vb.net

1 Answers