I'm learning to update a record in a SQL Server table after checking for concurrency violation using a RowVersion column (timestamp data type). However, I'm not able to extract the existing RowVersion value to a suitable variable type. I get the following error and the relevent code is also mentioned below.
Error:
' UPDATE with concurrency check
Public Sub Update(user As UserModel, rowVer As Byte)
Dim sql As String = "UPDATE TB_USERS SET " &
"FullName = @FullName, " &
"Password = @Password, " &
"UserType = @UserType, " &
"NIC = @NIC, " &
"Address = @Address, " &
"Telephone = @Telephone, " &
"Level = @Level, " &
"LoginStatus = @LoginStatus, " &
"Locked = @Locked " &
"WHERE user_Id = @UserId AND RowVersion = @RowVersion"
Using con As New SqlConnection(connectionString)
Using cmd As New SqlCommand(sql, con)
cmd.Parameters.AddWithValue("@FullName", user.FullName)
cmd.Parameters.AddWithValue("@Password", user.Password)
cmd.Parameters.AddWithValue("@UserType", user.UserType)
cmd.Parameters.AddWithValue("@NIC", user.NIC)
cmd.Parameters.AddWithValue("@Address", user.Address)
cmd.Parameters.AddWithValue("@Telephone", user.Telephone)
cmd.Parameters.AddWithValue("@Level", user.Level)
cmd.Parameters.AddWithValue("@LoginStatus", user.LoginStatus)
cmd.Parameters.AddWithValue("@Locked", user.Locked)
cmd.Parameters.AddWithValue("@UserId", user.Id)
cmd.Parameters.AddWithValue("@RowVersion", rowVer)
con.Open()
Dim rowsAffected = cmd.ExecuteNonQuery()
MsgBox(rowsAffected)
End Using
End Using
End Sub
Private Sub cmdUpdateUserData_Click(sender As Object, e As EventArgs) Handles cmdUpdateUserData.Click
Dim con As New SqlConnection(connectionString)
Dim sql As String = "SELECT user_id, RowVersion FROM TB_USERS where user_name= '" & txtNewUserName.Text & "'"
Dim da As New SqlDataAdapter(sql, con)
Dim dt As New DataTable()
da.Fill(dt)
Dim repo As New UserRepository()
repo.Update(GetUserFromForm(), dt.Rows(0)("Rowversion"))
End Sub

AddWithValueis evil. Not to mention you have a dangerous SQL injection issue in yourSubcmdUpdateUserData_Click.