"选择与QUOT;来自数据表 [英] "Selecting" from a data table

问题描述

我有一个数据表，列出了我数据库中的一系列项目。在我的用户

表格中，我希望用户能够按照许多标准进行过滤（例如

位置，合同，修改日期等）。除了修改日期，我的所有

过滤器都是通过组合框选择的。我想让组合框

更新，这样如果给定选择的当前过滤的

列表中没有可用的项目，那么该选择将不会出现在名单。我想b * b也想做到这一点，以便过滤列表的过滤和更新

在客户端完成，而无需返回服务器。我正在寻找的

功能是这样的：


dt是项目的数据表。


location.datasource = SELECT DISTINCT location FROM dt

contract.datasource = SELECT DISTINCT合同FROM dt


有没有办法实现这个功能？ />

TIA

Ron L

解决方案

> dt是项目的数据表。

location.datasource = SELECT DISTINCT location FROM dt
contract.datasource = SELECT DISTINCT contract FROM dt

要过滤DataTable中的一系列内容，您可以这样做：


dt.DefaultView.RowFilter =" any where where where where where where ;


但是，我认为你的问题不同了。我认为你需要3个DataTables：


1.位置记录

2.合同记录

3.用户在表单上进行交互的记录。


在您的数据库中创建存储过程如下：


创建程序dbo.GetMyStuff （

@param INT - 或者其他什么，如果你需要参数

）AS

SELECT DISTINCT东西的位置FROM

SELECT DISTINCT合同的东西FROM FROM

选择你的表格的东西从哪里到哪里

GO


然后你设置一个SqlDataAdapter来调用你的存储过程，你使用一个

DataSet而不是一个DataTable：


Imports System.Configuration

Imports System.Data

Imports System.Data.SqlClient

....

''[Jedi]你正在存储.config文件中的连接字符串，不是吗。

Dim cnstr As String = _

ConfigurationSettings.AppSettings（" connectionStrin g"）

Dim cn As New SqlConnection（cnstr）

Dim ds As New DataSet（ ）

Dim da As New SqlDataAdapter（" dbo.GetMyStuff"，cn）


da.Fill（ds）


''现在您的DataSet中有三个DataTable：

''ds.Tables（0）... ds.Tables（1）... ds.Tables（2）

''哪一个是由存储过程中选择

''语句的顺序控制的：

''ds.Tables （0）是你的位置东西

''ds.Tables（1）是你的合约东西

''ds.Tables（2）是你的东西用于其他约束的东西在您的表单上


''如果您需要三个DataTables在DataSet中具有结构化关系

''，您可以创建一个指向<的$ DataRelation对象br />
''到一个DataTable中的父列和另一个中的子列。

''所以现在你的列表绑定更好：

location.DataSource = ds.Tables（0）

contract.DataSource = ds.Tables （1）


''等...


为满足您的过滤需求，这三个DataTable中的每一个都有一个DefaultView

属性，表示DataView对象。该属性有一个RowFilter

属性。


有关详细信息，请按CTRL + ALT + J并在对象浏览器中搜索DataView

-

Peace&快乐的计算，

Mike Labosh，MCSD


" Mr。 McKittrick经过非常仔细的考虑后，我得到了这个新系统SUCKS的结论。

- Barringer将军，战争游戏< br / >

2005年6月29日星期三14:43:36 -0400，Ron L写道：

我有一个列出a的数据表我的数据库中的一系列项目。在我的用户
表单中，我希望用户能够按照许多标准进行过滤（例如，
位置，合同，修改日期等）。除了修改日期之外，我的所有
过滤器都是通过组合框选择的。我想更新组合框
，以便在给定选择的当前过滤的列表中没有可用的项目时，该选择将不会出现在列表中。我也希望这样做，以便过滤列表的过滤和更新在客户端完成，而无需返回服务器。我正在寻找的功能是这样的：

dt是项目的数据表。

location.datasource = SELECT DISTINCT location FROM dt
contract.datasource = SELECT DISTINCT合同FROM dt

有没有办法实现这个功能？

TIA
Ron L

阅读DataViews。

Mike


感谢您的回复。这种方法的问题在于，当我应用

一个过滤器（比如位置）时，所有合约为Big Client

的记录可能已被过滤掉。在这种情况下，我希望能够更新

合约组合框以不再包含大客户，我想要这样做

