Combobox, datagrid and duplicates

[rakker]

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?

[techgnome]

How are you storing your data? Database? File? Excel? If database, what kind? Access? SQL Server? Oracle?

-tg

[rakker]

Storing in an access database.

[incidentals]

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

[incidentals]

would be good to know the table contents dictionary

[kevininstructor]

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

SELECT 
    Identifer, 
    CompanyName, 
    ContactName, 
    ContactTitle, 
    Address, 
    City, 
    PostalCode, 
    Country 
FROM Customer

Here is a simple example

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

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.

ComboBox1.DisplayMember = "CompanyName"
ComboBox1.ValueMember = "Identifier"
ComboBox1.DataSource = DistinctCompanies

[EricPeterson87]

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.

[make me rain]

no it is not in this way,
first of all you need not to store company name & name both in combobox

Company1 james
company1 john
company2 johnnyB
Company1 anne
company3 joan
company4 rick

instead store only company name.

next

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

[make me rain]

i think Kevins post suits you fine

[rakker]

Haven't got that much code to show. I used the built in database connection of VS2010.

 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.

[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?

[kevininstructor]

@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.

[rakker]

Will do, thanks for now.

[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

[kevininstructor]

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.

[rakker]

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.

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.

  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.

[kevininstructor]

For automatic you may have the following generated via the IDE

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

Dim dt = CType(CustomerBindingSource.DataSource, Database1DataSet).Customer

[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.

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.

[kevininstructor]

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.

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.

[rakker]

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?

[rakker]

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.

    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.

   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 :-).

[kevininstructor]

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.

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

[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.?

[kevininstructor]

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.

Firm	firstname   lastname	Identifier
a1	john	       steward        200
a1	jim 	       jackson         123
a2	jack 	       black            89
a1	john 	       johnson         345

[rakker]

Good idea, thank you for that, didn't thought of doing it this way.