Access数据库问题锦集-mysql教程-PHP中文网

很少采用access做数据库开发，前段时间，朋友接了一个小单，让我帮忙做后台开发，采用access + asp.net开发，结果在开发过程使用access碰到不少问题，所以把这些问题总结一下，希望对其它人有些帮助 1： Access分页脚本问题使用Access作为数据库开发系统时

很少采用access做数据库开发，前段时间，朋友接了一个小单，让我帮忙做后台开发，采用access + asp.net开发，结果在开发过程使用access碰到不少问题，所以把这些问题总结一下，希望对其它人有些帮助

1： Access 分页脚本问题

使用Access作为数据库开发系统时，页面数据分页的方案有好几种：

1.1 使用DataGridView自带的分页功能.

1.2. 使用SELECT TOP NOT IN 来实现。类似如下所示：

NT80 购物系统

功能说明：1 会员可申请开店功能2 购买在线扣除金额3 冲值卡自动生成4 支持2级分类5 数据库压缩和备份6 会员分5个级别7 商品带讨论8 自带融合论坛，可关闭打开9 密码找回功能10 新闻``滚动新闻``帮助中心11 后台设置前台会员的上传权限12 可关闭/打开商店13 会员自助发布商品功能14 用户问题咨询管理

查看详情

<p><span>SELECT</span><span>TOP</span><span>100</span><span><br /><br />        </span><span>[</span><span>ID</span><span>]</span><span> ,<br /><br />        </span><span>[</span><span>Message</span><span>]</span><span> ,<br /><br />        </span><span>[</span><span>CreateDate</span><span>]</span><span><br /><br /></span><span>FROM</span><span>    ScrollMessage<br /><br /></span><span>WHERE</span><span>   ID </span><span>NOT</span><span>IN</span><span> ( </span><span>SELECT</span><span>TOP</span><span>900</span><span><br /><br />                            ID<br /><br />                    </span><span>FROM</span><span>    ScrollMessage<br /><br />                    </span><span>ORDER</span><span>BY</span><span> ID )<br /><br /></span><span>ORDER</span><span>BY</span><span> ID</span></p>

登录后复制

1.3．使用嵌套的TOP结合分页控件AspNetPager来实现

1.4. 设置一个自增长字段.并且该字段为INDEX.(网上有方案，我没有研究过这种方案)

其实上面几种方案中，我觉得效率最高的应该是使用TOP嵌套方案（特别是当数据量大的时候），也是我下面将要讲解的。如下图所示：假设有些滚动信息需要分页显示，则我们可以这样处理

Access数据库问题锦集

1.1 解决Access 分页，首页或最后一页数据显示错误、混乱问题

<p><span>///</span><span><summary></span><span><br /><br />        </span><span>///</span><span> 分页获取滚动消息数据<br /><br />        </span><span>///</span><span></summary></span><span><br /><br />        </span><span>///</span><span><param name="pageSize"></span><span>一页显示数据条数</span><span></param></span><span><br /><br />        </span><span>///</span><span><param name="curPageSize"></span><span>当前页的数据在所有数据中位置（排序）</span><span></param></span><span><br /><br />        </span><span>///</span><span><param name="condition"></span><span>查询条件</span><span></param></span><span><br /><br />        </span><span>///</span><span><returns></span><span>返回当前条件下的滚动消息</span><span></returns></span><span><br /></span><span><br />        </span><span>public</span><span> DataTable GetScrollMessage(</span><span>int</span><span> pageSize, </span><span>int</span><span> curPageSize, </span><span>string</span><span> condition)<br /><br />        {<br /><br />            </span><span>string</span><span> cmdText </span><span>=</span><span>string</span><span>.Format(</span><span>@"</span><span>SELECT * FROM<br /><br />                                               (SELECT TOP {0} * FROM<br /><br />                                                (<br /><br />                                                    SELECT TOP {1}  ID,  Message,CreateDate<br /><br />                                                    FROM ScrollMessage<br /><br />                                                    WHERE 1=1 {2}<br /><br />                                                    ORDER BY ID DESC<br /><br />                                                ) T<br /><br />                                                 ORDER BY T.ID ASC   <br /><br />                                                ) TT ORDER BY TT.ID DESC;        <br /><br />                                        </span><span>"</span><span>, pageSize, curPageSize, condition);<br /><br /> <br /><br /> <br /><br />            </span><span>return</span><span> DbHelper.ExecuteDataSet(cmdText).Tables[</span><span>0</span><span>];<br /><br /> <br /><br />        }</span></p>

登录后复制

Access数据库问题锦集 View Code

