Syntax error (missing operator) in query expression in OleDb vb.net

Question

Please give me a solution.

I think I made the query code wrong

Private Sub PopulateDataGridView()
    Dim query = "select ITM,ITC,QOH,PRS FROM IFG (WHERE QOH > 0 AND ITM = @ITM OR ISNULL(@ITM, '') = '')"
    Dim constr As String = "provider=Microsoft.Jet.OLEDB.4.0; data source=C:\Users\ADMIN2\Desktop; Extended Properties=dBase IV"
    Using con As OleDbConnection = New OleDbConnection(constr)
        Using cmd As OleDbCommand = New OleDbCommand(query, con)
            cmd.Parameters.AddWithValue("@ITM", cbCountries.SelectedValue)
            Using sda As OleDbDataAdapter = New OleDbDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                dataGridView1.DataSource = dt
            End Using
        End Using
    End Using
End Sub

Syntax error in FROM clause. contents of the database

Answer 1

It's a question about SQL syntax, really, and not so much vb.net or oledb.

You had two WHERE clauses, which is invalid SQL. Change the second WHERE to AND

Dim query As String = "select ITM,ITC,QOH,PRS FROM IFG WHERE QOH > 0"
query &= " AND ITM = @ITM"

By the way, since strings are immutable in vb.net, you should not build a string like that (first assigning to, then adding to) when you so clearly can avoid it because every concatenation creates a new string in memory. You can either use &, a StringBuilder, or one long string. For example, taking advantage of vb.net syntax to make a multiline string, you can change the vb.net to

Dim query = "
select ITM,ITC,QOH,PRS 
FROM IFG 
WHERE QOH > 0 
AND ITM = @ITM"

which is [subjectively] much easier to read as a SQL query (add the proper parentheses based on your logic, of course!).

Based on your update, you need to add a parameter to the query. Here is a more or less complete example of a query with one parameter

Using con As New OleDbConnection("connection string")
    Dim query = "
        select ITM,ITC,QOH,PRS 
        FROM IFG 
        WHERE QOH > 0 
        AND ITM = @ITM"
    Using cmd As New OleDbCommand(query, con)
        cmd.Parameters.AddWithValue("@ITM", itmValue)
        Using rdr = cmd.ExecuteReader()
            For Each result In rdr.AsQueryable()
                ' do something with each result
            Next
        End Using
    End Using
End Using