We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Save changes from dataview to database

Cherryex
Cherryex asked
on
Medium Priority
821 Views
Last Modified: 2008-01-09
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()
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
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

Author

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

     
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

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

Bob

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
What version of .NET are you working with?  What is the context--WinForms or ASP.NET?

Bob

Author

Commented:
I am using version 2005 pro and it is WinForms.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
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
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.