This code works fine in Sql Server. it's for pagination.
but it throws error in Microsoft access.
The first error: Invalid Sql statement; expected DElETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'
when I remove this first three line
declare @RecordCount int
declare @PageSize int=4
declare @PageIndex int=3
the error change to: Syntax error in FROM clause
What is wrong?
declare @RecordCount int
declare @PageSize int=4
declare @PageIndex int=3
Select @RecordCount= Count(*) From tbl_product
SELECT *
FROM (
SELECT Top (@PageSize) sub.*
FROM (
SELECT TOP ((@RecordCount) - (((@PageIndex) - 1) * (@PageSize))) tbl_product.*
FROM tbl_product
ORDER BY tbl_product.pd_id Desc
) sub
ORDER BY sub.pd_id Asc
) subOrdered
ORDER BY subOrdered.pd_id
Sql Server and Microsoft Access are different. I doubt you'll be able to just take a SQL Server procedure and just copy it over to Access (if I remember you can have parameters which are declare differently but you don't have local variables).
Sql Server and Microsoft Access are different. I doubt you'll be able to just take a SQL Server procedure and just copy it over to Access (if I remember you can have parameters which are declare differently but you don't have local variables).
And as an addition, in Access, parameters cannot be used in the TOP clause. Also Acces doesn't support batched statements
In order to achieve what you want in Access, you need to create the query in code, (beware of SQL injections!)
SELECT *
FROM (
SELECT Top 4 sub.*
FROM (
SELECT TOP 11 tbl_product.*
FROM tbl_product
ORDER BY tbl_product.[pd_id] Desc
) sub
ORDER BY sub.[pd_id] Asc
) subOrdered
ORDER BY subOrdered.[pd_id]
but they don't work when i write them after each other.
so when i cant pass parameter to top clause i can't use it for dynamic pagination.
Member
102 Points
247 Posts
Syntax error in FROM clause
Oct 04, 2013 06:32 AM|uid571490|LINK
Hi,
This code works fine in Sql Server. it's for pagination.
but it throws error in Microsoft access.
The first error: Invalid Sql statement; expected DElETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'
when I remove this first three line
declare @RecordCount int
declare @PageSize int=4
declare @PageIndex int=3
the error change to: Syntax error in FROM clause
What is wrong?
what is my worng?
tanx
All-Star
52813 Points
15770 Posts
Re: Syntax error in FROM clause
Oct 04, 2013 06:44 AM|oned_gk|LINK
(*) From tbl_product;
Suwandi - Non Graduate Programmer
All-Star
48730 Points
18190 Posts
Re: Syntax error in FROM clause
Oct 04, 2013 06:54 AM|PatriceSc|LINK
Hi,
Sql Server and Microsoft Access are different. I doubt you'll be able to just take a SQL Server procedure and just copy it over to Access (if I remember you can have parameters which are declare differently but you don't have local variables).
All-Star
25756 Points
7014 Posts
Re: Syntax error in FROM clause
Oct 04, 2013 07:15 AM|hans_v|LINK
And as an addition, in Access, parameters cannot be used in the TOP clause. Also Acces doesn't support batched statements
In order to achieve what you want in Access, you need to create the query in code, (beware of SQL injections!)
Member
102 Points
247 Posts
Re: Syntax error in FROM clause
Oct 04, 2013 07:38 AM|uid571490|LINK
yes hans_v you are right.
this two query work fine seperately
but they don't work when i write them after each other.
so when i cant pass parameter to top clause i can't use it for dynamic pagination.
All-Star
25756 Points
7014 Posts
Re: Syntax error in FROM clause
Oct 04, 2013 07:48 AM|hans_v|LINK
Correct, in order to do that, you need to create the query dynamically in code behind.