• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 154
  • Last Modified:

Updating Sql from form

I currently have a form that contains a combobox and a number of text boxes, that display information based upon a dataset that is filled at form load.


All the text boxes are bound to display the value of a specific field in the dataset. When I select a different facility from the combobox all related data is displayed correctly in the associated text boxes (so if I change from facility A to facility B the facility ID field is updated and so on). I want to be able to make a change to a field such as the address text box and save it back to the database. I mostly use the designer and wizards as I'm still learning but I've tried the following which works on other forms where I use a datagrid and not individual fields.

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

            daFacility.Update(DsFacility, "facility")
        Catch ex As Exception


        End Try

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Catch ex As Exception
        End Try
    End Sub

I've also tried to use a sql query direct with no luck (I used the sqlconnection that I used on the form for the dataset)

            daFacility.UpdateCommand.CommandText = "UPDATE facility SET oid = '" & txtOid.Text & "', facstatus = '" & txtFacStatus.Text & 
            "faccap='" & txtFacCap.Text & "',facint='" & txtFacInt.Text & "',faccntyid='" & txtFacCntyId.Text & "' where facid='" & lblFacID.Text "'"


it's probably something simple but I've spent too much time already and need to move on.
1 Solution
A DataAdapter's .Update method only works on rows for which the .RowState flag is set to one of the modified values: Added, Deleted or Modified.  With databound controls that flag is set (and the data in the row is changed) by the binding manager and it won't do that until it knows that the user has finished editing the record/row concerned.  With some controls - e.g. a datagrid - the binding manager knows that the edit on any one record/row has been finished if the user navigates to a different row. Otherwise, your code needs explicitly to instruct the binding manager to .EndCurrentEdit.

I refer above to "binding manager".  That can be be a CurrencyManager or BindingManagerBase that you have expressly declared.  If not, you can use Me.BindingContext(myTable) - where myTable is the DataTable that contains the data that is bound to the control/s.

And then, if you simply add the appropriate one of


right at the start of your btnUpdate_Click sub, your first approach should work.  

So far as I can see, your alternative approach should, in principle, have worked (although you don't actually show all the code to make it do so), but whether it would do so in practice would depend on details which I don't know.  But I suggest you try the above amendment to your dataadapter approach before we start investigating the SQL statement.

dlubonskiAuthor Commented:
Thanks Roger, it worked perfectly. Now I also have a better understanding of what wasn't happening and why.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now