When creating a GridView in Visual Studio, the default action for key fields is to disallow editing. While for the most part this is a good idea, there are some situations where this is not the desired action. I won't go over how to create a GridView tied to a DataSource because there are a plethorea of these articles already out there. This Tutorial will just explain how to make a non-updatable field in a GridView control tied to a DataSource updatable.
Defining the Problem
Lets say, for the sake of arguement, that I have a table of students GRE scores from applications. In this GRE scores table, I have the same student taking the test more than one time. Because there will be multiple records per student, I can't use their SSN or Student Identifier as the key. This means I will have to create a compound key of multiple fields in order to ensure uniqueness. Alternatively, I could just create an autonumber field. While easier, I don't believe this to be a very good technique. The autonumber doesn't have any context and doesn't relate to any other data.
The compound key I will use is the Students ID and the Date of the Test. This will ensure that each record will be unique. This brings up an issue when a date is entered incorrectly and needs to be updated in DataGrid. Because the date is part of the key, by default it will not be updateable in the DataGrid.
Looking at the SQL Code
The default Code for the update query is UPDATE [GRE_TABLE] set GREScore = @GREScore where [ID]=@ID and [TestDate]=@TestDate. The SQL code for the DataSource can be seen by highlighting the DataSource and clicking on UpdateQuery. The problem with this query is that it DOES NOT update the key fields. It only updates non-key fields, which in my example is the GREScore. The DataGrid will make the Key Fields Read only to signal to the developer that these fields are not updatable.
Why is this the Default Action?
The issue with updating a key field is that the value of the key field changes. Why is this a problem? Well, the key field is used to identify a row. If you attempt to identify the row with the new value, well now, it just won't be found now will it. This is a tricky thing to understand if you don't know sql very well, so an example should help.
In the SQL example above, our update command is updating GREScore where ID=ID and TestDate=TestDate. Lets say that our ID is 5 for our student and that their test date was 10/20/2007. Now lets assume that our test date was incorrect. The actual test date is 10/21/2007 and we want to change it. In order to make the swap, it is necessary to find the record with the first date, and update it with the second date. In pseudo-code, we would update GREScore and TestDate where ID=ID and TestDate=OriginalTestDate. Or, Update GREScore='510' and TestDate=10/21/2007 where Id=5 and TestDate=10/20/2007. If it was attempted to update the TestDate with the current value, the update would try to find the record 10/21/2007 which isn't in the table and just quietly not update anything just like it was asked.
Fundimentally, the problem is that we need both the OLD TestDate value and the NEW TestDate value to update the Grid, but by default we are only given the current.
Getting the Original and New Values
Now that the problem is understood, that by default there is only access to the New Value, let's change the DataSource so that we have access to both the old and new values.
The magic that is necessary to do this is called the OldValuesParameterFormatString property of the DataSource. This lets the programmer define the naming convention for the old values of the Grid before they were modified. In most examples, people will set this parameter to Original_{0}. Doing this creates just what we wanted above! Now there will be access to both the Old value of DateTime and the New value of DateTime. In the SQL code, to access the Old value of DateTime the convention @Original_DateTime will give this value.
Changing the SQL Code
Lets revisit the SQL code and make the changes we need to update a key field in our DataGrid. The code before was UPDATE [GRE_TABLE] set GREScore = @GREScore where [ID]=@ID and [TestDate]=@TestDate. After the changes we've made, lets create the code to update the TestDate key field. The New code should be UPDATE [GRE_TABLE] set [GREScore] = @GREScore, [TestDate]=@TestDate WHERE [ID]=@Original_ID and [TestDate]=@Original_TestDate.
Notice that we need to use @Original_ values to find the old record so that we can update it with the values in the Grid. This is sort of tricky because the old ID needs to be changed to Original_ID which implies that before changing the OldValueParameterFormatString ID was populated with the original value. Now that the property is set ID is empty because it isn't in our grid and only the original value is populated. If you are getting a scalar variable not defined, this is because you are trying to access the new value of ID that doesn't exist now.
Cleaning Up and Going Home
Now that the key field in the DataSource can be updated, simply change the column property of the grid to allow edits.
