none
Bindingsource problem RRS feed

  • Question

  • I have a form with several controls bound to a bindingsource. The bindingsource is bound to a datatable in a dataset. So far no problem.

    I want de user to be able to edit and add records to the underlying datasource and have de database engine determine if the newly added records are OK (Unique constraints, Fk constraints etc.)

    First problem is that when i edit a record, changes made in the bound controls immediately show up in de datasource (datatable), and the rowstate in the table is set to modified, which is fine, however........when i call the datatable.getschanges method, it returns the correct (changed) row, but with the OLD values, not the newly edited ones. Even though in the tables.rows(indexofchangedrow) these changes are clearly visible?????? I dont understand why this is happening.

     

    Second problem....In order to avoid the above behaviour i call the bindingsource.endedit method, thereby forcing the update of the datasource. However.....if the database engine now returns an error (primary key constraint or something else), i have lost the binding of the controls on the form to the datatable, why is that?

     

    Any help would be appreciated

    Jan Dirk

    Monday, August 8, 2011 1:12 PM

Answers

  • In button3 Click event have you tried the EndEdit method of the BindngSouce i.e. BndSource.EndEdit rather than Me.Validate which I am not sure what this is doing to the data as it appears Me is the form. This also goes back to Accepting changes to the current data row as I suggested earlier.

    Outside the current issue, Button1 and Button 2 could use BndSource.MovePrevious and BndSource.MoveNext

    Sorry I did not reply earlier been in meetings.


    KSG
    • Marked as answer by jdbussman Wednesday, August 10, 2011 1:41 PM
    Tuesday, August 9, 2011 8:28 PM
    Moderator
  • The endedit method will work, but according to microsoft the datarow should be updated automatically, without the need for an endedit.

    See

    http://msdn.microsoft.com/en-us/library/system.windows.forms.bindingsource.endedit.aspx 

    under REMARKS

    And the problem is still that the databinding fails on the (Binding)List (of datarow) and the Array (of datarow).

    I did some further investigation, and the problem does not seem to be the list or collection, but the fact that i bind to a datarow. It seems you cant bind to a datarow, only to the table containing the datarow.....hence, a list (of datarow) would not work either. I created a workaround for this issue that solves it for me right now.

    The question with the differences between rows from the table.getchanges.rows collection and the table.rows collection is still there, its strange, but i can easily work around that issue too.

    I cant do the acceptchanges as you suggest, because the DB may report a problem with the udate and then i need to be able to 'rollback' the changes made in the datarow.

    Thanks for the tip on the moveprevious....movenext methods, that is a better way of doing it!

    All this being said......i guess i have enough answers to work around the issues. Thanks for all your help and tips.

    Jan Dirk

    • Marked as answer by jdbussman Wednesday, August 10, 2011 1:42 PM
    Wednesday, August 10, 2011 1:41 PM

