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
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;