-
Combobox, datagrid and duplicates
My last post somehow went wrong so i try it again.
I have a form with just one combobox and a datagrid view.
What i would like to do are 2 things.
At first i would like to remove all duplicate records from the combobox that are loaded in when opening the form. How do i do this?
Second those duplicates are names of companies that have different contacts to me.
So I would like to be able to choose one of the names in the combobox which has for example 3 contacts, and show these contacts in the data grid.
Company1 james
company1 john
company2 johnnyB
Company1 anne
company3 joan
company4 rick
etc.
If i selelct company1 the datagrid should show the names james, john and anne.
How do i do this in a simple way?
-
Re: Combobox, datagrid and duplicates
How are you storing your data? Database? File? Excel? If database, what kind? Access? SQL Server? Oracle?
-tg
-
Re: Combobox, datagrid and duplicates
Storing in an access database.
-
Re: Combobox, datagrid and duplicates
why program and not office application?
I know that is still a program but much can be done by office for you
why is the data in the combo box organised in this way ( have you inherited it)?
why a data grid for display?
are you putting other stuff into grid?
are you going to save this grid stuff?
here to help
-
Re: Combobox, datagrid and duplicates
would be good to know the table contents dictionary
-
Re: Combobox, datagrid and duplicates
Using MS-Access as a database (since you did not mention your data provider) using an OleDbConnection and OleDbCommand to load data into a DataTable, create a view (see below) which returns distinct values for a field from a database table.
Our intial SQL (returned via an OleDbCommand) into a DataTable
Code:
SELECT
Identifer,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
PostalCode,
Country
FROM Customer
Here is a simple example
Code:
Public Sub SimpleDemo()
Using cn As New OleDbConnection("Your connection string")
Dim cmd As OleDbCommand = New OleDbCommand
cmd.Connection = cn
cmd.CommandText = _
<SQL>
SELECT
Identifier,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
PostalCode,
Country
FROM Customer
</SQL>.Value
Dim dt As New DataTable With {.TableName = "Customers"}
cn.Open()
dt.Load(cmd.ExecuteReader())
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, "CompanyName"))
End Using
End Sub
Now dt has all fields and rows while DistinctCompanies has Distinct companies but having just the company names does not do much good unless we can have a pointer back to the database or the other data table so let's add the primary key
Code:
Dim ColNames() As String = {"Identifier", "CompanyName"}
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, ColNames))
In the ComboBox Identifier becomes the ValueMember, CompanyName the DisplayMember and DistinctCompanies the data source.
Code:
ComboBox1.DisplayMember = "CompanyName"
ComboBox1.ValueMember = "Identifier"
ComboBox1.DataSource = DistinctCompanies
-
Re: Combobox, datagrid and duplicates
use a hashtable or some other mechanism to track what is going into the combo box or better yet, if your datasource is a database, use SELECT DISTINCT or something.
the combobox should track the name and primary key of the company. once someone selects a company name, look up all contacts in the database with the selected ID value and display them in the grid.
-
Re: Combobox, datagrid and duplicates
no it is not in this way,
first of all you need not to store company name & name both in combobox
Quote:
Company1 james
company1 john
company2 johnnyB
Company1 anne
company3 joan
company4 rick
instead store only company name.
next
Quote:
If i selelct company1 the datagrid should show the names james, john and anne.
query the names basing on the company name criteria & show it in datagridview
better you show your code to the forum
-
Re: Combobox, datagrid and duplicates
i think Kevins post suits you fine
-
Re: Combobox, datagrid and duplicates
Haven't got that much code to show. I used the built in database connection of VS2010.
Code:
Private Sub form5_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Me.firmTableAdapter.Fill(Me.Kjelpasser2012DataSet.navn)
End Sub
I don't have both the firm name and the persons name in the same combobox. I just wrote this as an example.
So how do i communicate this to my datagrid and how do i prevent the duplicate records from my access database to show in the combobox?
I could give Kevin's tip a try of course.
-
Re: Combobox, datagrid and duplicates
@kevininstructor
Could you please explain how i can do this with the automatic function within Vs2010 as i use it?
I don't need to use the OLe Db command as you mention, how should i start my connection then?
-
Re: Combobox, datagrid and duplicates
Quote:
Originally Posted by
rakker
@kevininstructor
Could you please explain how i can do this with the automatic function within Vs2010 as i use it?
I don't need to use the OLe Db command as you mention, how should i start my connection then?
Best to look at the Data WalkThrough link in my signature for this.
-
Re: Combobox, datagrid and duplicates
-
Re: Combobox, datagrid and duplicates
Well this cleared up a few things, but it is still not clear on how to prevent duplicates in the combobox. used your code but i'm not sure how it works and what it does. The combobox is connected and fills the correct firm names but keeps on displaying the duplicate names :(
-
Re: Combobox, datagrid and duplicates
Quote:
Originally Posted by
rakker
Well this cleared up a few things, but it is still not clear on how to prevent duplicates in the combobox. used your code but i'm not sure how it works and what it does. The combobox is connected and fills the correct firm names but keeps on displaying the duplicate names :(
If you searched Google for DataView Google would suggest DataView Distinct and the second item in the results would be http://msdn.microsoft.com/en-us/libr...(v=VS.90).aspx which in the first several paragraphs explains what my code is doing Create a DataTable that includes only distinct rows from the DataView, analogously to the DISTINCT keyword in Transact-SQL.
My advice is when someone gives you a suggestion that you do not understand is to use Google or your fav search engine and research.
Developers use parts of the .NET Framework daily where some not only use but also study and understand what they use which is critical in development of a solution for many reasons ranging from tigher, better code to less time coding. Of course there will always be something that does not make sense even after reading the documentation which is why there are forums such as this one.
If we go back to my code and studying the parts as suggested using Google and you find that something is not clicking rather than coming back and saying "I do not understand this" come back and say something like "I do not understand the second parameter of ToTable" although this means you did not research ToTable so someone here might point you to this or if that link was not clear they may give you some insight to the method.
-
Re: Combobox, datagrid and duplicates
I searched with google, but did not find everything to be clear for me. As i'm using the automated database connection. I think i need to change the OLEDB connection you mentioned. How to asddept this to my use is not quite clear to me. I understand what you are doing now, but the connection is not clear to me. How do i rewrite the connection to the database so that it willl be suitable for my "automated" connection?
The select command is used to "pull" out the correct data from the database I use the datagridview that displays the data. If i select the data form the combobox it wil show the correct data this in the datagrid.
Code:
Public Sub SimpleDemo()
Using cn As New OleDbConnection("Your connection string")
Dim cmd As OleDbCommand = New OleDbCommand
cmd.Connection = cn
cmd.CommandText = _
<SQL>
SELECT
Identifier,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
PostalCode,
Country
FROM Customer
</SQL>.Value
Dim dt As New DataTable With {.TableName = "Customers"}
cn.Open()
dt.Load(cmd.ExecuteReader())
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, "CompanyName"))
End Using
End Sub
The next part is problematic because i can't connect the dots to my own database solution. cn.open opens the oleDB connection which i don't use. I need this but again it is not quite clear to me on how to rewrite this to my needs.
Code:
Dim dt As New DataTable With {.TableName = "Customers"}
cn.Open()
dt.Load(cmd.ExecuteReader())
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, "CompanyName"))
I used a manual database connection before in VB6, but i used the automated version now and it will be double work to rewrite the whole program to a manual connection.
:sick:
-
Re: Combobox, datagrid and duplicates
For automatic you may have the following generated via the IDE
Code:
Public Class Form1
Private Sub CustomerBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomerBindingNavigatorSaveItem.Click
Me.Validate()
Me.CustomerBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.Database1DataSet)
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Database1DataSet.Customer' table. You can move, or remove it, as needed.
Me.CustomerTableAdapter.Fill(Me.Database1DataSet.Customer)
End Sub
End Class
If so then to get to the DataTable we would use
Code:
Dim dt = CType(CustomerBindingSource.DataSource, Database1DataSet).Customer
-
Re: Combobox, datagrid and duplicates
Yes this code is roughly what I use.
I however don't see how I prevent duplicates in the combobox by using the code you presented earlier.
Code:
Dim dt As New DataTable With {.TableName = "Customers"}
cn.Open()
dt.Load(cmd.ExecuteReader())
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, "CompanyName"))
Only fills the data this works now in my datagridview, but does not indicate to that it prevents duplicates. Can you tell me how to prevent the duplicates in my combobox.
-
Re: Combobox, datagrid and duplicates
Quote:
Originally Posted by
rakker
Yes this code is roughly what I use.
I however don't see how I prevent duplicates in the combobox by using the code you presented earlier.
Code:
Dim dt As New DataTable With {.TableName = "Customers"}
cn.Open()
dt.Load(cmd.ExecuteReader())
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, "CompanyName"))
Only fills the data this works now in my datagridview, but does not indicate to that it prevents duplicates. Can you tell me how to prevent the duplicates in my combobox.
The variable DistinctCompanies returns only Distinct rows. You would understand this if you read the MSDN documentation on the code by picking it apart, this is how I learned by studying MSDN documentation. I did not simply give you code and thought it would work but instead gave you could I know works.
-
Re: Combobox, datagrid and duplicates
It took a while to find out how this works, but i'm not getting the select distinct to prevent duplicates. I think i'm quite over my head here :-(. Reading the MSDN does not solve my problem at this point unfortunately.
I keep getting duplicate files or no load at all. Any tips?
-
Re: Combobox, datagrid and duplicates
So i tried a bit, but it seems that i'm not getting any further.
I created a form with a DATAGRID that fills during load (the fill routine) this form has now 1 combobox which i want to load with 1 of the collumns in from the datagrid (the "Firm" collumn).
If i select a firm from my Combobox it needs to filter all the people that are registred for this firm.
Code:
Private Sub loaddistinct()
Dim dt = CType(Me.EmployeesBindingSource.DataSource, 2012DataSet).Employees
Dim colnames() As String = {"firm", "lastname"}
Dim employeesbindingsource = _
New DataView( _
dt.DefaultView _
.ToTable("employees", True, colnames))
End Sub
in my form load i did the following.
Code:
Private Sub form5_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the '2012DataSet1.Employees' table. You can move, or remove it, as needed.
Me.EmployeesTableAdapter.Fill(Me.2012DataSet1.Employees)
Call loaddistinct()
cmbselectfirma.DisplayMember = "firm"
cmbselectfirma.ValueMember = "firm"
cmbselectfirma.DataSource = EmployeesBindingSource
End Sub
Well this doesn't seem to do the trick my combobox is now displaying nothing at all :-(. Is there anyway to get a more usefull combobox :-).
-
1 Attachment(s)
Re: Combobox, datagrid and duplicates
Attached is a VS2010 project which opens a MS-Access 2007 table, shows all rows for a table in a DataGridView and also populates a ComboBox with Distinct rows for two columns in the underlying source table shown in the DataGridView.
If you are using VS2008 then simply create a new project, add the form from this project into that project and make it the main form. Copy the Data folder to your project, build and run.
As shown in the code below I access data in a different manner than you but the key is to work with the DataTable, nothing else matters.
Code:
Public Class frmDemo
WithEvents bsData As New BindingSource
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim FileName As String = IO.Path.Combine(Application.StartupPath, "Data\Database1.accdb")
Dim cb As New OleDb.OleDbConnectionStringBuilder With {.DataSource = FileName, .Provider = "Microsoft.ACE.OLEDB.12.0"}
Using cn As New OleDb.OleDbConnection With {.ConnectionString = cb.ConnectionString}
Using cmd As New OleDb.OleDbCommand With {.CommandText = "SELECT CompanyName,CompanyContact FROM Company", .Connection = cn}
Dim dt As New DataTable
cn.Open()
dt.Load(cmd.ExecuteReader)
bsData.DataSource = dt
DataGridView1.DataSource = bsData
End Using
End Using
LoadComboBox()
End Sub
Private Sub LoadComboBox()
Dim dt = CType(Me.bsData.DataSource, DataTable)
Dim colnames() As String = {"CompanyName", "CompanyContact"}
Dim dv = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, colnames))
ComboBox1.DisplayMember = "CompanyContact"
ComboBox1.ValueMember = "CompanyName"
ComboBox1.DataSource = dv
Label1.DataBindings.Add("Text", dv, "CompanyName")
End Sub
End Class
-
Re: Combobox, datagrid and duplicates
I got the combobox to select a firm.
I still have problems with getting the datagrid to reload and display only the selected firm + lastname + firstname
Firm firstname lastname
a1 john steward
a1 jim jackson
a2 jack black
a1 john johnson
a3 harry houdini
a4 larry king
a2 nonsmo king
Any tip on how to do this.?
-
Re: Combobox, datagrid and duplicates
Quote:
Originally Posted by
rakker
I got the combobox to select a firm.
I still have problems with getting the datagrid to reload and display only the selected firm + lastname + firstname
Firm firstname lastname
a1 john steward
a1 jim jackson
a2 jack black
a1 john johnson
a3 harry houdini
a4 larry king
a2 nonsmo king
Any tip on how to do this.?
If I am understanding you correctly your ComboBox shows properly. Now to show information for the selection in the ComboBox you will need a method to identifiy the selection which would be best done by adding the primary key to query information for the contact as shown below, simply edit which columns are pulled when populating your ComboBox.
Code:
Firm firstname lastname Identifier
a1 john steward 200
a1 jim jackson 123
a2 jack black 89
a1 john johnson 345
-
Re: Combobox, datagrid and duplicates
Good idea, thank you for that, didn't thought of doing it this way.:)