All replies

  • Hi,

    Would you mind showing the code you have, and do some comments beside where are errors, or where is something to do...

    It will be easier to help.

    thx in advance.

    ------------------------

    Things to add here:

    I would suggest a bit different approact, so 1st you get the data out of the dataBase into a dataTable (if you have more tables, then DataSet). Do your bindings and do not update ot insert new values (or changed ones) streight into the dataBase - but do that on the end, when leaving a form or closing it.

    In the mean time, when you do the work (updating, deleting and insertings) do only the checking (Validating) if the new values are suitable to insert (or update). 

    So as said, when you will be about to close the form, do the update over dataBase (use SqlDataAdapter class and Update query). This way you will get rid of constant intercation with the dataBase.

    This is my preposition.


    Mitja
    Monday, August 8, 2011 1:15 PM
  • I may very well by jumping in to quickly without knowing more details but here is a thought. Use a child form with controls (such as Textboxes) data bound to your BindingSource where the BindingSource DataSource for this example is a DataTable.

    The following example binds several TextBoxes to fields in a BindingSource which is bound to a DataTable. There are two buttons, save where the DialogResult is OK and the other cancel button DialogResult is Cancel. If the user pressed save then we save changes back to the backend database then update the current DataRow or if they cancelled we reject changes and nothing changes.

    Private Sub EditCurrentCustomerBound()
      Dim f As New frmCustomerDialog
    
      Try
        f.txtCompanyName.DataBindings.Add("Text", bsCustomers, "CompanyName")
        f.txtContactName.DataBindings.Add("Text", bsCustomers, "ContactName")
    
        If f.ShowDialog = Windows.Forms.DialogResult.OK Then
          ' Save back to database then execute the following
          bsCustomers.CurrentRow.AcceptChanges()
        Else
    			bsCustomers.CurrentRow.RejectChanges()
        End If
      Finally
        f.Dispose()
      End Try
    
    End Sub
    


    Place the following language extensions in a modules

      <System.Diagnostics.DebuggerStepThrough()> _
      <System.Runtime.CompilerServices.Extension()> _
      Public Function CurrentRow(ByVal sender As BindingSource) As DataRow
       Return DirectCast(sender.Current, DataRowView).Row
      End Function
    
    


     

     

     


    KSG
    Monday, August 8, 2011 1:58 PM
    Moderator
  • Mitja, Kevin
    I actually found the reason for the second problem:
    I used a filterstring on the primary key of the datatable to set the bindingsource filter to only bind to the filtered datarows. Hence if the user changes the primary key....bingo the filter filters out the changed record and the binding is lost. I am now trying to NOT use the filter on primary key anymore, but instead generate a collection or list of datarows and set that list as the datasource for the bindingsource. (i use multiple datarows to bind to the form, one at a time, dependant on the position property of the bindingsource). However i cant get the databinding to work with a list (of datarows) as the datasource of the bindingsource?????
    Further.....I ran into a lot (and i mean a lot) of problems having the dataset implement and check all the database constraints so that i could do all the checking of valid inputs without accessing the database. I decided that one, and only one, entity should saveguard database integrity, and decided to use the database for that purpose.
    So here is how it works:
    Datatable is filled by dataadapter from database
    Datagridview is bound to datatable to display the table
    User selects one or more rows in the datagridview
    Those rows are then tracked back to the datatable and put in a list (of datarows) or in a collection (i tried both)
    That list becomes the datasource for a bindingsource
    An Edit form is launched with all the controls for editing the datarows (the form has buttons to edit one, or all datarows and Prev/Next to walk through the list(of datarows)
    The databindings for the controls are set referencing the above bindingsource <<<<<<< this is where i get an error indicating "Cant bind to the property or column ....... for datasource. Parameter name: Datamember"
    User edits the rows and presses OK
    EditForm.validate and Bindingsource.endedit is called
    Dataadapter updates the database
    Database reports an error in the update
    User gets a messagebox indicating what corrective actions to take
    User is send back to the (still bound) editform to correct mistakes
    Upon hitting Cancel, Editform.validate, Bindingsource.endedit and datatable.rejectchanges are called
    I use many many forms, so i wrote a generic handler for all forms to handle databinding. here it is.....
      Dim BndSource As BindingSource
      ''' <summary>
      ''' Generic handler for Form.Shown event, gets assigned with addhandler in Form.Load handler
      ''' </summary>
      ''' <param name="sender"></param>
      ''' <param name="e"></param>
      ''' <remarks></remarks>
      Public Sub Form_ShownHndlr(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim MyForm As Form
        If Not TypeOf sender Is Form Then Return
        MyForm = sender
    
        Dim DrColl As Collection
    
        'The selected datarows are send to the form as a collection in the Tag property
         If Not TypeOf MyForm.Tag Is Collection Then Return
        DrColl = MyForm.Tag
        If Not DrColl.Count >= 1 Then Return
    
        BndSource = New BindingSource
        BndSource.DataSource = DrColl
    
        Dim DBnd As Binding
        For Each TestControl As Control In GetAllControlsOf(MyForm)
          'First remove any old bindings
          TestControl.DataBindings.Clear()
          DBnd = Nothing
    
          If TypeOf TestControl Is TextBox Or TypeOf TestControl Is ComboBox Then
     >>>ERROR HERE        DBnd = TestControl.DataBindings.Add("Text", BndSource, TestControl.Name, True, DataSourceUpdateMode.OnPropertyChanged, "")
            DBnd.DataSourceNullValue = System.DBNull.Value
            DBnd.NullValue = ""
            DBnd.ControlUpdateMode = ControlUpdateMode.OnPropertyChanged
          ElseIf TypeOf TestControl Is CheckBox Then
            DBnd = TestControl.DataBindings.Add("Checked", BndSource, TestControl.Name, False, DataSourceUpdateMode.OnPropertyChanged, False)
            DBnd.ControlUpdateMode = ControlUpdateMode.OnPropertyChanged
          End If
        Next TestControl
      End Sub
    
    
    
    And when the user presses OK the OKBtnHndlr is called:
      Public Sub OKBtnHndlr(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim DrColl As Collection
        Dim Tabel As DataTable = Nothing
        Dim MyForm As Form
    
        MyForm = sender.FindForm
    
        DrColl = MyForm.Tag
        Tabel = DrColl(1).table
    
        MyForm.Validate()
        BndSource.EndEdit()
    
        If Save(Tabel) = SaveResult.SaveOk Then
          MyForm.DialogResult = DialogResult.OK
          MyForm.Close()
        Else
        End If
      End Sub
    
    

    This handler calls a save procedure that uses the dataadapter.update method along with some other logic and returns SaveOK when the uipdate succeeded.
    The first problem in my first post is still a big questionmark to me, why are there differences between the rows returned in the datatable.getchanges result and the rows directly accessed in the datatable???

    Thanks for your help so far
    Jan Dirk
    Monday, August 8, 2011 9:21 PM
  • In this line is the Name property of TestControl the column name you want to bind too?

    TestControl.DataBindings.Add("Text", BndSource, TestControl.Name, True, DataSourceUpdateMode.OnPropertyChanged
    


    Suppose we want to bind to a column name ContactName we would use

    TestControl.DataBindings.Add("Text", BndSource, "ContactName", True, DataSourceUpdateMode.OnPropertyChanged
    

     

     


    KSG
    Tuesday, August 9, 2011 12:12 AM
    Moderator
  • Kevin,

    Yes, all the forms i use have contols with names equal to the column names they bind to.

    Furthermore.... the procedure call GetAllControlsOf(MyForm) returns a collection with all controls on a form, including the nested controls inside container controls.

    Sorry, i should have clarified that...

    Jan Dirk

    Tuesday, August 9, 2011 9:03 AM
  • I created a little testprogram to isolate the problems with the databinding:

    Public Class Form1
     ''' <summary>
     ''' Create a form with 3 textboxes named "ParentID", "Parentname" and "Comments", as well as 3 buttons
     ''' Button 1 labeled "Previous" and button 2 labeled "Next" and button 3 labeled "Test"
     ''' First of all, databionding to anything except the datatable will result in error
     ''' With the datatable as datasource edit the FIRST commentfield (ID = 1) and hit the testbutton......notice the difference?????
     ''' </summary>
     ''' <remarks></remarks>
    
     Dim TestTable As DataTable
     Dim BndSource As BindingSource
     Dim Dset As DataSet
    
     Private Sub Form1_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
      'Define the test datatable
      TestTable = New DataTable
      TestTable.Columns.Add("ParentID", GetType(Integer))
      TestTable.Columns.Add("Parentname", GetType(String))
      TestTable.Columns.Add("Comments", GetType(String))
    
      'Lets put it in a dataset
      Dset = New DataSet
      Dset.Tables.Add(TestTable)
    
      'Fill the testtable with 100 records
      For Teller As Integer = 1 To 100
       Dim NwRow As DataRow
       NwRow = TestTable.NewRow
       NwRow(0) = Teller
       NwRow(1) = "ParentNumber " & Teller
       NwRow(2) = "Default Comment"
       TestTable.Rows.Add(NwRow)
      Next
    
      'Now define a list of datarows as a testdatasource
      Dim BndList As New List(Of DataRow)
      BndList.Add(TestTable.Rows(5))
      BndList.Add(TestTable.Rows(10))
      BndList.Add(TestTable.Rows(11))
      BndList.Add(TestTable.Rows(3))
    
      'Also define an array of datarows as testdatasource
      Dim BndArray(4) As DataRow
      BndArray(0) = (TestTable.Rows(5))
      BndArray(1) = (TestTable.Rows(10))
      BndArray(2) = (TestTable.Rows(11))
      BndArray(3) = (TestTable.Rows(3))
    
    
    
      'Create a bindingsource to test all of the above datasources
      BndSource = New BindingSource
    
      '>>>>>>>>>UnComment One of the below datasource assignments to test<<<<<<<<<<<<<
      BndSource.DataSource = BndList
      'BndSource.DataSource = TestTable
      'BndSource.DataSource = BndArray
    
      'Create the bindings to the controls on the form
      Dim DBnd As Binding
      For Each TestControl As Control In Me.Controls
       If TypeOf TestControl Is TextBox Then
        TestControl.DataBindings.Clear()
        DBnd = TestControl.DataBindings.Add("Text", BndSource, TestControl.Name, True, DataSourceUpdateMode.OnPropertyChanged, "")
        DBnd.DataSourceNullValue = System.DBNull.Value
        DBnd.NullValue = ""
        DBnd.ControlUpdateMode = ControlUpdateMode.OnPropertyChanged
       End If
    
      Next
    
    
     End Sub
    
     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      BndSource.Position -= 1
     End Sub
    
     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
      BndSource.Position += 1
     End Sub
    
     Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
      Me.Validate()
      'Changes should have been send to the datasource immediately after the bound control looses focus, or me.validate was called
      Try
       MessageBox.Show(TestTable.GetChanges.Rows(0)("Comments") & vbCrLf & TestTable.Rows(0)("Comments"))
      Catch
      End Try
    
     End Sub
    End Class
    
    

    Hope this helps to clarify the 2 problems

     

    Jan Dirk

     


    Tuesday, August 9, 2011 11:15 AM
  • In button3 Click event have you tried the EndEdit method of the BindngSouce i.e. BndSource.EndEdit rather than Me.Validate which I am not sure what this is doing to the data as it appears Me is the form. This also goes back to Accepting changes to the current data row as I suggested earlier.

    Outside the current issue, Button1 and Button 2 could use BndSource.MovePrevious and BndSource.MoveNext

    Sorry I did not reply earlier been in meetings.


    KSG
    • Marked as answer by jdbussman Wednesday, August 10, 2011 1:41 PM
    Tuesday, August 9, 2011 8:28 PM
    Moderator
  • The endedit method will work, but according to microsoft the datarow should be updated automatically, without the need for an endedit.

    See

    http://msdn.microsoft.com/en-us/library/system.windows.forms.bindingsource.endedit.aspx 

    under REMARKS

    And the problem is still that the databinding fails on the (Binding)List (of datarow) and the Array (of datarow).

    I did some further investigation, and the problem does not seem to be the list or collection, but the fact that i bind to a datarow. It seems you cant bind to a datarow, only to the table containing the datarow.....hence, a list (of datarow) would not work either. I created a workaround for this issue that solves it for me right now.

    The question with the differences between rows from the table.getchanges.rows collection and the table.rows collection is still there, its strange, but i can easily work around that issue too.

    I cant do the acceptchanges as you suggest, because the DB may report a problem with the udate and then i need to be able to 'rollback' the changes made in the datarow.

    Thanks for the tip on the moveprevious....movenext methods, that is a better way of doing it!

    All this being said......i guess i have enough answers to work around the issues. Thanks for all your help and tips.

    Jan Dirk

    • Marked as answer by jdbussman Wednesday, August 10, 2011 1:42 PM
    Wednesday, August 10, 2011 1:41 PM