<p><span>private</span><span>void</span><span> BindGridView()<br />    {<br />        </span><span>//</span><span> 当前页数据显示个数</span><span><br /></span><span>int</span><span> pageSize </span><span>=</span><span> AspNetPager1.PageSize;<br /><br />        </span><span>//</span><span> AspNetPager 控件中当前显示的页</span><span><br /></span><span>int</span><span> pageIndex </span><span>=</span><span> AspNetPager1.CurrentPageIndex;<br />        </span><span>int</span><span> curPageSize </span><span>=</span><span>0</span><span>;<br />        MessageMangent module </span><span>=</span><span>new</span><span> MessageMangent();<br /><br />        </span><span>//</span><span>设置AspNetPager 的RecordCount属性</span><span><br /></span><span>        AspNetPager1.RecordCount </span><span>=</span><span> module.GetScrollMessageCount(</span><span>""</span><span>);<br /><br />        </span><span>if</span><span> (pageIndex </span><span>==</span><span> AspNetPager1.PageCount)<br />        {<br />            pageSize </span><span>=</span><span> AspNetPager1.RecordCount </span><span>-</span><span> (pageIndex </span><span>-</span><span>1</span><span>) </span><span>*</span><span> pageSize;<br /><br />            </span><span>if</span><span> (curPageSize </span><span>==</span><span>0</span><span>)<br />                curPageSize </span><span>=</span><span> AspNetPager1.RecordCount;<br /><br />        }<br />        </span><span>else</span><span><br />        {<br />            curPageSize </span><span>=</span><span> pageSize </span><span>*</span><span> pageIndex;<br />        }<br /><br />   <br /><br />        DataTable dtDataSource </span><span>=</span><span> module.GetScrollMessage(pageSize, curPageSize, </span><span>""</span><span>);<br /><br />        gvMessage.DataSource </span><span>=</span><span> dtDataSource;<br />        gvMessage.DataBind();<br />    }</span></p>

登录后复制

上面代码已经能解决分页数据错乱问题，但是其实里面还有个隐藏的bug，就是当数据表没有任何记录时，它会报错：“SELECT 子句中包含一个保留字、拼写错误或丢失的参数，或标点符号不正确”。可能有些人被这个人弄得莫名其妙，其实主要是TOP 0引起的，你用SELECT TOP 0 * FROM A去查询设计里面执行下，就会弹出那个错误提示。所以要修改下上面的代码如下：

Access数据库问题锦集 View Code

<p><span>private</span><span>void</span><span> BindGridView()<br />    {<br />        </span><span>//</span><span> 当前页数据显示个数</span><span><br /></span><span>int</span><span> pageSize </span><span>=</span><span> AspNetPager1.PageSize;<br /><br />        </span><span>//</span><span> AspNetPager 控件中当前显示的页</span><span><br /></span><span>int</span><span> pageIndex </span><span>=</span><span> AspNetPager1.CurrentPageIndex;<br />        </span><span>int</span><span> curPageSize </span><span>=</span><span>0</span><span>;<br />        MessageMangent module </span><span>=</span><span>new</span><span> MessageMangent();<br /><br />        </span><span>//</span><span>设置AspNetPager 的RecordCount属性</span><span><br /></span><span>        AspNetPager1.RecordCount </span><span>=</span><span> module.GetScrollMessageCount(</span><span>""</span><span>);<br /><br />        </span><span>if</span><span> (pageIndex </span><span>==</span><span> AspNetPager1.PageCount)<br />        {<br />            pageSize </span><span>=</span><span> AspNetPager1.RecordCount </span><span>-</span><span> (pageIndex </span><span>-</span><span>1</span><span>) </span><span>*</span><span> pageSize;<br /><br />            </span><span>if</span><span> (curPageSize </span><span>==</span><span>0</span><span>)<br />                curPageSize </span><span>=</span><span> AspNetPager1.RecordCount;<br /><br />        }<br />        </span><span>else</span><span><br />        {<br />            curPageSize </span><span>=</span><span> pageSize </span><span>*</span><span> pageIndex;<br />        }<br /><br />        </span><span>if</span><span> (curPageSize </span><span>==</span><span>0</span><span>&&</span><span> pageSize </span><span>==</span><span>0</span><span>)<br />        {<br />            curPageSize </span><span>=</span><span>1</span><span>;<br />            pageSize </span><span>=</span><span>1</span><span>;<br />        }<br /><br /><br />        DataTable dtDataSource </span><span>=</span><span> module.GetScrollMessage(pageSize, curPageSize, </span><span>""</span><span>);<br /><br />        gvMessage.DataSource </span><span>=</span><span> dtDataSource;<br />        gvMessage.DataBind();<br />    }</span></p>

登录后复制

1.2 SELECT TOP 失效问题，比如只需前十条记录，结果它给你查出了所有记录。这主要是查询TOP语句的后面使用Order by，而且Order by字段中有重复值的话，那么这个TOP很会失效而会返回所有记录,所以Order by后面最好用主键字段。我又一次就是犯了这个错误，查询语句如下所示，本来工作好好的，但是由于客户需求，把CreateDate字段从yyyy-MM-dd HH:mm 改成yyyy-MM-dd 结果排序全部混乱了。

Access数据库问题锦集 View Code

<p><span>string</span><span> cmdText </span><span>=</span><span>string</span><span> .Format(<br />                                </span><span>@"</span><span>SELECT * FROM <br />                                (<br />                                    SELECT TOP {0} * <br />                                    FROM (<br />                                            SELECT TOP {1} * FROM  Images I<br />                                            INNER JOIN BaseImgPage P ON I.PageID = P.PageID AND I.SubType = P.SubType <br />                                            WHERE I.PageID =@PageID AND I.SubType=@SubType</span><span>"</span><span>+</span><span> searchCondtion </span><span>+</span><span>@"</span><span><br />                                            ORDER BY CreateDate ASC<br />                                    ) ORDER BY CreateDate DESC<br />                                )  ORDER BY ImageSortNum DESC;</span><span>"</span><span>, pageSize, curpageSize);</span></p>

登录后复制

2 关键字问题，像password等，这样网上介绍很多了，想必大家都比较了解。这里就不多说了

3 错误提示"操作必须使用一个可更新的查询",部署到服务器上，插入或更新数据时，有时会爆出这样的错误

1.Win 2000/ Win 2003 系统，在数据库文件上(*.mdb)点鼠标右键，选择属性，点安全，查看是否存在everyone用户，如果没有，添加everone用户，勾选"完全控制".

2.Win XP 系统,打开 data 文件夹，点击“工具”->“文件夹选项”->“查看”，找到“使用简单文件共享（推荐）”这项，把前面的勾去掉。然后在数据库文件上(*.mdb)点鼠标右键，选择属性，点安全，查看是否存在everyone用户，如果没有，添加everone用户，勾选"完全控制".