而无需制作往返服务器。对不起，如果我在原帖中不清楚

。


Ron L

" Mike Labosh" <毫升***** @ hotmail.com>在消息中写道

新闻：％2 *************** @ TK2MSFTNGP15.phx.gbl ...

dt是项目的数据表。

location.datasource = SELECT DISTINCT location FROM dt
contract.datasource = SELECT DISTINCT contract FROM dt

要过滤DataTable中的一系列内容，您可以执行以下操作：

dt.DefaultView.RowFilter ="任何where子句，不包含

1.记录位置
2.合同记录
3.用户与之交互的记录你的表单。

在你的数据库中创建一个存储过程如下：

创建程序dbo.GetMyStuff（
@param INT - 或者其他什么，如果你需要的话参数
）
选择DISTINCT东西的位置FROM
SELECT DISTINCT东西的合同FROM从哪里
选择你的形式的东西从哪里什么地方
GO
然后设置一个SqlDataAdapter来调用你的存储过程并使用一个DataSet而不是一个DataTable：

Imports System.Configuration
Imports System.Data
导入System.Data.SqlClient
...
''[Jedi]你将连接字符串存储在.config文件中，不是你的意思。
Dim cnstr As String = _
ConfigurationSettings.AppSettings（" connectionStrin g"）

Dim cn As New SqlConnection（cnstr）
Dim ds As New DataSet（）
Dim da As New SqlDataAdapter（" dbo.GetMyStuff"，cn）

da.Fill（ds）

''现在你的DataSet中有三个DataTables：
''ds.Tables（0）... ds.Tables（1）... ds .Tables（2）
''哪一个是由存储过程中选择
'语句的顺序控制的：
''ds.Tables（0）是你的位置东西
''ds.Tables（1）是你的合同东西
'ds.Tables（2）是你表单上其他绑定东西的东西

''如果你需要三个DataTables在DataSet中有结构化的关系，你可以创建一个DataRelation对象，它指向一个DataTable中的父列和另一个Data列中的子列。
location.DataSource = ds.Tables（0）
contract.DataSource = ds.Tables（1）

''等..

为了满足您的过滤需求，这三个DataTable中的每一个都有一个DefaultView
属性，它表示一个DataView对象。该属性具有
RowFilter属性。

有关详细信息，请按CTRL + ALT + J并在对象浏览器中搜索DataView
-
Peace&快乐的计算，

Mike Labosh，MCSD

先生。 McKittrick经过非常仔细的考虑，我得出结论，这个新系统SUCKS。
- General Barringer，War Games"

I have a data table that lists a series of items in my database. In my user
form, I want the user to be able to filter by a number of criteria (e.g.
location, contract, date modified, etc). Other than modified date, all my
filters are selected via combo boxes. I would like to have the combo boxes
update so that if there are no items available in the currently filtered
list for a given selection, that selection will not appear in the list. I
also want to make it so that the filtering and updating of the filter lists
is done on the client, without needing to go back to the server. The
functionality I am looking for is something like this:

dt is the data table of items.

location.datasource = SELECT DISTINCT location FROM dt
contract.datasource = SELECT DISTINCT contract FROM dt

Is there some way to implement this functionality?

TIA
Ron L

解决方案

> dt is the data table of items.

location.datasource = SELECT DISTINCT location FROM dt
contract.datasource = SELECT DISTINCT contract FROM dt

To filter a series of stuff in a DataTable, you can do this:

dt.DefaultView.RowFilter = "any where clause without the word where"

However, I think your problem is different. I think you need 3 DataTables:

1. the records for location
2. the records for contract
3. the records for your user to interact with on your form.

Make a stored procedure in your database like this:

CREATE PROCEDURE dbo.GetMyStuff (
@param INT -- or whatever, if you need parameters
) AS
SELECT DISTINCT stuff for location FROM wherever
SELECT DISTINCT stuff for contract FROM wherever
SELECT stuff for your form FROM wherever WHERE whatever
GO

Then you set up a SqlDataAdapter to call your stored procedure and you use a
DataSet instead of a DataTable:

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
....
'' [Jedi] you''re storing the connect string in the .config file, aren''t you.
Dim cnstr As String = _
ConfigurationSettings.AppSettings("connectionStrin g")

Dim cn As New SqlConnection(cnstr)
Dim ds As New DataSet()
Dim da As New SqlDataAdapter("dbo.GetMyStuff", cn)

