1

I have a MS Access 2003 database with a table called product1 with a Primary key named Product Code. There is no auto id column.

I have used this sql to do the custom data paging.

 SELECT *
FROM (
  SELECT Top 1  -- = PageSize
  *
  FROM
  (
   SELECT TOP 1  -- = StartPos + PageSize
   *
   FROM product1
   ORDER BY product1.[Product Code]
  ) AS sub1
  ORDER BY sub1.[Product Code] DESC
 ) AS clients
ORDER BY [Product Code]

Now my problem is Search. When I search for something on the database table and point it.

How can I make sure still paging works fine?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
kakopappa
  • 5,023
  • 5
  • 54
  • 73
  • I don't understud your query syntax but what abuout `where description_field like '%'+@var+'%'` – Saic Siquot Aug 02 '11 at 15:48
  • I don't think this will do the paging for you, this doesn't allow you to access a different page, it just allows you to show the first x number of results (i.e. page one only) Is your question how to do page 2/ page 3 etc? – AidanO Aug 02 '11 at 15:55

1 Answers1

5

I'm querying Access from C# as well (with paging and searching), and I'm using the following code to build all my queries:

var sb = new StringBuilder();
sb.Append("select {0} from {1}");
sb.Append(" where {3} in (");
sb.Append("select top {4} sub.{3}");
sb.Append("    from (");
sb.Append("          select top {5} tab.{3}");
sb.Append("          from {1} tab");
sb.Append("          where {2}");
sb.Append("          order by tab.{3}");
sb.Append("    ) sub");
sb.Append("    order by sub.{3} desc");
sb.Append(")");
sb.Append("order by {3}");

sql = string.Format(sb.ToString(), this.ColumnsToSelect, this.TableName, 
    this.WhereClause, this.OrderBy, this.PageSize, this.PageNum * this.PageSize);

Note that in order for this to work, all parameters must be supplied
(if you don't actually want to filter anything, just put 1=1 into the WHERE clause)

Christian Specht
  • 35,843
  • 15
  • 128
  • 182