Combobox, datagrid and duplicates-VBForums
Results 1 to 25 of 25

Thread: Combobox, datagrid and duplicates

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

    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?
    Last edited by rakker; Jan 9th, 2012 at 08:42 AM.

  2. #2
    PowerPoster
    Join Date
    May 2002
    Posts
    24,737

    Re: Combobox, datagrid and duplicates

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

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

    Re: Combobox, datagrid and duplicates

    Storing in an access database.

  4. #4
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    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

  5. #5
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

    Re: Combobox, datagrid and duplicates

    would be good to know the table contents dictionary

  6. #6
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,651

    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

  7. #7
    Hyperactive Member
    Join Date
    Jan 2012
    Location
    Salt Lake City
    Posts
    313

    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.

  8. #8
    Frenzied Member make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india
    Posts
    1,854

    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
    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
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  9. #9
    Frenzied Member make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india
    Posts
    1,854

    Re: Combobox, datagrid and duplicates

    i think Kevins post suits you fine
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

    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.

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

    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?

  12. #12
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,651

    Re: Combobox, datagrid and duplicates

    Quote Originally Posted by rakker View Post
    @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.

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

    Re: Combobox, datagrid and duplicates

    Will do, thanks for now.

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

    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

  15. #15
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,651

    Re: Combobox, datagrid and duplicates

    Quote Originally Posted by rakker View Post
    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.

  16. #16

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

    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.

  17. #17
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,651

    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

  18. #18

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

    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.

  19. #19
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,651

    Re: Combobox, datagrid and duplicates

    Quote Originally Posted by rakker View Post
    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.

  20. #20

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

    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?
    Last edited by rakker; Jan 29th, 2012 at 09:00 AM.

  21. #21

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

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

  22. #22
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,651

    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
    Attached Files Attached Files

  23. #23

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

    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.?
    Last edited by rakker; Feb 21st, 2012 at 08:29 AM.

  24. #24
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,651

    Re: Combobox, datagrid and duplicates

    Quote Originally Posted by rakker View Post
    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

  25. #25

    Thread Starter
    Lively Member
    Join Date
    Dec 2003
    Location
    netherlands
    Posts
    78

    Re: Combobox, datagrid and duplicates

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.