Question Databinding textbox does not refresh on the form after update occured

[Mimosa777]

Hi everyone,

I've been trying to fix that issue for some times now and i need your help please.
Basically, i have 2 forms (form1 and form2). I have a set of textboxes on form1 that are bind on a dataset via a bindingsource. when form1 is loaded, all textboxes are filled in correctly. Now i want to update one textbox on form1 via form2. Basically form2 is just a simple form with one textbox and a button. The user is asked to put the value he wants in that textbox on form2 and when he clicked on the button, it saves the new value in the database - then form2 get closed and we are back into form1 (main form).
here is my code to do that :

Private Sub btnUpdateKM_Click(sender As Object, e As EventArgs) Handles btnUpdateKM.Click
        'open DB connection
        Conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|dbServauto.mdb")
        Conn.Open()
        'start Transaction
        Trans = Conn.BeginTransaction
        Dim KM = tbKM.Text
        Dim IDVehicule = tbVehiculeID.Text

        'define a command for the sql statement
        Cmd = New OleDb.OleDbCommand("update tblCars set KM= " & KM & " where ID=:0", Conn, Trans)
        Cmd.Parameters.AddWithValue(":0", IDVehicule)
        'execute the command
        Cmd.ExecuteNonQuery()
        'save the transaction
        Trans.Commit()
        'close the connection
        Conn.Close()
        'Form1.KMTextBox.Text = tbKM.Text
        tbKM.Text = ""
        Me.Close()
    End Sub

and here is how my textbox in binded on form1 :

KMTextBox.DataBindings.Clear()
KMTextBox.DataBindings.Add(New Binding("text", bindingcar, "KM"))

Everything works fine with the update. All I want to see now is that new value showing in form1 without having to close my app and open it again. I've tried the bindingsource.resetbinding method but did not work.

The only way I found which I believe is not the correct way is to directly change the form1.textbox.text = form2.textbox.text which shows the new value as I wanted but my bindingsource is still having in memory the old value.

Hope I was clear enough and thanks in advance for your help. :encouragement:

 

[jmcilhinney]

The user is asked to put the value he wants in that textbox on form2 and when he clicked on the button, it saves the new value in the database - then form2 get closed and we are back into form1 (main form).

That's your problem right there. That's the wrong approach. When Form1 opens Form2 it should pass the current data to it. When Form2 closes, Form1 should retrieve the new data from it. Form1 is now up to date because it already has the new data. If the new data needs to be saved, Form1 should do it. Form2 shouldn't know anything about Form1 or the database.

 

[Mimosa777]

That's your problem right there. That's the wrong approach. When Form1 opens Form2 it should pass the current data to it. When Form2 closes, Form1 should retrieve the new data from it. Form1 is now up to date because it already has the new data. If the new data needs to be saved, Form1 should do it. Form2 shouldn't know anything about Form1 or the database.

Ok thank you - I'll try and make the appropriate changes - thank you again

 

[jmcilhinney]

The code will look something like this:

Using dialogue As New Form2
    Dim currentRow = DirectCast(myBindingSource.Current, DataRowView)

    dialogue.SomeProperty = CStr(currentRow("SomeColumn"))

    If dialogue.ShowDialog() = DialogResult.OK Then
        currentRow("SomeColumn") = dialogue.SomeProperty

        'This might be done now or you might save multiple edits in a batch later.
        myDataAdapter.Update(myDataTable)
    End If
End Using

 

[Mimosa777]

I really appreciate your help - im testing right now and will let you know. thanks JMC

 

[Mimosa777]

The code will look something like this:

Using dialogue As New Form2
    Dim currentRow = DirectCast(myBindingSource.Current, DataRowView)

    dialogue.SomeProperty = CStr(currentRow("SomeColumn"))

    If dialogue.ShowDialog() = DialogResult.OK Then
        currentRow("SomeColumn") = dialogue.SomeProperty

        'This might be done now or you might save multiple edits in a batch later.
        myDataAdapter.Update(myDataTable)
    End If
End Using

Hi ,

so I used your code on my form1 button as follow :

