How to remove Duplicate Combobox & Load the value into Text box

2015-09-10

Question

_{Thursday, September 10, 2015 12:07 PM}

Dear all,

I have database like this

Manufacture Model No Length Width height

Manufacture 1 MD-01 200 300 100

Manufacture 1 MD-02 300 400 100

Manufacture 1 MD-03 400 300 150

Manufacture 2 MAD-01 200 450 100

Manufacture 2 MAD-02 250 400 100

Manufacture 3 MDI-01 300 300 100

Manufacture 4 MOv-01 350 300 100

I would like to create 2 combobox -

1) Manufacture 2 ) model No

Manufacture should display for above example Manufacture 1,Manufacture 2,Manufacture 3, Manufacture 4

when particular manufacture selected for example Manufacture 2 -> combox should display only MAD-01 & MAD 02.

Based on both selection text box must me loaded with length, width , height.

i tried sample code & try to assign the combox with database value. I found duplicate list of column. Like for combobox1 accumulate

Manufacture 1

Manufacture 2

Manufacture 3

Manufacture 4

Is there any example program available to do this. How can do this.

Link

AMPS12

All replies (9)

_{Thursday, September 10, 2015 12:50 PM ✅Answered}

Hello,

High level one method is to create a DataSet, load the data as per your question. Next setup a DataRelation for each level. From here create a BindingSource for the master table, set the data source to the DataSet and the DataMember to the main table. Repeat this for each relationship.

So for instance, I have a product, diamonds, for each type there is a cut and cuts have details. I have two relations and three BindingSources.

