很少采用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 来实现。类似如下所示：
<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嵌套方案（特别是当数据量大的时候），也是我下面将要讲解的。 如下图所示：假设有些滚动信息需要分页显示，则我们可以这样处理
1.1 解决Access 分页，首页或最后一页数据显示错误、混乱问题
<p><span>///</span><span><summary></summary></span><span><br><br> </span><span>///</span><span> 分页获取滚动消息数据<br><br> </span><span>///</span><span></span><span><br><br> </span><span>///</span><span><param name="pageSize"></span><span>一页显示数据条数</span><span></span><span><br><br> </span><span>///</span><span><param name="curPageSize"></span><span>当前页的数据在所有数据中位置（排序）</span><span></span><span><br><br> </span><span>///</span><span><param name="condition"></span><span>查询条件</span><span></span><span><br><br> </span><span>///</span><span><returns></returns></span><span>返回当前条件下的滚动消息</span><span></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>
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去查询设计里面执行下，就会弹出那个错误提示。所以要修改下上面的代码如下：
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 结果排序全部混乱了。
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用户，勾选"完全控制".
access如何设置验证规则Apr 10, 2024 am 10:59 AMAccess 验证规则是一种数据验证工具，用于确保数据符合特定条件，防止输入无效数据。设置验证规则的步骤：1. 选择要设置验证规则的字段；2. 打开“字段属性”对话框并切换到“查找”选项卡；3. 在“验证规则”字段中输入验证规则；4. 在“验证文本”字段中输入不符合规则时的错误消息；5. 单击“确定”保存更改。
microsoft access是什么软件Mar 03, 2023 am 11:37 AMmicrosoft access是由微软发布的关系数据库管理系统；它结合了MicrosoftJet Database Engine和图形用户界面两项特点，是Microsoft Office的系统程序之一。
access和trunk端口的区别是什么Oct 31, 2023 pm 05:59 PMaccess和trunk端口的区别：1、Access端口用于连接终端设备，提供单个VLAN的接入，而Trunk端口用于连接交换机之间，提供多个VLAN的传输；2、Access端口只传输属于指定VLAN的数据，而Trunk端口可以传输多个VLAN的数据，并使用VLAN标签进行区分。
access数据库的扩展名是什么Apr 10, 2024 am 11:10 AMAccess 数据库文件的扩展名为 .accdb，自 Microsoft Access 2007 起开始使用，用于识别包含结构化数据的容器文件，如表、查询和窗体。
vb中怎么连接access数据库Oct 09, 2023 am 11:38 AMvb中连接access数据库的步骤包括引用必要的命名空间、创建连接字符串、创建连接对象、打开连接、执行SQL语句和关闭连接。详细介绍：1、引用必要的命名空间，在VB项目中，首先需要引用“System.Data`和`Microsoft.Office.Interop.Access”命名空间，以便使用ADO.NET和Access相关的类和方法，可以在VB项目的引用中添加这些命名等等。
access中如何导入excel数据Apr 10, 2024 pm 01:44 PM将 Excel 数据导入 Access 的步骤：1. 打开 Access 数据库。2. 创建链接的表，选择 Excel 工作簿。3. 选择 Excel 表格。4. 映射字段。5. 选择数据选项。6. 导入数据。7. 管理链接的数据。
access是什么软件Apr 10, 2024 am 10:55 AMMicrosoft Access 是一款关系型数据库管理系统 (RDBMS)，用于存储、管理和分析数据。它主要用于数据管理、导入/导出、查询/报表生成、用户界面设计和应用程序开发。Access 优势包括易用性、集成数据库管理、强大灵活、与 Office 集成和可扩展性。
access数据库有什么功能Apr 10, 2024 pm 12:29 PMMicrosoft Access 是一款用于创建、管理和查询数据库的关系型数据库管理系统，提供以下功能：数据存储和管理数据查询和检索表单和报表创建数据分析和可视化关系数据库管理自动化和宏多用户支持数据库安全可移植性
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.
PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool
MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
WebStorm Mac version
Useful JavaScript development tools
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
