1

I'm working on a project where users are able to modify records. So basically a form loads, with roughly 15 fields (textboxes, Comboboxes, etc). The user is able to edit any or all fields on that form. I create a function that saves the original values (on form_Load) to .tag property. Then when user tries to save it, i compare control.tag = control.text and see if the values have changed. It recognizes the smallest of changes, and works seamleassly. Now I'm trying to update these changes in SQL SErver, but I'm not sure what the best idea would be. I'm trying to create a parametized query to update each field, but in this case should I be comparing the .tag to .text for each value, and if it's different then passing the parameter to the UPDATE statement....here's what I mean...

Update tbl1 Set Dept = @Dept
if dept.tag = dept.text
    cmd.parameters.addwithvalue(@Dept, sqldptype.text)=txtdept.text
End if

Anyone have any ideas if this is wrong?

Is there another way to do this other than checking if values are different, I feel like this is tedious? then again should I just update all the fields, as long as 'SOME' fields where changed?

Trying to find a best way to do this....this is my function to check if fields are different on UPDATE

Private Function isDirty() As Boolean

    IsDirty = False
    For Each ctr As Control In grpClientInfo.Controls
        If TypeOf ctr Is TextBox Then
            If ctr.Tag.ToString <> ctr.Text Then
                IsDirty = True
            End If
        ElseIf TypeOf ctr Is ComboBox Then
            If ctr.Tag.ToString <> ctr.Text Then
                IsDirty = True
            End If
        ElseIf TypeOf ctr Is MaskedTextBox Then
            If ctr.Tag.ToString <> ctr.Text Then
                IsDirty = True
            End If
        ElseIf TypeOf ctr Is RichTextBox Then
            If ctr.Tag.ToString <> ctr.Text Then
                IsDirty = True
            End If
        End If
    Next

End Function

then I check the boolean

if isDirty() then
   MsgBox "You've made changes....blah blah saving now....
   CALL ThisNewUpdateFunction 'yet to be written
else
   MSgboX "no changes"
End if
BobSki
  • 1,531
  • 2
  • 25
  • 61
  • 1
    The *easiest* way is to let your DB provider do the work: `myDA.Update(myDT)`. The DataAdapter will add, delete and update all the rows according to what they need. No new SQL required. (the DA/DT will do all that tracking for you) – Ňɏssa Pøngjǣrdenlarp Oct 31 '16 at 16:57
  • 1
    Slight syntax issue with `cmd.parameters.addwithvalue(@Dept, sqldptype.text)=txtdept.text`. Should be `cmd.Parameters.Add("@Dept", SqlDbType.Text).Value = txtdept.text`. Worth noting if you go down this route. – Bugs Oct 31 '16 at 17:03
  • ...or even `cmd.Parameters.Add(@Dept, sqldptype.text).Value = txtdept.text` – Ňɏssa Pøngjǣrdenlarp Oct 31 '16 at 17:04
  • 1
    @Jinx88909 thank you, yeah i was just typing it from memory. – BobSki Oct 31 '16 at 17:04
  • @Plutonix - is this a good example if I want to go down the DA path...http://vb.net-informations.com/dataadapter/dataadapter-updatecommand-sqlserver.htm – BobSki Oct 31 '16 at 17:06
  • 1
    I think [this is a good example](http://stackoverflow.com/a/33702351/1070452) Also shows some fltering etc on the DataTable. You could poll the table to see if there are changes rather than testing each and every field. Also, updates can be "batched" send 5 deletes, 3 updates and 7 adds all at once – Ňɏssa Pøngjǣrdenlarp Oct 31 '16 at 17:18

0 Answers0