Data refresh

[BoerAas]

I have the following problem:

In VB.NET 2022, I add data to the database using program A.
Program B is open and connected to the same database with the same permissions.
After adding data through program A, when I run a query in program B, the data is not displayed in the DataGridView.
However, if I close and restart program B, the new data is displayed.
I have already consulted various AI programs, but none of them have provided a suitable solution.
What else can I do?

[BoerAas]

Databse is MySql on a Synolgy server

[PlausiblyDamp]

Could you post the relevant code? Without seeing how the application queries the db we would just be guessing.

[jmcilhinney]

What you're describing is not normal behaviour. If it's not working then you're doing it wrong. Without seeing what you're doing, there's no way we can see what's wrong with it.

[Zvoni]

Wrong Transaction Level?
Program A inserts the Data, but doesn't commit
Program B only reads committed "changes"

IIRC, MySQL-Default is Repeatable Read, but not sure how to interpret their explanation of it

[jmcilhinney]

Wrong Transaction Level?
Program A inserts the Data, but doesn't commit
Program B only reads committed "changes"

IIRC, MySQL-Default is Repeatable Read, but not sure how to interpret their explanation of it

I was thinking something like that but then I'd expect a restart of the writer to be required, rather than a restart of the reader.

[BoerAas]

A short part of the code:

Programma gedeelte A

      Public Sub VoegGymToe(strCoordinaten, strGymNaam, strStraatNaam, strWijk, strPostcode, strPlaats, strRoute, strVolgNr)

        Dim conn As MySqlConnection = OpenConnection()
        Try

            Dim query As String = "INSERT INTO Gyms (Coordinaten, GymNaam, StraatNaam, Wijk, Postcode, Plaats, RaidRoute, Routevlgnr) VALUES (@Coordinaten, @GymNaam, @StraatNaam, @Wijk, @Postcode, @Plaats, @Route, @Routevlgnr)"

            Dim cmd As New MySqlCommand(query, conn)

            cmd.Parameters.AddWithValue("@Coordinaten", strCoordinaten)
            cmd.Parameters.AddWithValue("@GymNaam", strGymNaam)
            cmd.Parameters.AddWithValue("@StraatNaam", strStraatNaam)
            cmd.Parameters.AddWithValue("@Wijk", strWijk)
            cmd.Parameters.AddWithValue("@Postcode", strPostcode)
            cmd.Parameters.AddWithValue("@Plaats", strPlaats)
            cmd.Parameters.AddWithValue("@Route", strRoute)
            cmd.Parameters.AddWithValue("@Routevlgnr", strVolgNr)
            cmd.ExecuteNonQuery()


        Catch ex As MySqlException
            MessageBox.Show("Error: " & ex.Message)
        Finally
            CloseConnection(conn)
        End Try

    End Sub
  
  Programma B
  
  Private Sub LaadRaidRoute()

      Dim query As String = "SELECT SQL_NO_CACHE GymId, GymNaam, Coordinaten, Raidroute, Routevlgnr FROM Gyms g WHERE NOT (LENGTH(g.RaidRoute) = 0) Order by RaidRoute, Routevlgnr"
      Dim dtGyms As DataTable = ExecuteQuery(query)
      Dim nRijen As Integer = dtGyms.Rows.Count

      bsRaidRoute.DataSource = dtGyms
      dgvRaidRoute.DataSource = bsRaidRoute
      dgvRaidRoute.RowHeadersVisible = False
      dgvRaidRoute.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill

      dgvRaidRoute.Columns("GymId").Width = 50
      dgvRaidRoute.Columns("GymId").Visible = False
      dgvRaidRoute.Columns("Raidroute").Width = 20
      dgvRaidRoute.Columns("Routevlgnr").Width = 20
      dgvRaidRoute.Columns("Gymnaam").Width = 180
      dgvRaidRoute.Columns("Coordinaten").Width = 50
      dgvRaidRoute.Columns("Coordinaten").Visible = False

      For Each ctrl As Control In Me.Controls
          If TypeOf ctrl Is TextBox Then
              AddHandler ctrl.KeyDown, AddressOf TextBox_KeyDown
          End If
      Next
      dgvRaidRoute.CurrentCell = dgvRaidRoute.Rows(0).Cells(1)

  End Sub

[laila]

Now show us programma gedeelte B ;-)

[BoerAas]

Programma B

Private Sub LaadRaidRoute()

Dim query As String = "SELECT SQL_NO_CACHE GymId, GymNaam, Coordinaten, Raidroute, Routevlgnr FROM Gyms g WHERE NOT (LENGTH(g.RaidRoute) = 0) Order by RaidRoute, Routevlgnr"
Dim dtGyms As DataTable = ExecuteQuery(query)
Dim nRijen As Integer = dtGyms.Rows.Count

bsRaidRoute.DataSource = dtGyms
dgvRaidRoute.DataSource = bsRaidRoute
dgvRaidRoute.RowHeadersVisible = False
dgvRaidRoute.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill

dgvRaidRoute.Columns("GymId").Width = 50
dgvRaidRoute.Columns("GymId").Visible = False
dgvRaidRoute.Columns("Raidroute").Width = 20
dgvRaidRoute.Columns("Routevlgnr").Width = 20
dgvRaidRoute.Columns("Gymnaam").Width = 180
dgvRaidRoute.Columns("Coordinaten").Width = 50
dgvRaidRoute.Columns("Coordinaten").Visible = False

For Each ctrl As Control In Me.Controls
If TypeOf ctrl Is TextBox Then
AddHandler ctrl.KeyDown, AddressOf TextBox_KeyDown
End If
Next
dgvRaidRoute.CurrentCell = dgvRaidRoute.Rows(0).Cells(1)

End Sub

[Shaggy Hiker]

Since you are populating the DGV in a sub, are you sure that sub is being called after the data has been updated? The way that method is constructed, it looks like it might not be. I would expect that the DGV would be set up one time, with a different method being used to populated the DGV. Since you fill the datatable, then set up the DGV layout, it makes it look like you are only filling the datatable just one time. That would account for the behavior you are seeing.