Save changes from dataview to database

Hi all,

I have asked similar questions before but I always get stuck on updating data.  

On my form all the controls are bound at runtime (in form load event) to a dataview.  Changes are made to the dataview via the controls and they should be saved to the database when the 'save' button is clicked.

This is the code I have but it doesn't work, I have tried every combination I can think off but just can't get it working which is very frustrating.  Can somebody please tell me what I am doing wrong?  I am so confused!

Thanks...code below:

In the Public class bit at the top of my form I have have this:

  Dim sqlStr As String = "Select  LastName + ', ' + FirstName as Name, FirstName, LastName, Initials, ACode, JobTitle, GradeID, DateJoined, DateLeft, HrsPerWeek, CurrentChargeRate FROM tblStaff" 'FirstName, Initials, JobTitle, DateJoined, DateLeft, UnitsPerWeek, CurrentChargeRate, TeamID, TeamLeader, WorkingHoursFrom, WorkingHoursTo, Active, FullTime, Returning FROM tblStaff"
    Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sqlStr, conTRS)
    Dim dsJJ As DataSet = New DataSet
    Dim staffDV As DataView
    Dim cb As New OleDb.OleDbCommandBuilder(da)


In the form load I have this:

            'dataadaptor and dataset declared at top of form as public
            da.Fill(dsJJ, "Staff")

            'Dataview declared at top of form as public
            staffDV = New DataView(dsJJ.Tables("Staff"), "HrsPerWeek = 145", "Name", DataViewRowState.CurrentRows)

            cbxStaffName.DataSource = staffDV
            cbxStaffName.DisplayMember = "Name"
            cbxStaffName.ValueMember = "HrsPerWeek"

            Me.txtFirstName.DataBindings.Clear()
            Me.txtFirstName.DataBindings.Add("Text", staffDV, "FirstName")

On the save button I have this:

        Dim tbl As DataTable = dsJJ.Tables("Staff")
        staffDV = tbl.DefaultView
        Me.BindingContext(staffDV).EndCurrentEdit()
        da.Update(dsJJ.Tables("Staff"))
        dsJJ.AcceptChanges()
CherryexAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
What does this test produce?  What does 'recordCount' equal?

   Dim changes As DataTable = dsJJ.Tables("Staff").GetChanges()
   Dim recordCount As Integer = changes.Rows.Count

Bob
CherryexAuthor Commented:

I got the following message when I tried your test.  Is this because it is returning a null value because it can't see any changes to make?

  Message="Object reference not set to an instance of an object."

     
Bob LearnedCommented:
If you don't have any changes, then nothing will get updated in the database.

Bob
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

CherryexAuthor Commented:
I am making changes but they aren't being picked up.  I changed the data in a field and tabbed into the next field then clicked save.  I don't know why it can't see that a change has been made.
Bob LearnedCommented:
What version of .NET are you working with?  What is the context--WinForms or ASP.NET?

Bob
CherryexAuthor Commented:
I am using version 2005 pro and it is WinForms.
Bob LearnedCommented:
I was looking for an easy answer, like you were running ASP.NET, and there was a post-back, and blah blah blah, but no dice.  Everything looks good from here.

Bob
CherryexAuthor Commented:
Bob, thank you for your help.  

I hope you don't mind me just closing this question but I found the solution myself after lots of trial and error code.  It looks like it did not want to update as I had not included the primary key field in my SQL statement.  I made a few amendments to my code to simplify it but nothing worked until the primary key was added.  

For anyone with a similar problem this is the code that now works for me:
   
This code in the top part of the form:

    Dim sqlStr As String = "Select StaffID, LastName + ', ' + FirstName as Name, FirstName, LastName, Initials, ACode, JobTitle, GradeID, DateJoined, DateLeft, HrsPerWeek, CurrentChargeRate FROM tblStaff" 'Include primary key.
    Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sqlStr, conTRS)
    Dim dsJJ As DataSet = New DataSet
    Dim staffDV As DataView
    Dim cb As New OleDb.OleDbCommandBuilder(da)

This code in the form load:

    da.Fill(dsJJ, "Staff") 'Populate datatable
    staffDV = New DataView(dsJJ.Tables("Staff"), "HrsPerWeek = 145", "Name", DataViewRowState.CurrentRows)
    lblRows.Text = staffDV.Count & " rows"

     'Add databindings
     cbxStaffName.DataSource = staffDV
     cbxStaffName.DisplayMember = "Name"
     cbxStaffName.ValueMember = "HrsPerWeek"

     'Lots of other databindings for my text boxes here..

This code in the 'Save' button:

      Me.BindingContext(staffDV).EndCurrentEdit()
      da.Update(dsJJ.Tables("Staff")) 'Update changes

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.