Geek Answers Handbook

SQL statement working with SQL Database does not work with Access Database

I am creating a GUI in C # and I have the following line of code to get 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 (the .Fill (table) adapter is running) when used in an access database, but works fine in the sql database.

SQL hash generated by:

 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 received:

 Syntax error in query expression 'ID NOT IN (SELECT TOP 0 ID FROM Data ORDER BY ID)'.

Ive been trying to fix this for a few hours, but I was out of luck. It does not make sense why the same operator did not work in the access database. Any help is appreciated!

+4
source share
2 answers

The Access demo engine will throw an error with this part of your request.

 SELECT TOP 0 ID FROM Data ORDER BY ID

You can split this section and check it as a new Access request. Unfortunately, the error message is not very useful: "The SELECT statement contains a reserved word or argument name with an error or absence, or the punctuation is incorrect." And such a generic error message that the db mechanism gives you when it cannot accurately describe the problem.

Basically, it all comes down to the fact that you cannot do SELECT TOP 0 in Access SQL.

Also, as soon as you solve the problem with SELECT TOP 0 , you will need the ORDER BY in the outer query. Without ORDER BY rows returned by TOP 25 are arbitrary.

+4
source

To perform the paging function, you can:

  • Leave a NOT IN clause when pagestart is 0.
  • Use the SELECT TOP pagesize * FROM (SELECT TOP pagestart + pagesize * FROM X ORDER BY Condition) AS Alias ORDER BY Condition DESC . The important part is that the second ORDER BY is in the opposite direction of the first. You may need the final ORDER BY to get the right order, although the customer must be capable of it. Note that prior to Access 2007, the designer would change this view (part in parentheses) to [SELECT ...]. AS Alias [SELECT ...]. AS Alias , but now she remains.

There are many methods if they are unsatisfactory.

Less than the answer to your question and much more to be informative, in addition to what HansUp said about TOP 0 , unsupported, you may also encounter other differences between the syntax of SQL Server and Access. To facilitate this, you might want to take a look at the DB level parameter to use SQL-Server syntax. This is not ideal, but allows some syntax that usually does not work in Access. Remember that switching in the middle of a project can be problematic. See ANSI 89 and ANSI 92 syntax incompatibilities .

+3
source

More articles:


All Articles