[RESOLVED] Detecting record change in datatable

[SpookyAwol]

Basic setup:

da = New SqlDataAdapter()
dt = New DataTable()
SQL etc
da.Fill(dt)
DataGridView1.DataSource = dt
<lots of bound text boxes>
TourDataID.DataBindings.Clear()
TourDataID.DataBindings.Add("Text", dt, "ID")

So this works as expected, user selects record from datagrid, bound text boxes update etc.

Whats the best way to update a related textbox value when a bound value changes?

eg:

    Private Sub TourDataID_TextChanged(sender As Object, e As EventArgs) Handles TourDataID.TextChanged

        'Do something

    End Sub

The reason I ask, is this seems to give the previous record ID, not the current. What trigger should I use?

[jmcilhinney]

Ideally, you should be binding via a BindingSource and then handling the appropriate event of that, e.g. CurrentItemChanged. If you don't want to use a BindingSource for some insane reason, the DataTable itself has events too.

Maybe this is just an example but I have to question why you would be changing the ID of a record in the first place. Are you actually talking about when the user selects a different record, rather than when they edit the current record? If so then that would be the CurrentChanged event of the BindingSource, rather than the CurrentItemChanged. You really ought to read the documentation for the BindingSource class and the relevant members though.

[SpookyAwol]

Yes, Im meaning when a user selects a different record and there is a bindingsouce for that record that I forgot to show.
When the user moves to a different record, Im trying to execute other code.

[jmcilhinney]

Then you should do as I instructed in my previous post. Is there an issue with that? The Current property refers to the record currently selected in the UI. When binding a DataTable, that record will be in the form of a DataRowView. When the user selects a different record, either by clicking in a grid or navigating via a BindingNavigator or some other way, the CurrentChanged event is raised. That should be all you need.

[SpookyAwol]

Should that be raised when included like so?

   Sub ClientBindingSource_CurrentChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ClientBindingSource.CurrentChanged

' code

    End Sub

        ClientBindingSource.DataSource = dt
        DataGridView1.DataSource = dt

Running a breakpoint on that code (CurrentChanged), it appears to fire once when the page loads, but not subsequently when clicking on the datagridview to navigate up and down records. Is that what should happen based on your reply?

[jmcilhinney]

If you don't bind the BindingSource to the grid, why would selecting a record in the grid affect the BindingSource?

[SpookyAwol]

Sorry, yes I missed that - however still the same issue. Navigating through the DGV does not fire the event.
Would I be correct in thinking that ClientBindingSource_CurrentChanged only fires when the Bindingsource actually changes? ie reloaded from database?
The only time it fires currently is on the first load

[jmcilhinney]

No you would not be correct in thinking that. The CurrentChanged event is raised whenever the value of the Current property changes, much as a TextBox raises TextChanged when Text changes. If you bind a BindingSource to a DataGridView and then select a different record in the grid, that record becomes the value of the Current property and so CurrentChanged is raised. If that's not happening for you then you did something wrong. As you haven't shown us what you did, it's hard to say what's wrong with it.

[SpookyAwol]

Heres a cut down example of what I am using. Its again a winform, and all code is contained within the single form (as opposed to the MidiParent application.
However the result is the same. What crucial part am I missing to show that its firing or to make it fire? Or am I simply barking up the wrong tree?
Im using a breakpoint on the sub (BindingSource1_CurrentChanged) and the code breaks to it when Sub bindGrid() is triggered.
It triggers 3 times before continuing. Then, once in the form and navigating (manually by selecting items with the mouse in the DGV) it never fires.
Is it the navigation method I am using maybe?
Thanks for sticking with me - Im sure there is still something obvious I am missing

'Option Strict On
'Option Explicit On
Imports System.Data.SqlClient

Public Class FrmNewToursManage

    Dim da As SqlDataAdapter = Nothing
    Dim dt As DataTable = Nothing

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        bindGrid()
    End Sub

    Private Sub bindGrid()

        da = New SqlDataAdapter()
        dt = New DataTable()

        Dim conxn As New SqlConnection(getConnectionString())
        Dim sql As String = "SELECT From Database........"

        da.SelectCommand = New SqlCommand(Sql, conxn)
        da.Fill(dt)

        da.UpdateCommand = New SqlCommand("UPDATE Database SQL...... WHERE ClientID = @ClientID", conxn)
        ' All parameters
        da.UpdateCommand.Parameters.AddWithValue("@ClientID", ClientID.Text)

        BindingSource1.DataSource = dt
        DataGridView1.DataSource = BindingSource1.DataSource

        AllBindings-ETC.DataBindings.Clear()
        AllBindings-Etc.DataBindings.Add("Text", dt, "ClientID")

    End Sub

    Sub BindingSource1_CurrentChanged(ByVal sender As Object, ByVal e As EventArgs) Handles BindingSource1.CurrentChanged

'Hey! This code never fires


    End Sub

    Private Function getConnectionString() As String
        Dim csb As SqlConnectionStringBuilder = New SqlConnectionStringBuilder()
        csb.DataSource = "localhost"
        csb.InitialCatalog = "PTRAVELPROD"
        csb.IntegratedSecurity = True
        Return csb.ConnectionString
    End Function

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        da.SelectCommand.Dispose()
        da.UpdateCommand.Dispose()
        da.Dispose()
    End Sub
    Private Async Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        da.Update(dt)
        Await ShowMessage()
    End Sub
    Private Async Function ShowMessage() As Task(Of Boolean)
        lblUpdate.Visible = True
        Await Task.Delay(2000)
        lblUpdate.Visible = False
        Return True
    End Function

End Class

[jmcilhinney]

First things first, you're STILL not using the BindingSource properly. Here:

AllBindings-Etc.DataBindings.Add("Text", dt, "ClientID")

you are still binding directly to the DataTable. Stop doing that! Bind the DataTable to the BindingSource and then everything else related to binding happens with the BindingSource. I don't know whether that is related to your issue or not but it's wrong so fix it and then we can talk further.

[SpookyAwol]

So with doing it this way, still no dice.
Thats all the references in the binding changed.
The DGV and textbox results still navigate as expected

ClientID.DataBindings.Add("Text", BindingSource1.DataSource, "ClientID")

[jmcilhinney]

Argh! I do wish that people could follow instructions. I said that you bind the BindingSource to the grid, not the DataSource of the BindingSource. The DataSource of the BindingSource is the DataTable so you're still just binding the DataTable. I missed this the first time:

DataGridView1.DataSource = BindingSource1.DataSource

Get rid of it and do what I said, i.e. bind the BindingSource to the controls. If you had looked at any examples then you would have seen how to do it and it's NOT like that.

BindingSource1.DataSource = dt
DataGridView1.DataSource = BindingSource1
AllBindings-Etc.DataBindings.Add("Text", BindingSource1, "ClientID")

That's it, that's all.

[SpookyAwol]

" I said that you bind the BindingSource to the grid, not the DataSource "
I honestly thought that was a shorthand of the same thing. Thank you so much for pointing me in the right direction.

[jmcilhinney]

How could it be the same thing? This:

BindingSource1.DataSource = dt
DataGridView1.DataSource = BindingSource1.DataSource

is functionally equivalent to this:
BindingSource1.DataSource = dt

Dim x = BindingSource1.DataSource

DataGridView1.DataSource = x
[/HIGHLIGHT]
You put a DataTable into the DataSource property of the BindingSource so you get a DataTable out, so x refers to a DataTable. x is equal to dt. You then bind that DataTable directly to the grid, which is what I specifically said not to do.