Private Sub btnUpdateKM_AddF_Click(sender As Object, e As EventArgs) Handles btnUpdateKM_AddF.Click
       
        Dim Conn As OleDb.OleDbConnection
        Dim Trans As OleDb.OleDbTransaction
        Dim Cmd As OleDb.OleDbCommand
        
        Using dialogue As New Form2
            Dim currentrow = DirectCast(bindingcar.Current, DataRowView)
            dialogue.tbKM.Text = CStr(currentrow("KM"))
            If dialogue.ShowDialog() = DialogResult.OK Then
                currentrow("KM") = dialogue.tbKM.Text
                daCars.Update(dscar, "Cars")
            End If

            'open DB connection
            Conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|dbServauto.mdb")
            Conn.Open()

            'start Transaction
            Trans = Conn.BeginTransaction

            Dim KM = dialogue.tbKM.Text
            Dim IDVehicule = bindingcar.Current("ID").ToString

            'define a command for the sql statement
            Cmd = New OleDb.OleDbCommand("update tblCars set KM= " & KM & " where ID=:0", Conn, Trans)
            Cmd.Parameters.AddWithValue(":0", IDVehicule)

            'execute the command
            Cmd.ExecuteNonQuery()

            'save the transaction
            Trans.Commit()
       
 End Using
 
    End Sub

Just so you know, on my form1, I have a combobox where I select a customer and then the car information of that customer are showing in textboxes. then I try to update KM textbox which is the kilometer usage of the car. So when I click on the btnUpdateKM, code is executed as you can see above.

I have one issue with that:

First, the value is indeed updated on my form1 but not instantly - I cant see it unless I reselect the customer in my combobox , then I see th change in KM textbox

Am I missing something ?

 

[jmcilhinney]

Firstly, don't create a command builder every time you edit a record. You only need one command builder so, if you're going to use one, create it when you create the data adapter.

As for your issues:

1. I'm not sure why just setting the field is not enough to update the bound control but, to force it, you can call ResetCurrentItem on the BindingSource straight after setting the field.

2. The data is either saved or it's not. If it is then it won't spontaneously disappear. Firstly, test the value returned by Update to see whether it's zero or not. If it's not zero then the change is being saved. In that case, you must be retrieving data from a different place to where you saved it. There are a number reasons that that might happen. The most likely is that you're using a local data file that is overwritten on each build. To see whether that might be the case, please post your connection string.

 

[Mimosa777]

I got it - bindingcar.resetbindings(false) now works perfectly .

and here is my connection string in my btnUpdateKM code and this code now works perfectly.

Private Sub btnUpdateKM_AddF_Click(sender As Object, e As EventArgs) Handles btnUpdateKM_AddF.Click
        Dim Conn As OleDb.OleDbConnection
        Dim Trans As OleDb.OleDbTransaction
        Dim Cmd As OleDb.OleDbCommand
        Using dialogue As New Form2
            Dim currentrow = DirectCast(bindingcar.Current, DataRowView)
            dialogue.tbKM.Text = CStr(currentrow("KM"))
            If dialogue.ShowDialog() = DialogResult.OK Then
                currentrow("KM") = dialogue.tbKM.Text
            End If

            'open DB connection
            Conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|dbServauto.mdb")
            Conn.Open()
            'start Transaction
            Trans = Conn.BeginTransaction
            Dim KM = dialogue.tbKM.Text
            Dim IDVehicule = bindingcar.Current("ID").ToString
            'define a command for the sql statement
            Cmd = New OleDb.OleDbCommand("update tblCars set KM= " & KM & " where ID=:0", Conn, Trans)
            Cmd.Parameters.AddWithValue(":0", IDVehicule)
            'execute the command
            Cmd.ExecuteNonQuery()
            'save the transaction
            Trans.Commit()
        End Using
        daCars.Update(dscar, "Cars")
        bindingcar.ResetBindings(False)
    End Sub

 

[jmcilhinney]

I got it - bindingcar.resetbindings(false) now works perfectly .

and here is my connection string in my btnUpdateKM code and this code now works perfectly.

Private Sub btnUpdateKM_AddF_Click(sender As Object, e As EventArgs) Handles btnUpdateKM_AddF.Click
        Dim Conn As OleDb.OleDbConnection
        Dim Trans As OleDb.OleDbTransaction
        Dim Cmd As OleDb.OleDbCommand
        Using dialogue As New Form2
            Dim currentrow = DirectCast(bindingcar.Current, DataRowView)
            dialogue.tbKM.Text = CStr(currentrow("KM"))
            If dialogue.ShowDialog() = DialogResult.OK Then
                currentrow("KM") = dialogue.tbKM.Text
            End If

            'open DB connection
            Conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|dbServauto.mdb")
            Conn.Open()
            'start Transaction
            Trans = Conn.BeginTransaction
            Dim KM = dialogue.tbKM.Text
            Dim IDVehicule = bindingcar.Current("ID").ToString
            'define a command for the sql statement
            Cmd = New OleDb.OleDbCommand("update tblCars set KM= " & KM & " where ID=:0", Conn, Trans)
            Cmd.Parameters.AddWithValue(":0", IDVehicule)
            'execute the command
            Cmd.ExecuteNonQuery()
            'save the transaction
            Trans.Commit()
        End Using
        daCars.Update(dscar, "Cars")
        bindingcar.ResetBindings(False)
    End Sub

