访问数据库查询问题 [英] Access Database Query Issue

问题描述

Access 2003 Databse

ASP.NET 2

Win 2003 Server


目前我有一个只允许的搜索页面一次搜索一个关键字（人）。我认为通过多选列表框升级该选项会更好，这样你就可以一次搜索一对或更多的人，但实现这一目标的查询给了我一笔钱。我不确定如何继续，所以我希望有人可以帮助我。


我的简化查询如下：

Access 2003 Databse
ASP.NET 2
Win 2003 Server

Currently I have a search page that only allows a single keyword (person) to be searched at a time. I thought upgrading that option via a multiple selection listbox would be better so that you can search for a pair or more of people at a time but the query that makes this happen is giving me a run for my money. I?m unsure as how to proceed and so I?m hoping someone can help me out.

My somewhat simplified query is as follows:

展开 | 选择 | Wrap | 行号

推荐答案

嗯，我没有ASP .NET经验，但对于查询结构，帽子不应该打扰。 :-)


对于多个字段，您的方法可以，只需使用LIKE和％

用于完全匹配传递输入的字符串和< all>使用％。


对于muliple完全匹配您可以在将查询传递给Access之前在WHERE子句中构造IN（a，b，c，....）结构。

在Access中我经常使用（temp）帮助表例如，不同的值姓氏和YesNo字段。为了显示结果，我使用了原始表格的JOIN，其中包含了对于YesNo字段具有True标准的temptable。


得到这个想法？


Nic; o）

Hmm, I have no ASP .NET experience, but for querie constructions hat shouldn''t bother. :-)

For multiple fields your approach will be OK, just use a LIKE and %
For a "full match" pass the entered string and for the <all> use %.

For muliple "full matches" you can construct an IN(a, b, c, ....) structure in the WHERE clause before passing the query to Access.
In Access I often use a (temp) help table with the distinct values for e.g. a lastname and a YesNo field. For the display of the result I use a JOIN of the original table with the temptable that''s having a True criteria for the YesNo field.

Getting the idea ?

Nic;o)

就个人而言，我会尽可能选择In（）结构。不幸的是，它并没有处理多个Like条目。这是Nico的TempTable想法真正有用的地方（你可以在这里用多个OR来构建一个更复杂的SQL字符串）。他的解决方案总体上更好，因为它更具可扩展性，更好地处理大量这些标准。另一种方法是使用SubQueries代替TempTable。它具有在单个SQL字符串中处理整个查询的好处。 （有关SubQueries的更多信息，请参见 SQL中的子查询。）

Personally, I''d go for the In() structure where possible. Unfortunately it doesn''t handle multiple Like entries though. This is where Nico''s TempTable idea really helps (You could just build a more complicated SQL string here with multiple ORs instead). His solution is better overall as it''s more scalable and better handles large numbers of these criteria. Another alternative is to use SubQueries in place of the TempTable. It has the benefit of handling the whole query in a single SQL string. (More on SubQueries can be found in Subqueries in SQL.)

啊，感谢IN（）提示。这有很大帮助。使用它我可以循环选择？列表框项目并将它们写入IN（）语句，如下面的两个名称：

Ah, thanks for the IN() tips. That helps a lot. Using that I could loop through the ?selected? listbox items and write them into the IN() statement like the two names below:

展开 | 选择 | Wrap | 行号

这篇关于访问数据库查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！