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
– competent_tech, Commented Dec 9, 2011 at 19:26
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
– gfuller40, Commented Dec 9, 2011 at 19:55
Your code is vulnerable to sql injection. This is a big deal. Please fix it. — Joel Coehoorn
– Joel Coehoorn, Commented Dec 28, 2011 at 4:27

competent_tech · Accepted Answer · 2011-12-09 20:14:04Z

3

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 "

answered Dec 9, 2011 at 20:14

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

missing operator in query expression vb.net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related