[thelist] comparing multiple values in VBScript

Matt Warden mwarden at gmail.com
Thu Dec 1 22:29:27 CST 2005


On 12/1/05, Canfield, Joel <JCanfield at pacadvantage.org> wrote:
> Our HelpDesk work order update tool is based on a form that calls
> itself, checking for changed fields to update in the db (MSSQL)
> There are 17 changeable fields, and currently, I'm looping through each
> field, comparing the current value I've gathered from the db to the new
> value submitted by the form. Any changed values are concatenated onto
> strings which ultimately form the db update query.
> There has to be a more efficient way, right?
> Here's a bit of code for two of the 17 fields:
>         If Not(strCVTASK = strNewTASK) Then
>                 strFields = strFields & ", TASK"
>                 strValues = strValues & ", '" & strNewTASK & "'"
>         End If
>         If Not(strCVTYPE = strNewTYPE) Then
>                 strFields = strFields & ", TYPE"
>                 strValues = strValues & ", '" & strNewTYPE & "'"
>         End If
> and they all get strung together here:
>     "insert into TASKS (" & strFields & ") values (" & strValues & ")"
> Ideas?

Can you help me understand why you are using an insert here instead of
an update? It seems like you have a purpose in mind, but I can't
figure it out. Why aren't you just updating the original record? If
you did that, you could issue an UPDATE statement with all fields and
not have to worry about which have been modified. If you are trying to
keep a history of the work order, I would suggest just copying the old
record to a history table.

But, if there is some reason you have to do it this way, I think
you've pretty much nailed how it has to be done.

Matt Warden
Miami University
Oxford, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list