I am going to use the form that I built for the TableAdapter series as an example, but really the only thing you need to do is create a DataSource and drag a table onto your form letting .NET create the necessary components, including the BindingSource.
I connected to the Northwind.mdb and will be referring to the Customer table.
There are many confusing posts out there about filtering datasets, tables, views etc. The easiest way to do this without a doubt is by utilizing the BindingSource .NET creates for you. Filtering can be confusing so you might need to Google some SQL language if you have issues.
To start, I want to only see customers in the USA, placing this code at any time after the .NET created Fill:
Me.CustomersBindingSource.Filter = "Country='USA'"
Simple right? Many of the posts out there on filtering use custom "views" of data, if you do that you can be in big trouble when you want to use multiple views, back out one of the filters etc. Just filter the one and you should have no issues.
You have multiple criteria? Use the &= to add another to the filter, you can even use LIKE though sometimes it is difficult to get the quote marks right. I want all USA customers with Manager in their title:
Me.CustomersBindingSource.Filter = "Country='USA'"
Me.CustomersBindingSource.Filter &= " AND ContactTitle like '*Manager*'
Dont for get the AND, and don't forget that it needs a space seperating it from the previous filter.
As you are running through the filters, you can hover over it to see what it looks like, this one is:
Country='USA' AND ContactTitle like '*Manager*'
that view will let you see obvious mistakes.
FIND is another useful tool in the BindingSource. Say you want to give someone the ability to type in a CustomerID and you want that record automatically selected at the click of a button.
MsgBox(Me.CustomersBindingSource.Find("CustomerID", "Greal"))
This will tell you what index customer Greal is at in the BindingSource, you can use that together with POSITION to select that row:
Me.CustomersBindingSource.Position = _
Me.CustomersBindingSource.Find("CustomerID", "Greal")
You can make this one line but it doesn't work well here so i split it with the _.
Why did I not just filter Customer Greal? Because you can use the index to do other things such as RemoveAt or after selected RemoveCurrent.
SORT
One of the most confusing things i've seen in a while is the .Sort of a binding source, i just couldn't figure out how to make it work! The trick is to do it just like you would a filter, to sort by City:
Me.CustomersBindingSource.Sort = "City"
That's not too confusing once you know, where it gets weird is selecting Ascending or Descending. I would have thought to add a ("City",1) or ("City").Descending or something, but you do it like this:
Me.CustomersBindingSource.Sort = "City DESC"
Right in there with the name of the column! I've never seen anything like it anywhere else in .NET.
This final item is one of the most useful, i will use it more than almost any other single piece of knowledge. Before, if i wanted to know what the value in a certain column of the current row I would get it from the datagridview. This often sucked, especially getting the entire row that i could then do things with. The answer is in BindingSource.Current. I don't know about you, but i rely a lot on intellisense to show me what a control/function/method etc can do, this time it really let me down. This is what it shows for Current:
Gets the current item in the list. That is all it says, nothing more to see here. Except maybe the most useful thing in the BindingSource component. If you add the Column name here, you get what is in that column on the current row returned to you. Simple, if you know it.
MsgBox(Me.CustomersBindingSource.Current("CustomerID"))
No more scraping data off of controls, go directly to the source.
No comments:
Post a Comment