da.Fill(ds)

'' Now your DataSet has three DataTables in it:
'' ds.Tables(0)... ds.Tables(1)... ds.Tables(2)
'' Which one is which is controlled by the order of the select
'' statements inside the stored procedure:
'' ds.Tables(0) is your location stuff
'' ds.Tables(1) is your contract stuff
'' ds.Tables(2) is your stuff for other bound things on your form

'' If you need the three DataTables to have structured relationships
'' inside the DataSet, you can create a DataRelation object that points
'' to the parent column in one DataTable and the child column in the other.

'' So now your list bindings are nicer:
location.DataSource = ds.Tables(0)
contract.DataSource = ds.Tables(1)

'' etc...

For your filtering needs, each of those three DataTables has a DefaultView
property which represents a DataView object. That property has a RowFilter
property.

For more info, press CTRL+ALT+J and search the object browser for DataView
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"

On Wed, 29 Jun 2005 14:43:36 -0400, Ron L wrote:

I have a data table that lists a series of items in my database. In my user
form, I want the user to be able to filter by a number of criteria (e.g.
location, contract, date modified, etc). Other than modified date, all my
filters are selected via combo boxes. I would like to have the combo boxes
update so that if there are no items available in the currently filtered
list for a given selection, that selection will not appear in the list. I
also want to make it so that the filtering and updating of the filter lists
is done on the client, without needing to go back to the server. The
functionality I am looking for is something like this:

dt is the data table of items.

location.datasource = SELECT DISTINCT location FROM dt
contract.datasource = SELECT DISTINCT contract FROM dt

Is there some way to implement this functionality?

TIA
Ron L

Read up on DataViews.

Mike

Thanks for your response. The problem with that approach is that as I apply
one filter (say location), all of the records with contract of "Big Client"
may have been filtered out. In that case I want to be able to update the
Contract combo box to no longer include "Big Client", and I want to do it
without having to make a round trip to the server. Sorry if I wasn''t clear
on this in the original post.

Ron L
"Mike Labosh" <ml*****@hotmail.com> wrote in message
news:%2***************@TK2MSFTNGP15.phx.gbl...

dt is the data table of items.

location.datasource = SELECT DISTINCT location FROM dt
contract.datasource = SELECT DISTINCT contract FROM dt

To filter a series of stuff in a DataTable, you can do this:

dt.DefaultView.RowFilter = "any where clause without the word where"

However, I think your problem is different. I think you need 3
DataTables:

1. the records for location
2. the records for contract
3. the records for your user to interact with on your form.

Make a stored procedure in your database like this:

CREATE PROCEDURE dbo.GetMyStuff (
@param INT -- or whatever, if you need parameters
) AS
SELECT DISTINCT stuff for location FROM wherever
SELECT DISTINCT stuff for contract FROM wherever
SELECT stuff for your form FROM wherever WHERE whatever
GO

Then you set up a SqlDataAdapter to call your stored procedure and you use
a DataSet instead of a DataTable:

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
...
'' [Jedi] you''re storing the connect string in the .config file, aren''t
you.
Dim cnstr As String = _
ConfigurationSettings.AppSettings("connectionStrin g")

Dim cn As New SqlConnection(cnstr)
Dim ds As New DataSet()
Dim da As New SqlDataAdapter("dbo.GetMyStuff", cn)

da.Fill(ds)

'' Now your DataSet has three DataTables in it:
'' ds.Tables(0)... ds.Tables(1)... ds.Tables(2)
'' Which one is which is controlled by the order of the select
'' statements inside the stored procedure:
'' ds.Tables(0) is your location stuff
'' ds.Tables(1) is your contract stuff
'' ds.Tables(2) is your stuff for other bound things on your form

'' If you need the three DataTables to have structured relationships
'' inside the DataSet, you can create a DataRelation object that points
'' to the parent column in one DataTable and the child column in the other.

'' So now your list bindings are nicer:
location.DataSource = ds.Tables(0)
contract.DataSource = ds.Tables(1)

'' etc...

For your filtering needs, each of those three DataTables has a DefaultView
property which represents a DataView object. That property has a
RowFilter property.

For more info, press CTRL+ALT+J and search the object browser for DataView
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"

这篇关于&QUOT;选择与QUOT;来自数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！