SQL Statement that worked with SQL Database doesnt work with Access Database
c#, ms-access, select, sql, syntax-error
Solution
The Access db engine will throw an error with this part of your query.
SELECT TOP 0 ID FROM Data ORDER BY ID
You can break out that section and test it as a new Access query. Unfortunately, the error message is not very helpful: "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." And that's sort of a generic error message the db engine gives you when it's unable to describe the problem precisely.
Basically, it all boils down to the fact you can not do `SELECT TOP 0` in Access SQL.
Also, once you resolve the problem about `SELECT TOP 0`, you need an `ORDER BY` clause in the outer query. Without the `ORDER BY`, the rows returned by `TOP 25` is arbitrary.
Problem
I am creating a GUI in C# and I have the following line of code to get the elements from lowerPageBound to upperPageBound. ``` command.CommandText = "Select Top " + rowsPerPage + " " + CommaSeparatedListOfColumnNames + " From " + tableName + " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " + lowerPageBoundary + " " + columnToSortBy + " From " + tableName + " Order By " + columnToSortBy + ") Order By " + columnToSortBy; adapter.SelectCommand = command; DataTable table = new DataTable(); table.Locale = System.Globalization.CultureInfo.InvariantCulture; adapter.Fill(table); ``` The generated SQL statement gives me an error(adapter.Fill(table) is executed) when used on an access database but works fine on a sql database. Heres the SQL that is generated: ``` Select Top 25 [ID], [Business Process], [Tier Level], [Application], [CI ID], [Server], [Server Function], [Data Center], [HA], [DR Equip], [Procedure], [Procedure Tested], [Type], [Outcome], [Overall Status] From Data WHERE ID NOT IN (SELECT TOP 0 ID FROM Data ORDER BY ID) ORDER BY ID; ``` And the error I recieve: ``` Syntax error in query expression 'ID NOT IN (SELECT TOP 0 ID FROM Data ORDER BY ID)'. ``` Ive tried to fix this for hours but I've had no luck. It doesn't make sense why the same statement wouldnt work on an access database. Any help is appreciated!!