VB.net form updates only if form closed and opened again

I have a form that that retrieves data from SQL table. If I change any field, and update the data, it changes while I am on that TabScreen, if I move to another TabScreen, and go back it reverts back to the original values. If I close the form and reopen it, it shows the updated values. I have tried everything I can think of to overcome the problem. Can anyone point me in the right direction. Apologies if I haven't explained it well.

Public Class FrmStaffDetails
    Dim Con As New SqlConnection("Data Source=LAPTOP\SQLEXPRESS;Initial Catalog=SimpleMaintenanceSystem;Integrated Security=True;Encrypt=True;TrustServerCertificate=True")
    Dim cmd As New SqlCommand
    Dim Index As Integer = 0
    Dim table As New DataTable()
    Dim UserStatus As String
    Dim sql As String
    'Dim i As Integer

    Private Sub FrmStaffDetails_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ShowData(Index)
    End Sub

    Private Sub ShowData(Position As Integer)
        Con.Close()
        Dim Cmd As New SqlCommand("Select * From Staff", Con)
        Dim adaptor As New SqlDataAdapter(Cmd)

        adaptor.Fill(table)

        TxtStaffID.Text = table.Rows(Position)(0).ToString()
        TxtStaffFirstName.Text = table.Rows(Position)(1).ToString()
        TxtStaffLastName.Text = table.Rows(Position)(2).ToString()
        TxtStaffAddress.Text = table.Rows(Position)(3).ToString()
        TxtStaffCity.Text = table.Rows(Position)(4).ToString()
        TxtStaffState.Text = table.Rows(Position)(5).ToString()

        Con.Close()
    End Sub

Private Sub BtnUpdateData_Click(sender As Object, e As EventArgs) Handles BtnUpdateData.Click

    If Con.State = ConnectionState.Open Then
        Con.Close()
    End If
    Con.Open()
    Try
        Dim rowsAffected As Integer
        cmd = Con.CreateCommand()
        cmd.CommandType = CommandType.Text

        cmd.CommandText = "UPDATE Staff SET StaffFirstname=@FirstName
        , StaffLastName = @LastName
        , StaffAddress = @Address
        , StaffCity = @city
        , StaffState = @State WHERE Staffid = @StaffId"

cmd.Parameters.AddWithValue("@staffID", TxtStaffID.Text)
cmd.Parameters.AddWithValue("@FirstName", TxtStaffFirstName.Text)
cmd.Parameters.AddWithValue("@LastName", TxtStaffLastName.Text)
cmd.Parameters.AddWithValue("@address", TxtStaffAddress.Text)
cmd.Parameters.AddWithValue("@city", TxtStaffCity.Text)
cmd.Parameters.AddWithValue("@State", TxtStaffState.Text)


            rowsAffected = cmd.ExecuteNonQuery()

            If rowsAffected > 0 Then
                MessageBox.Show("Data Updated Succesfully")
            ElseIf MessageBox.Show("Data UNSUCCESFULLY Updated") Then
            End If

        Catch ex As Exception

            ShowData(Index)
            Con.Close()
        End Try
    End Sub

Solution

We can't see your TabScreen definitions, but it's a good bet there's a grid or similar control data bound to the table variable. You need to update this table variable after updating the data, whether there was an exception or not.

You may be able to do this by changing the desired rows/cells in the existing variable, rather than reloading everything from the database. But reload the from the database if you find that easier.

It's also important to note that there is no ORDER BY clause for your SELECT query, but you also expect to be able to reference rows by index after reloading them from the database. This is not good.

Be aware: without an ORDER BY clause databases are free to return rows in ANY order they find convenient. You may tend to consistently see rows in a primary key or insert order, but this is NOT GUARANTEED.

There are a number of things that can cause the order to change from run to run, even for the same query to the same database on the same server with the same data.

Another concern is you should NOT keep a single SqlConnection object as a member of your form. This interferes with connection pooling in ADO.Net. It makes things slower, with more memory use rather than less, and forces things to happen in serial/sequence that might otherwise be able to go in parallel.

Instead, in most cases every query should get its own brand new connection object. Really. Ideally as part of a Using block so it is disposed in a deterministic way. Only the connection string should be shared among your application.

Here's an example of how that could look:

' This replaces the Con variable declared currently
Private cnString As String = "Data Source=LAPTOP\SQLEXPRESS;Initial Catalog=SimpleMaintenanceSystem;Integrated Security=True;Encrypt=True;TrustServerCertificate=True"

Private Sub ShowData(Position As Integer)
    Using Con As New SqlConnection(cnString), _
          Cmd As New SqlCommand("Select * From Staff ORDER BY StaffID", Con), _
          adaptor As New SqlDataAdapter(Cmd)

        adaptor.Fill(table)
    End Using

    If Position <= 0 OrElse Position >= table.Rows.Count Then Return

    TxtStaffID.Text = table.Rows(Position)(0).ToString()
    TxtStaffFirstName.Text = table.Rows(Position)(1).ToString()
    TxtStaffLastName.Text = table.Rows(Position)(2).ToString()
    TxtStaffAddress.Text = table.Rows(Position)(3).ToString()
    TxtStaffCity.Text = table.Rows(Position)(4).ToString()
    TxtStaffState.Text = table.Rows(Position)(5).ToString()

End Sub

Finally, be wary of the AddWithValue() method. When you use this method, ADO.Net has to infer the SQL type for the parameter. Sometimes ADO.Net can infer wrong, such as inferring NVarChar when you needed a VarChar or DateTime column, or getting a length value wrong. In these cases, type precedence rules can force SQL Server to do a conversion on the column for every row in a table to match the type for your parameter, instead of just converting the one parameter value to match the type stored in the database. This is especially likely for values used in a predicate expression. The result can be catastrophic for performance. Worse... converted values no longer match up with any index you might have on the column. This cuts to the core of database performance.

This doesn't happen all the time, but when it does, the difference is night/day.

Instead, specify the SQL data type for your parameters so you can be certain they line up with the column types in the database:

cmd.Parameters.Add("@staffID", SqlDbType.NVarChar, 20).Value =  TxtStaffID.Text;