That code is just so wrong I don't even know where to start. You've got so much going on in there that is not part of what I said. It's not part of what I said because it's not needed so get rid of it. All you had to do was add call to ResetCurrentItem after setting the field and instead you've added a whole lot of pointless garbage.

 

[Mimosa777]

oh ok - so here the updated code :

[Private Sub btnUpdateKM_AddF_Click(sender As Object, e As EventArgs) Handles btnUpdateKM_AddF.Click
        Using dialogue As New Form2
            Dim currentrow = DirectCast(bindingcar.Current, DataRowView)
            dialogue.tbKM.Text = CStr(currentrow("KM"))
            If dialogue.ShowDialog() = DialogResult.OK Then
                currentrow("KM") = dialogue.tbKM.Text
                bindingcar.ResetCurrentItem()
            End If
        End Using
        daCars.Update(dscar, "Cars")
    End Sub

but the thing is as soon as I remove the pointless garbage :applause: which is this part I believe :

'open DB connection
             Conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|dbServauto.mdb")
            Conn.Open()
             'start Transaction
             Trans = Conn.BeginTransaction
             Dim KM = dialogue.tbKM.Text
             Dim IDVehicule = bindingcar.Current("ID").ToString
             'define a command for the sql statement
             Cmd = New OleDb.OleDbCommand("update tblCars set KM= " & KM & " where ID=:0", Conn, Trans)
             Cmd.Parameters.AddWithValue(":0", IDVehicule)
             'execute the command
             Cmd.ExecuteNonQuery()
             'save the transaction
             Trans.Commit()

the update is not happening in the DB, that is why I tought I needed it.

this is my form_load code when I create my dataadapter and fill my dataset :

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|dbServauto.mdb"
        con.Open()
        sqlC = "SELECT * from tblCustomers"
        daCustomers = New OleDb.OleDbDataAdapter(sqlC, con)
        daCustomers.Fill(ds, "Customers")
        bindingcust.DataSource = ds.Tables("Customers")
        dgCustomers.DataSource = bindingcust
       
 sqlV = "SELECT * from tblCars"
        daCars = New OleDb.OleDbDataAdapter(sqlV, con)
        daCars.Fill(dscar, "Cars")
        bindingcar.DataSource = dscar.Tables("Cars")
        dgCars.DataSource = bindingcar


[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]con.Close()[/SIZE][/FONT][/SIZE][/FONT]
End Sub
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]
[/SIZE][/FONT][/SIZE][/FONT]

 

[jmcilhinney]

oh ok - so here the updated code :