The code is simple as shown below, set the BindingSources up, set data sources for a ListBox, ComboBox and DataGridView (we could use one type of control for all but that does not make since.

Public Class StoneForm
    WithEvents bsProducts As New BindingSource
    WithEvents bsCuts As New BindingSource
    WithEvents bsPrices As New BindingSource

    Private Sub StoneForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim ds As New DataSet
        ds = LoadStoneData()
        bsProducts.DataSource = ds
        bsProducts.DataMember = ds.Tables(0).TableName

        bsCuts.DataSource = bsProducts
        bsCuts.DataMember = ds.Relations(0).RelationName

        lstProducts.DisplayMember = "Description"
        lstProducts.DataSource = bsProducts
        ComboBox1.DisplayMember = "Cut"
        ComboBox1.DataSource = bsCuts

        bsPrices.DataSource = bsCuts
        bsPrices.DataMember = ds.Relations(1).RelationName

        DataGridView1.DataSource = bsPrices
        DataGridView1.Columns("Identifier").Visible = False
        DataGridView1.Columns("ID").Visible = False
    End Sub
End Class

So there you have it, a pattern to work from. No need for navigation as selecting new items in the ListBox change the items in the ComboBox etc down the chain of controls bound to the data.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions. Microsoft Developer tools

_{Thursday, September 10, 2015 12:49 PM}

I don't know how you are populating your combo box, but I would use

SELECT DISTINCT Manufacture FROM [your table name] to get your first combobox data source

then on SelectedIndex.Changed event of your first combo box execute a query:

SELECT [Model No] FROM [your table name] and use that as the data source for your second combobox.

David M. Nichols software engineer

_{Thursday, September 10, 2015 12:50 PM}

Why not just display the columns (Manufacture, Model No, Length, Width, height) and rows in a DataGridView? Then write code for providing specific information if a row is selected? That would be the simplest method and the DataGridView keeps all info in one control and is professional looking. Plus the DGV can be provided a datasource such as a DataTable for assigning the DGV's column and row values. The DGV has scroll bars and can be hidden if desired when not in use.

Also I would recommend that instead of attempting to duplicate the program PVSYS demo if that is how PVSYS provides the information you are talking about that you implement some different methods for what you are doing. Such that there will not be any legal issues occuring by copying other peoples proprietary information or design in exact detail.

La vida loca

_{Friday, September 11, 2015 6:54 AM}

Hello,

High level one method is to create a DataSet, load the data as per your question. Next setup a DataRelation for each level. From here create a BindingSource for the master table, set the data source to the DataSet and the DataMember to the main table. Repeat this for each relationship.

So for instance, I have a product, diamonds, for each type there is a cut and cuts have details. I have two relations and three BindingSources.

The code is simple as shown below, set the BindingSources up, set data sources for a ListBox, ComboBox and DataGridView (we could use one type of control for all but that does not make since.
Public Class StoneForm
    WithEvents bsProducts As New BindingSource
    WithEvents bsCuts As New BindingSource
    WithEvents bsPrices As New BindingSource

    Private Sub StoneForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim ds As New DataSet
        ds = LoadStoneData()
        bsProducts.DataSource = ds
        bsProducts.DataMember = ds.Tables(0).TableName

        bsCuts.DataSource = bsProducts
        bsCuts.DataMember = ds.Relations(0).RelationName

        lstProducts.DisplayMember = "Description"
        lstProducts.DataSource = bsProducts
        ComboBox1.DisplayMember = "Cut"
        ComboBox1.DataSource = bsCuts

        bsPrices.DataSource = bsCuts
        bsPrices.DataMember = ds.Relations(1).RelationName

        DataGridView1.DataSource = bsPrices
        DataGridView1.Columns("Identifier").Visible = False
        DataGridView1.Columns("ID").Visible = False
    End Sub
End Class
So there you have it, a pattern to work from. No need for navigation as selecting new items in the ListBox change the items in the ComboBox etc down the chain of controls bound to the data.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions. Microsoft Developer tools

In your program where is LoadStone function Declared

AMPS12

_{Friday, September 11, 2015 9:21 AM}

The LoadStone is in a code module, could easily be in a class or form. One should avoid putting this code into a form so that the code can be shared by other forms or other projects. The key is for my example is having three tables with distinct data, after loaded into a DataSet the DataRelations are setup so that when they are contained with the BindingSources the controls will provide navigation through the data.

_{Friday, September 11, 2015 9:48 AM}

Is there any example program explaining details. There is no isssue creating database or allocating the dabase value. Problem faced pointing one combox to other,

For example :

actual database value Database.accdb

Manufacture Model No Length Width height

Manufacture 1 MD-01 200 300 100

Manufacture 1 MD-02 300 400 100

Manufacture 1 MD-03 400 300 150

Manufacture 2 MAD-01 200 450 100

Manufacture 2 MAD-02 250 400 100

Manufacture 3 MDI-01 300 300 100

Manufacture 4 MOv-01 350 300 100

After Using combo Box form should display

Combo_Manufacture Combo_Model No TextBox_length TextBox_width TextBox_Height

Manufacture 1 MD-01 200 300 100

MD-02 300 400 100

MD-03 400 300 150

Manufacture 2 MAD-01 200 450 100

MAD-02 250 400 100

SO on. This How it show in form With combobox. Can some share example code for this, With form Created

AMPS12

_{Friday, September 11, 2015 10:07 AM}

When I get to work I can upload the stones example to Microsoft OneDrive, remember it will not be your data but will show the logic. I need to tweak it, use an xml file rather than a database but the xml file will contain the same data as the database and should not matter as we are interested starting with the dataset and not how we actually got the data. Currently the data is in a SQL-Server database but could be from MS-Access, MySQL etc, doesn't matter.

When you download the project best to run through the code with the debugger and look at the dataset data and the data relationships.

What makes this work is to have a proper separation of data and not a flat design which is inefficient and much more work than it should be. Will be back with the download in about three hours which is when I am at work. Don't have it here at home.

_{Friday, September 11, 2015 12:54 PM}

Here is a mocked up example, on OneDrive using VS2013

https://onedrive.live.com/redir?resid=A3D5A9A9A28080D1!795&authkey=!AMqnWblc_VKv2ZU&ithint=file%2czip

_{Wednesday, September 16, 2015 5:02 AM}

By looking into some online example. Here is code , I am facing problem displaying the model no.

Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class Form1

    Dim dt As New DataTable
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\AccessDatabase\Manufacture.accdb;Persist Security Info=False;")
        Dim da As New OleDbDataAdapter("SELECT * FROM Solar_Panel", conn)
        da.Fill(dt)

        ComboBox1.DisplayMember = "manufacturer"
        ComboBox1.DataSource = dt.DefaultView.ToTable(True, "manufacturer")


    End Sub




    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        If ComboBox1.SelectedIndex = -1 Then Return
        Dim dv As New DataView(dt, "manufacturer = '" & ComboBox1.Text & "'", "", DataViewRowState.CurrentRows)
        ComboBox2.DisplayMember = "model"
        ComboBox2.DataSource = dv
        TextBox1.DataBindings.Clear()
        TextBox1.DataBindings.Add("Text", dv, "length")
        TextBox2.DataBindings.Clear()
        TextBox2.DataBindings.Add("Text", dv, "width")
        TextBox3.DataBindings.Clear()
        TextBox3.DataBindings.Add("Text", dv, "Height")
    End Sub


End Class

The model no displaying like this

My actual database look like this

AMPS12

שתף באמצעות

How to remove Duplicate Combobox & Load the value into Text box

Question

All replies (9)

משאבים נוספים