Data binding update problem

[ddrees]

I must be missing something obvious.

In VB.Net, I have a windows form. On the form is a SQLDataAdapter, SQLConnection and a dataset. The controls on the form are bound to fields in the dataset. The proper data shows up where it is supposed to. The problem is that if I change the data on the form, it doesn't update the dataset row. According to the on-line help, in the article Introduction to Dataset Updates,

In Windows Forms, the data-binding architecture takes care of sending changes from data-bound controls to the dataset, and you do not have to explicitly update the dataset with your own code.

However, it doesn't seem to be working. I created a button with the following code behind it:

Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
     MessageBox.Show(DsContact1.Tables(0).Rows(0).RowState.ToString)
End Sub

No matter what changes I make to the record, the result each time when I click the button is "Unchanged". Is there a setting somewhere that I am missing which forces the changes I make on the form to be reflected in the dataset?

[BryanJ]

The dataset is being updated by the controls on the form but the database is not updated. You have to call the update method in the dataAdapter.

Something like Adapter.Update(Dataset)

Where :

Adapter is your dataAdapter and Dataset is the dataset bound to the controls.

[ddrees]

BryanJ,

Thank you for your response. I'm sorry that I failed to mention that I had tried using the update method. However, the problem is that the dataset is not being updated by the controls; therefore the database is not updated when I call the update method. Everything I read tells me that the dataset should be updated by the controls, but it doesn't seem to be so.

I've tracked down the problem to the the fact that it never senses that the row is "Modified", but sees the row as "Unchanged" so it never pushes the data back to the database.

Some additional information may help (or may not ). The dataAdapter uses the following SQL statement:
SELECT *
FROM Contact
WHERE ContactID = @ContactID;

where @ContactID is a parameter to the query. When someone selects a contact on another form and clicks a button labeled "Edit", the following code runs:

Private Sub btnContactEdit_Click(...) Handles btnContactEdit.Click
        Dim f As New ContactEdit()
        f.ContactID = cboContact.SelectedValue
        f.ShowDialog()
End Sub

where ContactID is a public property of the ContactEdit form. Then, in the form's load event the following code happens:

Private Sub ContactEdit_Load(...) Handles MyBase.Load
        SqlDataAdapter1.SelectCommand.Parameters(0).Value = Me.ContactID
        DsContact1.Clear()
        SqlDataAdapter1.Fill(DsContact1)
End Sub

So, the dataset should contain only one row. The 'OK' button has the following code:

Private Sub btnOK_Click(...) Handles btnOK.Click
        'MessageBox.Show(DsContact1.Tables(0).Rows(0).RowState.ToString)
        SqlDataAdapter1.Update(DsContact1)
        DsContact1.AcceptChanges()
        DsContact1.Clear()
        Me.Dispose()
End Sub

But the database is never updated. The messagebox was for testing purposes, and each time would say "Unchanged".

Does this help explain my situation? Is it because I have only one row in the dataset that this is a problem? Has anyone run into this before? This is my first attempt at datasets in VB.Net, and so far, it hasn't been encouraging.

[BryanJ]

I found that if the cell that is being changed still has the focus then the datasource is not updated. The updating only occurs once the cell has lost its focus.

I am still working on the problem - Read DataSet Hell - just posted

[Mr.No]

Strange, I just spent the whole day trying to solve the same problem as in this thread.

This is how I solved my problem.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click        
        Dim myrow As DataRow
      
       ' Get a reference to the current row being edited
        myrow = DsCustomers1.Customers.Item(Me.BindingContext(DsCustomers1, "customers").Position)
        myrow.EndEdit()
 
        SqlDataAdapter1.Update(DsCustomers1)
        MessageBox.Show("Customer Data updated")
    End Sub

You'll have to replace all occurance of "Customer" with "Contact" in your code. It appears that the Dataset does not expose a property to indicate which row is being edited.

[SjR]

I apologise if this has already been suggested and fixed or whatever - I have a short attention span when it comes to reading posts.

If you add the line

Me.BindingContext(dsContact1.Tables(0)).EndCurrentEdit()

directly before the MsgBox bit, it should work.

For some reason, as I've found, if you access values in the Dataset without doing the EndCurrentEdit bit, it reverts back to the original value, hence unchanged.

Hope this is of some use - and sorry if someone else has already answered with this - I'm not poaching your code (this time )

[Mr.No]

Thanks SjR

I hadn't noticed the EndCurrentEdit() method on the Table object of the dataset. This resolves the issue of identifying the current row.