-
Jun 10th, 2013, 08:24 AM
#1
Thread Starter
Lively Member
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
-
Jun 10th, 2013, 09:42 AM
#2
Addicted Member
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
-
Jun 10th, 2013, 05:53 PM
#3
Re: Sorting data in a combobox using the underlying data model
Originally Posted by Crzyrio
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!
-
Jun 10th, 2013, 09:18 PM
#4
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.
-
Jul 1st, 2015, 02:39 PM
#5
Hyperactive Member
Re: Sorting data in a combobox using the underlying data model
Originally Posted by jmcilhinney
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.
-
Jul 1st, 2015, 03:33 PM
#6
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
-
Jul 1st, 2015, 03:37 PM
#7
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.
-
Jul 1st, 2015, 04:31 PM
#8
Hyperactive Member
Re: Sorting data in a combobox using the underlying data model
Originally Posted by wes4dbt
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.
-
Jul 1st, 2015, 04:51 PM
#9
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.
-
Jul 1st, 2015, 04:56 PM
#10
Hyperactive Member
Re: Sorting data in a combobox using the underlying data model
Originally Posted by wes4dbt
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.
-
Jul 1st, 2015, 07:16 PM
#11
Re: Sorting data in a combobox using the underlying data model
Look for s SortOrder property... set it to Descending.
-tg
-
Jul 1st, 2015, 08:57 PM
#12
Re: Sorting data in a combobox using the underlying data model
Originally Posted by larrycav
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.
-
Jul 1st, 2015, 09:31 PM
#13
Hyperactive Member
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.
-
Jul 1st, 2015, 10:49 PM
#14
Re: Sorting data in a combobox using the underlying data model
Originally Posted by larrycav
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.
-
Jul 2nd, 2015, 05:37 PM
#15
Hyperactive Member
Re: Sorting data in a combobox using the underlying data model
Originally Posted by jmcilhinney
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.
-
Jul 2nd, 2015, 05:51 PM
#16
Re: Sorting data in a combobox using the underlying data model
Originally Posted by larrycav
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.
-
Jul 2nd, 2015, 11:11 PM
#17
Re: Sorting data in a combobox using the underlying data model
Originally Posted by jmcilhinney
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...
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jul 3rd, 2015, 06:57 AM
#18
Re: Sorting data in a combobox using the underlying data model
Originally Posted by larrycav
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&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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|