Best solution for modifying a key in a record while selecting on it

Question

I have an UPDATE sql command that modifies a Date/Time field in a particular table. This table has two keys, ProductionCode and TestTime, which is what is being modified. The Production Code never changes for a particular record, but often a user will find that they entered the wrong test time and need to change it.

I have the updated time which I can retrieve from the datagrid, however, I need to select the right record to change. In order to do that, one of my where criteria has to be the current time that that record is set at in the database.

Currently, I keep an array of old times, which are updated whenever an UPDATE command is run. So in the WHERE block of the sql command, the old time is used. Once its updated in the database, then I update the time array. The difficulties come in when times are entered out of order, or when the datagrid is sorted on a different field than the time. I have to keep track of what order all the old times are in corresponding to the data grid rows, and its caused a number of bugs in the past. Currently it works fine, but its delicate at best.

So what I'm really looking for is a better solution. Should I just store the results of the SELECT statement that is used to populate the UI, and rely on them? Currently, the only place my database results go, is directly into the datagrid. Or is there another way? I have avoided automatic data binding in favor of manually using SQL commands because my datagrids are dynamically configured and use different schemas from one load to the next, and the only columns guaranteed to be there are the ProductionCode and the TestTime.

Answer 1

Add a new column "id" that holds a unique number that can identify each row. If your users need to modify the primary key you have probably not chosen a good primary key?

Answer 2

I don't know which language you're using, but the way you mention "datagrid" makes me think a .NET language.

Datagrids (and gridviews, etc.) have properties that allow you to set the key to refer back to. Create an autonumber index for your primary key, and use that to refer back to the right record to update.

A key should be something that is read-only, period.

Adding in this new field shouldn't disturb your code, hopefully -- but it might. But it's the correct way to approach your data structure.

Also, depending on your SQL flavor, you might be able to generate a row number in your selects. This might lead you to a workable solution, but it is NOT the right path to take.