search
HomeDatabaseMysql TutorialAccess数据库问题锦集

很少采用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嵌套方案(特别是当数据量大的时候),也是我下面将要讲解的。 如下图所示:假设有些滚动信息需要分页显示,则我们可以这样处理

Access数据库问题锦集

 

 

 

 

 

 

 

 

 

 

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>

Access数据库问题锦集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数据库问题锦集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数据库问题锦集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用户,勾选"完全控制".

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are the different storage engines available in MySQL?What are the different storage engines available in MySQL?Apr 26, 2025 am 12:27 AM

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

What are some common security vulnerabilities in MySQL?What are some common security vulnerabilities in MySQL?Apr 26, 2025 am 12:27 AM

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

How can you identify slow queries in MySQL?How can you identify slow queries in MySQL?Apr 26, 2025 am 12:15 AM

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

How can you monitor MySQL server health and performance?How can you monitor MySQL server health and performance?Apr 26, 2025 am 12:15 AM

To monitor the health and performance of MySQL servers, you should pay attention to system health, performance metrics and query execution. 1) Monitor system health: Use top, htop or SHOWGLOBALSTATUS commands to view CPU, memory, disk I/O and network activities. 2) Track performance indicators: monitor key indicators such as query number per second, average query time and cache hit rate. 3) Ensure query execution optimization: Enable slow query logs, record and optimize queries whose execution time exceeds the set threshold.

Compare and contrast MySQL and MariaDB.Compare and contrast MySQL and MariaDB.Apr 26, 2025 am 12:08 AM

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

mPDF

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

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!