Question DataSet - DataBinding: How to get previous value?

[theodordem]

I have a DataSet that loads a table from SQL server (e.g table Products). From the form designer I choose a textbox and from its properties I go to (DataBindings) -> (Advanced) and choose for Binding a Column of the Products (e.g Products.Code). The Products.Code is a primary key so I need it in order to perform the command

UPDATE TABLE Products
SET Code = "New Value"
WHERE Code = "Old Value".

The "New Value" would be new value entered in the textbox. The problem is the "old value": How do I get the "old value" from the DataSet ?

 

[jmcilhinney]

It's generally considered bad practice to change the value of a primary key. If you plan to change the Code values then you shouldn't make Code the primary key. You could perhaps make it a unique key, but you should add another ID column to act as the primary key. You then wouldn't need to get the old value for Code because you'd use the current value of ID to identify the record.

That said, the "New Value" wouldn't be what's in the TextBox, or at least that's not where you should be getting it from. The whole point of DataBinding is that whatever changes you make to one end are reflected in the other. By editing the TextBox you are inherently editing the DataTable, so the new value is already in the DataTable.

The old value is still in the DataTable too. The DataTable is specifically designed to be able to have changes made transactionally, such that they can be committed or rolled back. As such, the DataTable must store both versions of the data. When you get a value from a DataRow you specify the index or name of the column and you get the current value in that field. The DataRow.Item property is overloaded though, allowing you to specify a version of the data to get, so you can get the current value or the old value.

If you have bound your data in the designer then presumably you must have a typed DataSet with TableAdapters. If so then this should all be handled by the TableAdapter. If you look at the UpdateCommand of your TableAdapter you should see the SQL code it's using and also the parameters used to insert the values into that SQL code. Those parameters will be configured to use a particular version of the data and that parameter will be using the old value.

 

[cjard]

How do I get the "old value" from the DataSet ?

In short: you don't. You alter the value in the datatable (which given that you have bound the textbox, just editing the textbox and then leaving it is sufficient)

You then call EndEdit on the bindingsource you bound the textbox through
And you call Update on the relevant tableadapter, passing in the modified table


Do heed jmc's caveat about editing primary keys, please..