Results 1 to 18 of 18

Thread: Sorting data in a combobox using the underlying data model

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jun 2007
    Posts
    98

    Sorting data in a combobox using the underlying data model

    I looked through many older posts but was unable to find out how to resolve this issue I am having.
    I am trying to sort a combobox in alphabetical order that was populated by a Access database datasource.

    I cannot set the sorted property to true. It tell me this..

    "ComboBox that has a DataSource set cannot be sorted. Sort the data using the underlying data model."

    How do I sort the data using the underlying data model?

    Thanks.
    J

  2. #2
    Addicted Member
    Join Date
    Jan 2013
    Posts
    177

    Re: Sorting data in a combobox using the underlying data model

    What it means is you have to sort your Access database and the combo box will automatically sort itself.

    I will see if I can add a bit more info in a bit

  3. #3
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: Sorting data in a combobox using the underlying data model

    Quote Originally Posted by Crzyrio View Post
    What it means is you have to sort your Access database and the combo box will automatically sort itself.

    I will see if I can add a bit more info in a bit
    Er .. not really. The datasource must be sorted before it's bound to the Combobox, that's the point. The usual way to do that would be to create a Dataview and use that as the datasource rather than the datatable directly.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Sorting data in a combobox using the underlying data model

    Both replies so far have somewhat missed the mark. Firstly, if you have control over the query at run time and you want the data in a specific order then you should do that using the query, e.g.
    Code:
    SELECT *
    FROM MyTable
    ORDER BY SomeColumn
    It's not the Access database that you sort, but rather the data you retrieve as you retrieve it.

    If you don't have control of the query or you want to be able to sort in multiple different ways then you have to do that in your VB code. dunfiddlin is correct that you use a DataView but you don't create it and you don't use it in place of the DataTable. When you bind a DataTable to one or more controls, the data actually comes from the DefaultView of the DataTable, not the Rows. The DefaultView is a DataView that the DataTable creates itself. That's how you're able to sort a DataGridView bound to a DataTable. You can set the Sort property of the DataView before binding, e.g.
    Code:
    myDataTable.DefaultView.Sort = "SomeColumn"
    myComboBox.DataSource = myDataTable
    That will display the data in the ComboBox in ascending order by SomeColumn. If you set the Sort property again later, the ComboBox will update automatically.

    In this specific case it doesn't really offer much advantage but I would recommend using a BindingSource whenever you bind. It provides a one-stop-shop for any access you need to the bound data. It also has a Sort property and you can use it instead of the Sort property of the DataView, e.g.
    Code:
    myBindingSource.DataSource = myDataTable
    myBindingSource.Sort = "SomeColumn"
    myComboBox.DataSource = myBindingSource
    The BindingSource would be added in the designer.

  5. #5
    Hyperactive Member
    Join Date
    Nov 2013
    Posts
    292

    Re: Sorting data in a combobox using the underlying data model

    Quote Originally Posted by jmcilhinney View Post
    Both replies so far have somewhat missed the mark. Firstly, if you have control over the query at run time and you want the data in a specific order then you should do that using the query, e.g.
    Code:
    SELECT *
    FROM MyTable
    ORDER BY SomeColumn
    It's not the Access database that you sort, but rather the data you retrieve as you retrieve it.

    If you don't have control of the query or you want to be able to sort in multiple different ways then you have to do that in your VB code. dunfiddlin is correct that you use a DataView but you don't create it and you don't use it in place of the DataTable. When you bind a DataTable to one or more controls, the data actually comes from the DefaultView of the DataTable, not the Rows. The DefaultView is a DataView that the DataTable creates itself. That's how you're able to sort a DataGridView bound to a DataTable. You can set the Sort property of the DataView before binding, e.g.
    Code:
    myDataTable.DefaultView.Sort = "SomeColumn"
    myComboBox.DataSource = myDataTable
    That will display the data in the ComboBox in ascending order by SomeColumn. If you set the Sort property again later, the ComboBox will update automatically.

    In this specific case it doesn't really offer much advantage but I would recommend using a BindingSource whenever you bind. It provides a one-stop-shop for any access you need to the bound data. It also has a Sort property and you can use it instead of the Sort property of the DataView, e.g.
    Code:
    myBindingSource.DataSource = myDataTable
    myBindingSource.Sort = "SomeColumn"
    myComboBox.DataSource = myBindingSource
    The BindingSource would be added in the designer.
    This was very helpful but I'm having an issue with a combobox on a form that will not sort by a checkbox field in an MSAccess table. The field is a checkbox.

    Taking the advice on sorting by using the sort property of the binding source seemed like the solution and in fact it seems to work if I use a field other than the checkbox field. I'm wondering if there's some trick to the syntax that must be typed in the the SORT Property field in the form designer.

    The combobox is bound to a dataset on the form. The binding source data member is set to the table I want and the combobox fills properly.

    To create the combobox I drag/dropped fields from the DataSource window onto the form. Associated with the combobox are 3 text boxes next to it.

    If, in the bindingsource sort property, I type in the name of another column, one that is associated with one of the textboxes, then the combobox sorts by that field. But it flat out refuses to sort by the underlying data field that is a checkbox "Active" is the name of that column of data.

    Any hints as to what's causing this? I thought perhaps it was related to that field being a yes/no field and tried a few different things in the bindingsource sort property field.. Active = yes, Active = "yes" but they don't work. The form blows up when I run debug. If it type in the checkbox field name of ACTIVE, the debug runs fine but the combobox doesn't sort by that field.
    Last edited by larrycav; Jul 1st, 2015 at 02:43 PM.

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Sorting data in a combobox using the underlying data model

    I don't know about using a bindingsource sort method but as jmc suggest in post #4 you can just sort the datatables using the defaultview.

    This works,
    Code:
                da.Fill(dt)
                dt.DefaultView.Sort = "yesno"
                Me.Combobox1.DisplayMember = "yesno"
    
    
                Me.Combobox1.DataSource = dt

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Sorting data in a combobox using the underlying data model

    I just tested using a bindingsource and it worked just fine,
    Code:
                da.Fill(dt)
                Me.BindingSource1.DataSource = dt
                Me.Combobox1.DisplayMember = "yesno"
                Me.Combobox1.DataSource = Me.BindingSource1
                Me.BindingSource1.Sort = "yesno"

    EDIT: I think maybe I misunderstood you. Do you want to sort the items in the dropdown list or sort the records in the database table.
    but it works both ways.
    Last edited by wes4dbt; Jul 1st, 2015 at 04:13 PM.

  8. #8
    Hyperactive Member
    Join Date
    Nov 2013
    Posts
    292

    Re: Sorting data in a combobox using the underlying data model

    Quote Originally Posted by wes4dbt View Post
    I just tested using a bindingsource and it worked just fine,
    Code:
                da.Fill(dt)
                Me.BindingSource1.DataSource = dt
                Me.Combobox1.DisplayMember = "yesno"
                Me.Combobox1.DataSource = Me.BindingSource1
                Me.BindingSource1.Sort = "yesno"

    EDIT: I think maybe I misunderstood you. Do you want to sort the items in the dropdown list or sort the records in the database table.
    but it works both ways.
    I want to sort the combobox & the textboxes next to it. It's like this on the form.

    [combobox] [checkbox] [textbox1] [textbox2] [textbox3] Selecting from the combobox yields the textbox fields and checkbox to change according to the combobox item selected. They do...that works just fine.

    That data uses a bindingsource and tableadapter.

    Based on JMC's post I simply used the bindingsource property setting for sort and entered in the name of the field that contains the checkbox. It doesn't work... It won't sort by that field.

    However, if using bindingsource property and using textbox1, textbox2 or textbox3 as the sort field, the combobox items are sorted perfectly, based on any of those other sort criteria. That's what puzzles me.

    In the combobox, the displaymember is "name" and I need to keep that. I see you used the checkbox as the display member.

    I verified that only one item in the combobox field is checked so I can tell immediately if the sort takes place. That item should appear first in the combobox list of names. The checkbox does in fact reflect the data correctly with regard to showing checked when the item associated with it is selected from the combobox.... it just won't sort by it. Makes absolutely no sense..

    I went through each items properties to verify that they are all pointed to the correct binding source.

    Perhaps in the bindingsource sort property where I type in the table column name I need to also add = yes but no matter how I've tried to word it, entering anything but the word ACTIVE which is the name of the column, it will blow up as soon as you run debug. Any suggestions...BTW, thank you for the assistance.
    Last edited by larrycav; Jul 1st, 2015 at 04:34 PM.

  9. #9
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Sorting data in a combobox using the underlying data model

    So, you want to sort the combobox list items (names) by whether the field "Active" is checked or not?

    This statement is wrong,
    Code:
    I verified that only one item in the combobox field is checked so I can tell immediately if the sort takes place. That item should appear first in the combobox list of names.
    The checked items will be at the end of the list because "False" come before "True" in string sorting. Unless you've made to sort descending.

    You should post your code.

  10. #10
    Hyperactive Member
    Join Date
    Nov 2013
    Posts
    292

    Re: Sorting data in a combobox using the underlying data model

    Quote Originally Posted by wes4dbt View Post
    So, you want to sort the combobox list items (names) by whether the field "Active" is checked or not?

    This statement is wrong,
    Code:
    I verified that only one item in the combobox field is checked so I can tell immediately if the sort takes place. That item should appear first in the combobox list of names.
    The checked items will be at the end of the list because "False" come before "True" in string sorting. Unless you've made to sort descending.

    You should post your code.
    Ahhh... I did not know that. That's interesting.

    There truly is no code on the form related to the combobox. It's all handled through the properties settings.

    That little tid bit of information that you gave me solves the issue. Thank you for that. I can tell the users to simply uncheck the item they wish to work with.... long story...you'd have to know what the app is for.


    Or is there a way to force true ahead of false in this matter?
    Last edited by larrycav; Jul 1st, 2015 at 05:00 PM.

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Sorting data in a combobox using the underlying data model

    Look for s SortOrder property... set it to Descending.

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

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Sorting data in a combobox using the underlying data model

    Quote Originally Posted by larrycav View Post
    Ahhh... I did not know that. That's interesting.

    There truly is no code on the form related to the combobox. It's all handled through the properties settings.

    That little tid bit of information that you gave me solves the issue. Thank you for that. I can tell the users to simply uncheck the item they wish to work with.... long story...you'd have to know what the app is for.


    Or is there a way to force true ahead of false in this matter?
    Of there's a way and you've already been told what it is:
    The checked items will be at the end of the list because "False" come before "True" in string sorting. Unless you've made to sort descending.
    Did you bother to make any effort to find out how to sort in descending order? I just Googled bindingsource sort descending and the first result was the MSDN documentation for the BindingSource.Sort property. Given that you're already using that property, you really should have already read that documentation but it's still a 5 second job to find it via Google. Another 10 seconds into read ing that documentation you find this:
    The Sort property is a case-sensitive string that specifies the column names used to sort the rows, along with the sort direction. Columns are sorted ascending by default. Multiple columns can be separated by commas, such as "State, ZipCode DESC".
    Look at that! An answer to your question and it took about 20 seconds to find. I am repeatedly amazed at how people's inclination to actually look for information is inversely proportional to the ease with which it can be found.

  13. #13
    Hyperactive Member
    Join Date
    Nov 2013
    Posts
    292

    Re: Sorting data in a combobox using the underlying data model

    JMC.....You're pretty sharp with Visual Studio....but without question your tongue is sharper. Let's add this up... you spent at least 60 seconds whining about 20 seconds worth of searching.... Do you feel better now... here's your "W"...wear it proudly...

    Thank you Wes4dbt and techgnome...much appreciated.
    Last edited by larrycav; Jul 1st, 2015 at 09:38 PM.

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Sorting data in a combobox using the underlying data model

    Quote Originally Posted by larrycav View Post
    JMC.....You're pretty sharp with Visual Studio....but without question your tongue is sharper. Let's add this up... you spent at least 60 seconds whining about 20 seconds worth of searching.... Do you feel better now... here's your "W"...wear it proudly...
    The first time I saw someone too lazy to do a simple web search I was quite polite in pointing it out. The few thousand times since then have depleted my patience. Maybe, just maybe, you should consider that you could actually help yourself by taking the criticism on board instead of thinking that it's worse to point out someone being lazy than actually being lazy in the first place. Look first, ask questions later. It's that simple.

  15. #15
    Hyperactive Member
    Join Date
    Nov 2013
    Posts
    292

    Re: Sorting data in a combobox using the underlying data model

    Quote Originally Posted by jmcilhinney View Post
    The first time I saw someone too lazy to do a simple web search I was quite polite in pointing it out. The few thousand times since then have depleted my patience. Maybe, just maybe, you should consider that you could actually help yourself by taking the criticism on board instead of thinking that it's worse to point out someone being lazy than actually being lazy in the first place. Look first, ask questions later. It's that simple.
    People come here because the don't know it all. That MS article you pointed me to was of very little help to me. You read it and it all makes perfect sense. When you're a beginning programmer it often doesn't. Time and again I've see you unleash your temper on newbies.

    You didn't need to even jump into the conversation. The other guys, with personalities that aren't 80 Grit were helping me just fine. Then you come along with your condescending attitude pretending to answer the question when your hidden agenda was really to attack. You're a sharp, seasoned programmer. That's painfully obvious. What you are NOT is a teacher. In future, just stay out of my conversations.

  16. #16
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Sorting data in a combobox using the underlying data model

    Quote Originally Posted by larrycav View Post
    Then you come along with your condescending attitude pretending to answer the question when your hidden agenda was really to attack.
    I have no hidden agenda. My agenda is to help people become the best programmers they can. If that means shocking them into thinking for themselves then so be it. I don't think it actually helps people in the long run to continue to spoon-feed them things that they can easily find for themselves. It's worrisome that that article didn't help because it explicitly answers the question you needed answered. I think that it's probably because you didn't even know the question, which is even more worrisome. Regardless, even if the article didn't help, the fact that you hadn't already found it because you hadn't already looked for it is the major issue that will hold you back. If you fix that then you will be the one to benefit, not me. If you'd rather ignore constructive criticism because you don't like how it's delivered then you only hurt yourself. It's no skin off my nose either way.

  17. #17
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,467

    Re: Sorting data in a combobox using the underlying data model

    Quote Originally Posted by jmcilhinney View Post
    The first time I saw someone too lazy to do a simple web search I was quite polite in pointing it out. The few thousand times since then have depleted my patience. Maybe, just maybe, you should consider that you could actually help yourself by taking the criticism on board instead of thinking that it's worse to point out someone being lazy than actually being lazy in the first place. Look first, ask questions later. It's that simple.
    The first ten thousand times were the worst. The second ten thousand times, they were the worst too. The third ten thousand times I didn't enjoy at all. After that it got worse...

  18. #18
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: Sorting data in a combobox using the underlying data model

    Quote Originally Posted by larrycav View Post
    People come here because the don't know it all. That MS article you pointed me to was of very little help to me. You read it and it all makes perfect sense. When you're a beginning programmer it often doesn't. Time and again I've see you unleash your temper on newbies.
    Correct, none of us know it all. The MSDN documentation is an acquired taste, one you should develop. If it doesn't make sense the first time read it until it does, or read some other articles. You might be surprised at JMC's response if you had said, "I read the article and didn't understand ______________." The ______________ is important, without it you get some more JMC charm

    We have all been there, the terminology is daunting, but JMC and MSDN use it correctly more often than not. It is a foreign language that must be learned. The JMC whip has seen my back a few times, but instead of being continually offended I became a voyeur, reading posts by JMC just to see who he was lashing. A funny thing happened; I noticed that he REALLY helped those that try to help themselves. JMC says his patience is depleted, but he takes the time to write what I summarize with an acronym, RTFM.

    Up to this time my knowledge of the tools that can be used to manipulate data and forms is primitive at best. Binding what, data what, etc. In my job I have to get better at this, so I read posts about data. You may not have followed JMC's advice but I did and there on the very page was an example. Here is my first attempt, using the example, at trying to see how it all fits together:
    Code:
    Public Class Form1
    
        Private Sub Buttons_Click(sender As Object, e As EventArgs) Handles Button1.Click, Button2.Click
            If myBS.Position = myBS.List.Count - 1 Then
                myBS.MoveFirst()
            Else
                myBS.MoveNext()
            End If
        End Sub
    
        Dim myDS As New DataSet()
        Dim myBS As New BindingSource()
    
        Private Sub PopulateData()
    
            ' Some xml data to populate the DataSet with. 
            Dim musicXml As XElement = <music>
                                           <recording><artist>Coldplay</artist><cd>X&amp;Y</cd></recording>
                                           <recording><artist>Dave Matthews</artist><cd>Under the Table and Dreaming</cd></recording>
                                           <recording><artist>Natalie Merchant</artist><cd>Tigerlily</cd></recording>
                                           <recording><artist>U2</artist><cd>How to Dismantle an Atomic Bomb</cd></recording>
                                           <recording><artist>Dave Matthews</artist><cd>Live at Red Rocks</cd></recording>
                                       </music>
    
            ' Read the xml. 
            Dim ms As New IO.MemoryStream
            musicXml.Save(ms)
            ms.Seek(0L, IO.SeekOrigin.Begin)
            myDS.ReadXml(ms)
    
            ' Get a DataView of the table contained in the dataset. 
            Dim tables As DataTableCollection = myDS.Tables
            Dim view1 As New DataView(tables(0))
    
            ' Create a DataGridView control and add it to the form. 
    
            ' Create a BindingSource and set its DataSource property to 
            ' the DataView. 
            myBS.DataSource = view1
    
            ' Sort the data source 
    
            'myBS.Sort = "cd"
            myBS.Sort = "artist, cd DESC"
    
            TextBox1.DataBindings.Add("text", myBS, "artist")
            TextBox2.DataBindings.Add("text", myBS, "cd")
        End Sub
    
        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            PopulateData()
        End Sub
    End Class
    You didn't need to even jump into the conversation. The other guys, with personalities that aren't 80 Grit were helping me just fine. Then you come along with your condescending attitude pretending to answer the question when your hidden agenda was really to attack. You're a sharp, seasoned programmer. That's painfully obvious. What you are NOT is a teacher. In future, just stay out of my conversations.
    As often is the case I am glad he did, I learned something. My experience with JMC is that there is nothing hidden, he speaks his mind. My flying instructor was from South Africa and she had one rule; when she said she "had the plane" she meant it. On one early lesson, low and slow on short final, she uttered those words and my hands and feet did not move quickly away from the controls, she cracked me with a flight computer. It never happened again.

    IMHO JMC is a great teacher so long as the student tries to help themselves. If you read all of his posts you will see that the "attack" on you was not personal, he advises all the same. You will also see some of the most well written, informative answers here. Read them at least twice.

    Read his posts in this thread again. He never said anything critical of you personally, though you might have reasoned that you were the one that was lazy. It didn't stop you from saying, "What you are NOT is a teacher." Something about glass houses comes to mind.

    You should hope that JMC doesn't take your request to heart.

    All of us that have been here a long time like to see people try and help themselves. "Give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime."
    Last edited by dbasnett; Jul 3rd, 2015 at 07:11 AM.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

Posting Permissions

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



Click Here to Expand Forum to Full Width