Updating Sql from form

Posted on 2006-05-10
Last Modified: 2010-04-23
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.
Question by:dlubonski
    LVL 34

    Accepted 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.


    Author Comment

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

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now