MS Access Error: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Question

Hi all,

[Update: The query works if I hardcode in the parameters - so it has to do with the way I am adding parameters to the query]

For the life of me, I cannot figure out what the problem is here.

Here is the query being passed to the datareader:

    SELECT * FROM (SELECT TOP ? StartDate, [ID] FROM
    (SELECT TOP ? StartDate, [ID] FROM Story 
    ORDER BY StartDate DESC, [ID] DESC) AS foo 
    ORDER BY StartDate ASC, [ID] ASC) AS bar 
    INNER JOIN Story AS t ON bar.ID = t.ID 
    ORDER BY bar.StartDate DESC, bar.[ID] DESC

The parameters are added in the following order:

var pNumToRetrieve = new OleDbParameter("", OleDbType.Integer) {Value = numToGet};
var pResultSet = new OleDbParameter("", OleDbType.Integer) {Value = resultSet};

_cmd.Parameters.Add(pNumToRetrieve);
_cmd.Parameters.Add(pResultSet);

If I enter this query into access directly it works just fine. However, when running the query from ASP.NET, I get the following error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

What am I doing wrong?

Thanks,

Adam

Answer 1

I notice that not all of your ID columns have square braces around them. This could be it but it's been a while since I've played with Access so someone else may have a better answer.

EDIT:

As you've had no luck so far I'll attempt another guess. Could you try changing the "*" to instead explicitly name the columns you want.

FINAL EDIT:

The last piece of advice I have to offer is to replace the query with a very simple version of itself that produces no results. Then give it a test, if it fails it must be the parameter code that is failing, if not then it's something in the complexity of the query. If you gradually reconstruct the query you should then be able to detect the fail point. Sorry I can't be more help though.

Answer 2

Is it that the pResultSet parameter is typed as OleDbTypeInteger?

Answer 3

Does it like the order by in the subqueries? That isn't supported in SqlServer.

Edit : My mistake, I missed the Top ?

Answer 4

See if changing the query to this way helps.

PARAMETERS numToGet number, rowsToGet numeric;
SELECT * FROM (SELECT TOP numToGet StartDate, [ID] FROM
    (SELECT TOP rowsToGet StartDate, [ID] FROM Story 
    ORDER BY StartDate DESC, [ID] DESC) AS foo 
    ORDER BY StartDate ASC, [ID] ASC) AS bar 
    INNER JOIN Story AS t ON bar.ID = t.ID 
    ORDER BY bar.StartDate DESC, bar.[ID] DESC

EDIT: On a side note, what is the value being passed using those 2 parameters? Are both numbers?

Answer 5

The N in TOP N queries in Jet SQL cannot be parameterized, period. You have to write your SQL string on the fly to get a variable N. This means either that you can't use a saved QueryDef, or that you have to edit the SQL of the QueryDef and save it before using it.

Answer 6

Consider rewriting you TOP N constructs using a correlated subqueries.

Here's a simple example. Consider a table named Sequence with a column (seq) of unique INTEGERs (a standard SQL auxiliary table which is useful in countless situations -- every database should have one!)

Both the following queries return two the highest values for seq:

1)

SELECT TOP 2 seq 
  FROM SEQUENCE
 ORDER 
    BY seq DESC;

Pros: The Access database engine performs this relatively well (as you would expect for proprietary syntax). Cons: Proprietary syntax therefore bad for portability. The N (as in TOP N) cannot be parametrized. For me, using DESC in the ORDER BY to return the highest values is counter-intuitive.

2)

SELECT S1.seq 
  FROM SEQUENCE AS S1
 WHERE 2 >= (               
             SELECT COUNT(*)          
              FROM SEQUENCE AS S2       
             WHERE S2.seq >= S1.seq 
            );

Pros: Standard SQL syntax therefore good for portability. The N can be parametrized. Cons: The Access database engine does not optimize correlated subqueries well therefore performance will degrade as the number of rows in the table increases (as ever with performance issues, you will need to test it). Some SQL coders find a correlated subquery hard to understand and therefore has potential maintenance issues.