[Private Sub btnUpdateKM_AddF_Click(sender As Object, e As EventArgs) Handles btnUpdateKM_AddF.Click
        Using dialogue As New Form2
            Dim currentrow = DirectCast(bindingcar.Current, DataRowView)
            dialogue.tbKM.Text = CStr(currentrow("KM"))
            If dialogue.ShowDialog() = DialogResult.OK Then
                currentrow("KM") = dialogue.tbKM.Text
                bindingcar.ResetCurrentItem()
            End If
        End Using
        daCars.Update(dscar, "Cars")
    End Sub

but the thing is as soon as I remove the pointless garbage :applause: which is this part I believe :

'open DB connection
             Conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|dbServauto.mdb")
            Conn.Open()
             'start Transaction
             Trans = Conn.BeginTransaction
             Dim KM = dialogue.tbKM.Text
             Dim IDVehicule = bindingcar.Current("ID").ToString
             'define a command for the sql statement
             Cmd = New OleDb.OleDbCommand("update tblCars set KM= " & KM & " where ID=:0", Conn, Trans)
             Cmd.Parameters.AddWithValue(":0", IDVehicule)
             'execute the command
             Cmd.ExecuteNonQuery()
             'save the transaction
             Trans.Commit()

the update is not happening in the DB, that is why I tought I needed it.

Firstly, I call Update inside the If block in my example so why would you do different? There's no point saving changes if the user didn't make any, is there?

Secondly, the code I showed you should update the database so, if it doesn't, you are doing something wrong. If you do as I instructed then I can help you work out what that is without an ugly hack.

The data is either saved or it's not. If it is then it won't spontaneously disappear. Firstly, test the value returned by Update to see whether it's zero or not. If it's not zero then the change is being saved. In that case, you must be retrieving data from a different place to where you saved it. There are a number reasons that that might happen. The most likely is that you're using a local data file that is overwritten on each build. To see whether that might be the case, please post your connection string.

The connection string that you're using in the code you posted suggests that the issue is exactly what I suspected it was. Have you added your MDB file to the project in the Solution Explorer? If so, what is its Copy To Output Directory property set to?

 

[Mimosa777]

ok I've added my .mdb file to the solution explorer and the Copy To Output Directory property is set to : always copy
I rebuild the solution and now I can see dbServuato.mdb file in my \bin\Release folder

 

[jmcilhinney]

You should follow the first link in my signature below to learn a bit about how local data files are managed. The synopsis is:

1. Add the file, be it MDB, ACCDB, MDF, SDF or whatever, to your project. That becomes the source data file, in which you build the schema and to which you add any default data.
2. When you build your project the EXE is written to the output folder and a copy of the data file is made in the same folder. That should be `bin\Debug` when debugging and then `bin\Release` when you're ready to deploy. It's that copy that you connect to at run time. At run time, "|DataDirectory|" resolves to the path of the folder the EXE was run from in a Windows app not deployed using ClickOnce.
3. The default value for `Copy to Output Directory` is `Always Copy` so that means that any changes you have made to the working database ill be lost each time you build. If you don't want that then change it to `Copy If Newer`. Despite what that link implies, it's very rare that that will result in a new copy unless you actually change the schema or data of the source file.

 

[Mimosa777]

You should follow the first link in my signature below to learn a bit about how local data files are managed. The synopsis is:

1. Add the file, be it MDB, ACCDB, MDF, SDF or whatever, to your project. That becomes the source data file, in which you build the schema and to which you add any default data.
2. When you build your project the EXE is written to the output folder and a copy of the data file is made in the same folder. That should be `bin\Debug` when debugging and then `bin\Release` when you're ready to deploy. It's that copy that you connect to at run time. At run time, "|DataDirectory|" resolves to the path of the folder the EXE was run from in a Windows app not deployed using ClickOnce.
3. The default value for `Copy to Output Directory` is `Always Copy` so that means that any changes you have made to the working database ill be lost each time you build. If you don't want that then change it to `Copy If Newer`. Despite what that link implies, it's very rare that that will result in a new copy unless you actually change the schema or data of the source file.

ok thank you JMC - I have some reading to do since im obviously very confused with the database connection and links. Ill definitely look into your signature and learn starting right now. Hope ill resolve my problem by then
thanks again

 

[Mimosa777]

Hi mate,

so I believe I've fix my connection issue and have now the current mdb in my project. When I build the solution in debug mode, I can see the copy of my dbServauto in \bin\Debug\ Folder and so on.

Back to my issue with updating the KM field, I've tried to test the update value like this (in a msgbox) to see what the result is :

Private Sub btnUpdateKM_AddF_Click(sender As Object, e As EventArgs) Handles btnUpdateKM_AddF.Click
        Using dialogue As New Form2
            Dim cb As New OleDb.OleDbCommandBuilder(daCars)
            Dim currentrow = DirectCast(bindingcar.Current, DataRowView)
            dialogue.tbKM.Text = CStr(currentrow("KM"))
            If dialogue.ShowDialog() = DialogResult.OK Then
                currentrow("KM") = dialogue.tbKM.Text
                bindingcar.ResetCurrentItem()
                daCars.Update(dscar, "Cars")
            End If
            'test the value if its being updated in DB
            MsgBox(dscar.Tables("Cars").Rows(0).Item("KM").ToString)
        End Using
    End Sub

I don't know if that is what you meant by testing the update but this is how I usually test to see if my values are being updated in my dataset. Each time I run the application, even after the update, the msgbox always show the old value. Please bear with me if that is not the way to test, I just don't know then how to test the value return by the update.

The confusing part is that I use almost the same code to save a customer info and it works perfectly and here is my code :

Private Sub btnSaveClient_Click(sender As Object, e As EventArgs) Handles btnSaveClient.Click
                Dim row = DirectCast(bindingcust.Current, DataRowView)
                row("Company") = UCase(tbCompagnie.Text)
                row("Adress") = UCase(tbAdress.Text)
                row("Adress2") = UCase(tbAdress2.Text)
                row("City") = UCase(tbCity.Text)
                row("PostalCode") = UCase(tbZip.Text)
                row("Phone") = UCase(tbPhone1.Text)
                row("Phone2") = UCase(tbPhone2.Text)
                row("Email") = UCase(tbEmail.Text)
                row("NoteAdd") = UCase(tbNote.Text)
                Me.bindingcust.EndEdit()
          daCustomers.Update(ds, "Customers")
End sub

So i dont know why it is not working for the car Item. Thank you for your help again.