OFFSET command not recognized in Table Adapter Query c#

Question

I am attempting to build the following tableadapter query in Visual Studio 2019

SELECT * FROM Vendors
ORDER BY VendorID 
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY

And it gives the error "Unable to Parse Query Text" at the offset command I know this query works on the database itself as i can run it successfully on the sql server (2019 Express).

Will Visual Studio tableadapter queries not recognize the offset command? Or is the syntax different in some way?

Answer 1

TableAdapters are pretty old these days, but they do remain a serviceable data access strategy. The designer attempts to parse the query you entered and doesn't like it much with the extra offset parts.

I recommend you try:

• right click your dataset surface
• add >> tableadapter
• "select that downloads rows"
• put the query in as SELECT * FROM Vendors
• finish the wizard
• click the fill,getdata() line of the adapter
• in the properties grid, paste the extra clause onto the end of the query text
• say "no" to "do you want to update the other queries?"

You should be left with a usable adapter. See the footnote though

You have other options if you want to use a lot of syntax that designer doesn't like; most notably you can say "Create New Stored Procedures" when going through the wizard, put a base query of SELECT * FROM Vendors, VS will make the sprocs, and then you can just edit the OFFSET etc into the sproc command in SSMS

Footnote: I personally still use TAs a lot and have never needed such syntaxes, but generally I make the first query in a TA of the form SELECT * FROM x WHERE ID = @y so it'll only ever pull one row anyway and is very "plain SQL" - the datatable schema is driven from it and it "just works":

Then other queries have defined uses such as SELECT * FROM x WHERE Name LIKE ... - If you adopt this approach of making the firt query in a "normal" one that it can cope with, then you can certainly add another query with unsupported syntax - you get an error "unable to parse query text":

but you can ignore it and finish the wizard anyway, and it will work fine