updating record, but not everything has changed

  • Thread starter Thread starter sklett
  • Start date Start date
S

sklett

Hi-

I have a problem and a proposed solution. In my DataGrid's update command,
there are some sections that may or may not be changed, for example
uploading a file. A user might edit the record and change the title of the
file stored, but might not upload a new file. I can't think of a clean way
to handle this type of situation w/out calling 2 different stored procedures
based on these different possible outcomes and that could get ugly fast.
Then I thought that I would conditionally execute UPDATES in the stored
procedure, for example:

if @FileName.Length <= 0
-- code to update everything BUT the file stuff
else
-- code to update file stuff as well.

Right away there is a problem. First of all, @FileName.Length is totally
made up, I don't know how to get the length of a parameter. Is that
possible? I see something called DATALENGTH in the online books, but that
doesn't seem to be the correct solution.

Second of all, if I had 20 optional pieces of data that may or may not have
changed, this could get to be really ugly. I know that I could store the
current filename, changed or unchanged, but that would cause an upload to
happen again unless I were to wrap everything up in a bunch of "Are you a
new value?" logic in my UpdateEvent. I would rather ship everything off to
the stored procedure and let it worry about everything...

So, what do I want? What is the best way? Am I going about this all wrong?
Please advise and help if possible, I'm not sure where to go from here and I
want to do it right.

Thanks,
Steve
 
Here's my solution, Update it all, even if it hasn't been changed! In the
stored procedure, create input parameters for every field you could ever
update. Then one update statement, updating every parameter. Then in code,
send all you have, whether it has been changed or not (You do have the
original values right?).

This may not be the most efficient in every case, but it's easy to
implement, easy to change, and you won't have to look at a thousand
different cases to determine what needs to be update.

If you didn't want to use stored procedures, you could dynamically create
the SQL statement/with parameters, and add only the changed parameters.
That would work well too. Or even in the stored procedure, you could
dynamically create the update statement based on if the parameter has a
value or not, then use EXEC to call it. But I still like my first solution,
though I'm in a company where something like this wouldn't be executed that
much to matter about server load.

HTH,
--Michael
 
That does help, thanks Michael!

The reason I can take a "shotgun approach" is that I DON'T have all the
data. See, when someone uploads a file, I take that opportunity to get:
mime type
size
last write time
physical path
etc, etc

when a user is editing a record in the DataGrid, they can either choose a
new file, or not, they can;t however change the above listed values since
they are obtained based on the file they choose.

So, I could send all that data to the DataGrid and implement it in read only
labels or textboxs, that would work, but it seems like unnecessary overhead.
Something for me to think about. I will review your dynamic statement
creation suggestion, I like that one too :)

Thanks for the response.
-Steve
 
Back
Top