BindingSource with Sql Query

[jamie_pattison]

Im not sure if this is possible or if i dont understand something here. I have a Dataset with two SQL queries. One query calls "all" the data and second calls "half" the data (in simple terms).

I add a DataGridView to my windows form.. I am now trying to connect the DataGridView to the QUERY that displays half the data - this works as expected when i programmatically do this ,however i dont have the option to add and delete columns, rename them etc etc and it also displays column names for those that i excluded in the SQL query (but no data).

So reading around it seems that i have to use a BindingSource to call the query at design time. Once i do this then i should have the usual options to edit the column names etc etc as required for the DataGridView once i use the BindingSource as the source for the DGV.

Question is how do i do this as so far all the attempts ive tried bring back all the data and dont allow me to select any SQL queries from my dataset. I believe i could set the filter on the BindingSource but that defeats the point of having a Dataset with a query that i would like to run. Programmatically it works but then i have some unusual results?

Thanks

 

[jmcilhinney]

You don't need to use a BindingSource but you probably should. The trick is that the grid will create any extra columns needed when you bind data to it, but it's never going to remove columns. That means that if you already have data bound to the grid you need to unbind it, remove all the columns then bind the new data. So:

1. Populate a DataTable.
2. Bind the DataTable to a BindingSource.
3. Bind the BindingSource to the grid.

4. Unbind the BindingSource, i.e. set the grid's DataSource to Nothing.
5. Clear the grid's Columns collection.

6. Populate a new DataTable.
7. Bind the DataTable to the BindingSource.
8. Bind the BindingSource to the grid.

 

[cjard]

A few facts, some of which may not apply to you:

When you write a query into a tableadapter, if you don't include the columns that form the primary key of the table, you cannot update rows in the database

When you create a tableadapter with SELECT * FROM table you get a datatable that represents ALL the columns in the table

If you add another query to the same TA that only selects half the columns (see note 1 re updating data) the datatable will still have all the columns, but only half of them will have data

If you want a DataGridView that only shows this half data then you should expand the data sources window (find it on a menu) and drag the icon of the datatable to your form: You end up with a datagridview that has ALL the columns in the datatable but critically, it has its AutoGenerateColumns property set to FALSE (this property is not shown in the property grid of the visual designer but can be set in code)
Edit the columns collection (in the visual designer) of the grid, removing the columns that you DIDNT select in your query.
Ensure the line that does the filling of the datatable, is the query that fills half the data - it's normally in form load event but you can put it elsewhere
You now have a Datatable with ALL columns, a query that fills only half of them, and a grid that only is rendering that half, and isnt going to autogenerate the others (as grids do by default)

Remember note 1- if the half you select DOESNT contain the PK, then you will not be able to update the database. If you want to update the db, include the PK columns. If you dont want the user to SEE them, remove them from the grid, but they HAVE to be in the datatable so that the update can use them to identify the row