[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 789
  • Last Modified:

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()
0
Cherryex
Asked:
Cherryex
  • 4
  • 4
1 Solution
 
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
0
 
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."

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

Bob
0
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.

 
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.
0
 
Bob LearnedCommented:
What version of .NET are you working with?  What is the context--WinForms or ASP.NET?

Bob
0
 
CherryexAuthor Commented:
I am using version 2005 pro and it is WinForms.
0
 
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
0
 
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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