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

Wrong number of arguments used with function in query expression

Syntax error in FROM clause. contents of the database

@djv , if I omit all codes from and after "or isnull" then there is no error. — roy
– roy, Commented Oct 7, 2021 at 8:24
well that check is checking a parameter you're passing from your code so you can do that logic check in code instead of on the server — djv
– djv, Commented Oct 7, 2021 at 15:26
@djv I use a local database so not from the server. Tomorrow I will screenshot the contents of the database — roy
– roy, Commented Oct 7, 2021 at 15:40

djv · Accepted Answer · 2021-10-21 15:22:46Z

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

edited Oct 21, 2021 at 15:22

answered Oct 6, 2021 at 15:58

djv

15.8k7 gold badges53 silver badges81 bronze badges

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

6 Comments

roy Over a year ago

,thanks for your reply, but there is an error. if I use full of your code then error "Wrong number of arguments used with function in query expression"

djv Over a year ago

@roy see my edit

roy Over a year ago

I add my code but still problem

djv Over a year ago

@roy you will need to be more specific

roy Over a year ago

I have updated my specific code

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related