No duplicates in combo from datasource

[freschx]

Hey all,

Newbie here..

I'm having an issue wherein my combobox is populated from a datasource (access database >> http://i51.tinypic.com/2mpbvh1.jpg ) and unwanted duplicates are shown.

-Example-
http://i51.tinypic.com/x5xd2f.jpg

How can I make it so no duplicates arise?

Also on another note...I'm having difficulties linking the 2nd combo (model) to the first (make).

I would like the program to function in such a manner that

>makeCombo is populated from the database
>the user selects the Make of the vehicle from makeCombo
>modelCombo is populated from the database according to the make selected
>the user selects the Model of the vehicle from modelCombo
>user hits search
>datagrid is populated with appropriate vehicles


This has been driving me insane so any help would be GREATLY appriciated

[jmcilhinney]

You should probably redesign your database. You should not have that Make and Model information in the in that table at all. You should have a Make table, which contains one record per make. That way, you simply display the contents of that table and there will be no duplicates. You would also have a Model table, with one record per model. Each Model record would also contain a MakeID, linking it to a record in the Make table. Your Vehicle table (or whatever it will be called) can then just contain a ModelID. There's no duplication of data and, when you have a Vehicle record, you can use the ModelID to get a Model record ad the MakeID of that to get a Make record. This is how relational databases work: via relations.

Once you've got your related Make and Model tables, the two ComboBoxes are easy to configure using parent/child data-binding. For an example, follow the CodeBank link in my signature and check out my post on the subject.

That said, if you ever do have a list like that and you need to get distinct values from one column/property, the easiest way is with LINQ, e.g.

Dim makes = From row In myDataTable.AsEnumerable() Select row.Field(Of String)("Make") Distinct

[freschx]

Ah thank you for your help jm, I feared (but deep down probably knew) a reworking of the database was needed.

Just to confirm, would the revised relational DB look something like this

MakeTable
-make(pk)

ModelTable
-model(pk), make(fk)

SeriesTable
-series(pk), model(fk), make(fk) weight, power, blah, blah

Once again thanks for the reply

[jmcilhinney]

Each table probably should have an AutoNumber column as the primary key. Other than that, the Series table doesn't need a foreign key from both Make and Model. As I said before, the Series table needs only a foreign key from the Model table, which will then lead you to the Make table. If you need all the data together you execute a query that joins Series to Model and Model to Make.

[freschx]

Awesome thanks!

I'll rework the database tonight and get cracking on the parent/child data binding tommorow

[freschx]

I have come up with a different solution that does not involve linked combos, instead the user will just search the model through a textbox.

I'm having a different issue now however and again it is driving me insane (the worst part is, its probably something very simple).

So I would like it so after clicking an item in the datagrid, the details update automatically. Like such -
http://i51.tinypic.com/1zf3qyo.jpg

The problem is, after running a search (or hitting 'show all') the label/details no longer change when selecting a new item in the datagrid.
Like such-
http://i54.tinypic.com/opcpbr.jpg

Heres the code (there isnt much, I'm not very experienced)

    Private Sub Sheet1BindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Validate()
        Me.Sheet1BindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.CardatabaseDataSet)

    End Sub

    Private Sub search_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'CardatabaseDataSet.Sheet1' table. You can move, or remove it, as needed.
        Me.Sheet1TableAdapter.Fill(Me.CardatabaseDataSet.Sheet1)

    End Sub

    Private Sub searchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchButton.Click

        If searchText.Text = "" Then
            Sheet1DataGridView.DataSource = Me.CardatabaseDataSet.Sheet1.Select("Make like '" & MakeIDComboBox.SelectedItem & "'")
        Else
            Sheet1DataGridView.DataSource = Me.CardatabaseDataSet.Sheet1.Select("Model like '" & searchText.Text & "'")
        End If
    End Sub

    Private Sub showAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles showAll.Click
        Sheet1DataGridView.DataSource = Me.CardatabaseDataSet.Sheet1
    End Sub

As always, any help would be met with massive thanks!

quick edit: just to double clarify , the search itself filters the datagrid perfectly as intended....it just seems to stop the details from updating when new items are selected which is my problem

[jmcilhinney]

Read the title of this thread and then ask yourself whether your latest question belongs here or in a new thread of its own.

[freschx]

Noted thanks