[RESOLVED] DataGridView: Bound and Unbound information

[ovi_gm]

Hey,

I have a data-grid-view that has a data source associated with it. Suppose the information contained in the database is:

0. ID
1. Number of Pieces
2. Price / piece

Now I manually add a new UNBOUNDED column in my DGV. Lets name it Total.

I want this column to display the "Number of pieces" * "Price / piece" multiplication.

But I want this multiplication to be made at runtime for all the records in the database, without having to store this information in the database.

How can I do this?

Thx.

[jmcilhinney]

The easiest way would be to actually add a column to your DataTable and set its Expression and then let that be bound to the grid. The fact that the column exists in the DataTable does not mean that it has to exist in the database. You simply don't include that column in any of your SQL code and you're good to go. If you're using a data adapter to populate an untyped DataTable then the code might look like this:

myDataAdapter.Fill(myDataTable)
myDataTable.Add("TotalValue", GetType(Decimal), "StockCount * Price")
myDataGridView.DataSource = myDataTable

[ovi_gm]

I have this code:

Me.partsTableAdapter.Fill(Me.ContactsDataSet.Parts)
' And some sorting
Dim dv1 As New DataView(ContactsDataSet.Parts)
...here I have my sorting code...
dgvParts.DataSource = dv1

where dgvParts is the datagridview containing the information

[jmcilhinney]

Firstly, don't create a DataView. If you have a typed DataSet then add a BindingSource and bind your DataTable to that and bind that to the DataGridView. You set the DataSource and DataMember of the BindingSource and then the DataSource of the DataGridView. If you want to sort the data after retrieving it then you set the Sort property of the BindingSource. Likewise, filtering is done by setting the Filter property of the BindingSource.

As for adding the column, you do that DataSet designer. Right-click the appropriate DataTable, select Add -> Column and then configure it in the Properties window. As long as the SQL code in the table adapter doesn't change, which it won't unless you change it, that column will have no interaction with the database.

[ovi_gm]

Ok, it works now the way I need. Thank you. But why did you advice me not to use DataView? It is important for me because my database will contain many different projects and I want to show only the information belonging to a certain project. I have this code:

Dim dv1 As New DataView(ContactsDataSet.Parts)
dv1.RowFilter = projectCode 'which is a variable and is set somewhere inside the form
dgvParts.DataSource = dv1

And so I am filtering my parts according to my projectCode.

Is that ok or is there another way to do this?

[jmcilhinney]

Ok, it works now the way I need. Thank you. But why did you advice me not to use DataView? It is important for me because my database will contain many different projects and I want to show only the information belonging to a certain project. I have this code:

Dim dv1 As New DataView(ContactsDataSet.Parts)
dv1.RowFilter = projectCode 'which is a variable and is set somewhere inside the form
dgvParts.DataSource = dv1

And so I am filtering my parts according to my projectCode.

Is that ok or is there another way to do this?

As I said in my previous post, if you want to sort and filter the data locally then you use the Sort and Filter properties of the BindingSource. That said, if you only want a portion of the data to begin with then you should only retrieve that portion. Don't retrieve it all unless you do, or at least might, need it all. If you don't need all the data then you should be filtering via the query, so that only some of the data gets retrieved from the database. To do that, right-click the table adapter and select Add -> Query and configure it appropriately. The SQL code can do anything you like as long as the result set has the same schema as the original query, which matches the database table schema. If you want to filter then you need to add a WHERE clause. For instance, if you have a query like this:

SELECT ID, Name, Date
FROM MyTable

being executed by the Fill method and you want to be able to filter that by Date then you would add a query like this:

SELECT ID, Name, Date
FROM MyTable
WHERE Date = @Date

and execute it by a FillByDate method.

[ovi_gm]

Ok, I got it. Thanx a lot.

[ovi_gm]

BUT. Excuse me If I may sound stupid but it's really making me crazy. In your example you wrote WHERE Date = @Date. Ok, but what if I need a variable form my project?

Like: WHERE ProjectCode = variable

How do I put it?

[ovi_gm]

Nevermind. I got it. Made it work. Thx again jmcilhinney.
I have no experience with DGV's but now I understan what you mean.

Cheers