TableAdapter and ROW_NUMBER problem

microsoft.public.dotnet.framework.adonet

24 Feb 2006 8:55 AM

rpuertas

Hello,

Visual Web Developer 2005 Express Edition and SQL Server 2005 Express: I
want to run a paged query within a TableAdapter. In the TableAdapter Query
Configuration Wizard" I write the next query:

SELECT usu_id, usu_name, usu_password
FROM     (SELECT TOP (@StartRowIndex + @NumRows - 1) ROW_NUMBER() OVER
(ORDER BY usu_name DESC)
             AS row, usu_id, usu_name, usu_password FROM users)
            AS users_rows
WHERE row between @StartRowIndex AND @StartRowIndex + @NumRows - 1

but the next error apperas:

"The wizard detected the following problems when configuring TableAdapter
query "FillBy":
Details:

Generated SQL statement.

The OVER SQL construct or statement is not supported.

But, if I run the query from within Microsoft SQL Server Management Studio
Express, It runs Ok

¿Where is the problem? ¿It's there any solution?

Thanks.

24 Feb 2006 4:46 PM

Mohammad Samara

I'm not sure if ROW_NUMBER is supported within the application,
however, most of my application queries use ROW_NUMBER by having the
SQL statements in stored procedures, that is easier to maintain and
faster to execute.