Syntax error in FROM clause[Answered] RSS

• • Reply
  uid571490

  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?

  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

  what is my worng?

  tanx
  
  

• • Reply
  oned_gk

  All-Star

  52813 Points

  15770 Posts

  Re: Syntax error in FROM clause

  Oct 04, 2013 06:44 AM|oned_gk|LINK

  Set @RecordCount = Select Count
  (*) From tbl_product;
  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  PatriceSc

  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).

• • Reply
  hans_v

  All-Star

  25756 Points

  7014 Posts

  Re: Syntax error in FROM clause

  Oct 04, 2013 07:15 AM|hans_v|LINK

  PatriceSc

  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!)

• • Reply
  uid571490

  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

  SELECT Count (*) from tbl_product

  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.

  
  
  

• • Reply
  hans_v

  All-Star

  25756 Points

  7014 Posts

  Re: Syntax error in FROM clause

  Oct 04, 2013 07:48 AM|hans_v|LINK

  Bahare Feizi

  so when i cant pass parameter to top clause i can't use it for dynamic pagination.

  Correct, in order to do that, you need to create the query dynamically in code behind.