为什么绑定源不能具有不同的功能？ [英] Why can't a binding source have a distinct function?

问题描述

我正在尝试将某些字段绑定到组合框和文本框。截至目前，我在使用Select distinct from语句时遇到了一个问题，即使用为填充数据源而创建的数据表创建绑定源。我得到了数据，但没有明确的值。我得到所有值，包括任何重复的类型数据。

 Dim sqlconn2 As New SqlConnection（sqlstring）
 Dim da2 As New SqlDataAdapter（select distinct Id，county ，[商家名称]，[营业执照号码]，[商家地址]来自商家，sqlconn2）
 sqlconn2.Open（）
 da2.SelectCommand.ExecuteNonQuery（）
 Dim newdts As New DataTable 
 da2.Fill（newdts）
 sqlconn2.Close（）
 bs2.DataSource = newdts 
 
 businessnamecombobox.DataSource = bs2 
 businessnamecombobox.DisplayMember = 商家名称
 businessnamecombobox.ValueMember =商家名称
 

我尝试了什么：



我试图使用带有bindingsource的过滤器。 bs2.filter，但它仍然没有给出明确的值。有什么建议？

解决方案

distinct 关键字适用于从表中选择的整个列集。在您的情况下，如果有不同县的公司名称相同，那么他们也将被选中。



在你的组合框中你没有看到县或其他相关信息因此它似乎是重复的。



您可以省略select语句中的所有其他列，只从表中检索商务名称：

  Dim  da2  As  新 SqlDataAdapter（ 从商家中选择不同的[商家名称]，sqlconn2）

或者，在组合框中添加显示其他详细信息，例如县，许可证编号等



如果您还想显示其他详细信息但是一旦你可以使用窗口功能列出商业名称：

选择Id，县，[商家名称]，[营业执照号码] ，[商家地址]来自
（选择Id，县，[商家名称]，[营业执照麻木呃]，[商家地址]，
 row_number（）OVER（由[商家名称]按ID划分）rn 
来自商家）a其中rn = 1 

I am trying to bind certain fields to combo boxes and textboxes. As of now, I am having an issue using Select distinct from statement with creating a binding source using the data table created to fill the data source. I get the data, but not distinct values. I get all values including any duplicate type data.

Dim sqlconn2 As New SqlConnection("sqlstring")
     Dim da2 As New SqlDataAdapter("select distinct Id, county, [business name], [business license number], [business address] from businesses", sqlconn2)
     sqlconn2.Open()
     da2.SelectCommand.ExecuteNonQuery()
     Dim newdts As New DataTable
     da2.Fill(newdts)
     sqlconn2.Close()
     bs2.DataSource = newdts

     businessnamecombobox.DataSource = bs2
     businessnamecombobox.DisplayMember = "business name"
     businessnamecombobox.ValueMember = "business name"

What I have tried:

I have tried to use a filter with the bindingsource. bs2.filter, but it still isn't giving distinct values. Any suggestions?

解决方案

The distinct keyword applies to the whole set of columns you are selecting from a table. In your case if there are companies with the same business names in different counties then they will be selected as well.

In your combobox you do not see counties or other related information therefore it seems that it is duplicated.

You can either omit all other column from the select statement and retrieve only busines name from you table:

Dim da2 As New SqlDataAdapter("select distinct [business name] from businesses", sqlconn2)

Or, add display additional details, such as county, license number, etc in your combobox

If you still want to display additional details but list the business name once you can use a window function:

select Id, county, [business name], [business license number], [business address] from 
(select Id, county, [business name], [business license number], [business address],
 row_number() OVER (partition by [business name] order by Id) rn
 from  businesses) a where rn = 1

这篇关于为什么绑定源不能具有不同的功能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！