Best Practice - Handling multiple fields, user roles, and one stored procedure

Question

I have multiple fields both asp:DropDownList's and asp:TextBox's. I also have a number of user roles that change the Visible property of certain controls so the user cannot edit them. All of this data is saved with a stored procedure call on PostBack. The problem is when I send in the parameters and the control was not on the page obviously there wasn't a value for it, so in the stored procedure I have the parameters initialized to null. However, then the previous value that was in the database that I didn't want changed is overwritten with null.

This seems to be a pretty common problem, but I didn't have a good way of explaining it. So my question is, how should I go about keeping some fields from being on the page but also keeping the values in the database all with one stored procedure?

Answer 1

Apply the same logic when chosing what data to update as the logic you're actually using when chosing what data (and its associated UI) to render.

Answer 2

I think the problem is you want to do the update of all fields in a single SQL update, regardless of their value.

I think you should do some sanity check of your input before your update, even if that implies doing individual updates for certain parameters.

Answer 3

Without an example, it is a little difficult to know your exact circumstances, but here is a fictitious statement that will hopefully give you some ideas. It is using t-sql (MS SQL Server) since you did not mention a specific version of SQL:

UPDATE SomeImaginaryTable
SET FakeMoneyColumn = COALESCE(@FakeMoneyValue, FakeMoneyColumn)
WHERE FakeRowID = @FakeRowID

This basically updates a column to the parameter value, unless the parameter is null, in which case it uses the columns existing value.

Answer 4

Generally to overcome this in my update function

1. I would load the current values for the user
2. Replacing any loaded values with the newly changed values from the form
3. Update in db.

This way I have all the current plus everything that has been changed will get changed.

This logic will also work for an add form because all the fields would be null then get replaced with a new value before being sent to the db. You would of course just have to check whether to do an insert or update.