MS Access数据库分页+搜索怎么办? [英] How to do MS Access database paging + search?
问题描述
我有一个MS Access 2003数据库,该数据库的表名为product1
,主表名为Product Code
.没有自动ID列.
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.
我已使用此sql进行自定义数据分页.
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?
推荐答案
我也正在通过C#查询Access(具有分页和搜索功能),并且正在使用以下代码构建所有查询:
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);
请注意,要使其正常工作,必须提供所有参数
(如果您实际上不想过滤任何内容,只需将1=1
放在WHERE
子句中)
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)
这篇关于MS Access数据库分页+搜索怎么办?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!