I'm getting an error: Syntax error (missing operator) in query expression in vb

Question

This is the SQL.

dtaadpResult.SelectCommand.CommandText = " SELECT * FROM tblResult WHERE EventID =" & EventID(Counter1) & " AND ORDER BY Time DESCENDING"

It happens when I attempt to fill the dataset.

dtaadpResult.Fill(dtasetSD, "tblResult")

I'm using MS access, do I add paranthesis? How would I do that.

Dai · Accepted Answer · 2015-04-02 00:35:55Z

Your code is potentially vulnerable to SQL injection. Use parameters instead of string concatenation.

With that obligatory warning out of the way, look at your query text after formatting it:

SELECT
    *
FROM
    tblResult
WHERE
    EventID = @eventId
    AND
ORDER BY
    Time DESCENDING

The problem is you have an AND statement followed by ORDER BY, when you should have a predicate clause instead.

Either add another clause, or remove the AND operator keyword.

Also, more protips:

Avoid hungarian notation and consider using plurals for table names (i.e. use Results instead of tblResult.
Long lines of SQL embedded in code are hard to read, consider formatting your SQL and using multi-line strings instead.
USE PARAMETERISED QUERIES, NOT STRING CONCATENATION to generate SQL
If you're executing a SELECT query and passing it to a DataAdapter just to fill a DataTable or DataSet you're wasting memory and CPU cycles, instead use a DataReader instead: it's much faster (and with a considerably lower memory overhead).

1 Answer 1