VB.NET Two way Update - Stack Overflow

admin管理员组

文章数量:1024652

Im Creating a two way update where the system won't clash it data refresh inside the Checklistbox but i can't make it update the way i want because the timing if the refresh update isn't exactly the same and make the later refresh update display than the one who update first.

---Code:---

    Sub CB1ItemSaving()

        Dim strquery As String = "DELETE FROM [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] where CTRLNo = '" & lblctrlNo.Text & "' and Description = '" & lbldesc1.Text & "'"

        If RunQuery(strquery) Then
        End If

        Dim strquery1 As String = "INSERT INTO [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] (CTRLNo,Description, Item, isSelected) VALUES (@CTRLNo,@Description, @Item, @isSelected)"

        For i = 0 To CLB1.Items.Count - 1
            Dim isChecked As Boolean = (CLB1.GetItemCheckState(i) = CheckState.Checked)

            If gSQlConn.State = ConnectionState.Closed Then gSQlConn.Open()
            Using cmd As New SqlCommand(strquery1, gSQlConn)
                cmd.Parameters.AddWithValue("@CTRLNo", lblctrlNo.Text)
                cmd.Parameters.AddWithValue("@Description", lbldesc1.Text)
                cmd.Parameters.AddWithValue("@Item", CLB1.Items(i).ToString())
                cmd.Parameters.AddWithValue("@isSelected", isChecked)
                cmd.ExecuteNonQuery()

            End Using

        Next
    End Sub



    Private Sub RefreshTimer_Tick(sender As Object, e As EventArgs) Handles RefreshTimer.Tick
        Try
            
            'EnableDoubleBuffering(CLB1)
            RefreshTop5Data()
            CBL1Counter()
            Condition()
            CLBClear()
            CB1ItemSaving()
            CLBSaves()
        Catch ex As Exception
        End Try
  
    End Sub

I tried Using a Timer but it won't update the way i want that the data that should be updating is two way like when i update the first one, the second one should update and vice versa but the result of mine is when the first one who refresh won't save the data and the latter one saved the data

Im Creating a two way update where the system won't clash it data refresh inside the Checklistbox but i can't make it update the way i want because the timing if the refresh update isn't exactly the same and make the later refresh update display than the one who update first.

---Code:---

    Sub CB1ItemSaving()

        Dim strquery As String = "DELETE FROM [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] where CTRLNo = '" & lblctrlNo.Text & "' and Description = '" & lbldesc1.Text & "'"

        If RunQuery(strquery) Then
        End If

        Dim strquery1 As String = "INSERT INTO [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] (CTRLNo,Description, Item, isSelected) VALUES (@CTRLNo,@Description, @Item, @isSelected)"

        For i = 0 To CLB1.Items.Count - 1
            Dim isChecked As Boolean = (CLB1.GetItemCheckState(i) = CheckState.Checked)

            If gSQlConn.State = ConnectionState.Closed Then gSQlConn.Open()
            Using cmd As New SqlCommand(strquery1, gSQlConn)
                cmd.Parameters.AddWithValue("@CTRLNo", lblctrlNo.Text)
                cmd.Parameters.AddWithValue("@Description", lbldesc1.Text)
                cmd.Parameters.AddWithValue("@Item", CLB1.Items(i).ToString())
                cmd.Parameters.AddWithValue("@isSelected", isChecked)
                cmd.ExecuteNonQuery()

            End Using

        Next
    End Sub



    Private Sub RefreshTimer_Tick(sender As Object, e As EventArgs) Handles RefreshTimer.Tick
        Try
            
            'EnableDoubleBuffering(CLB1)
            RefreshTop5Data()
            CBL1Counter()
            Condition()
            CLBClear()
            CB1ItemSaving()
            CLBSaves()
        Catch ex As Exception
        End Try
  
    End Sub

I tried Using a Timer but it won't update the way i want that the data that should be updating is two way like when i update the first one, the second one should update and vice versa but the result of mine is when the first one who refresh won't save the data and the latter one saved the data

• vb

Share Improve this question Follow edited Nov 19, 2024 at 10:27 Black cat 6,61355 gold badges3434 silver badges6969 bronze badges asked Nov 19, 2024 at 0:21 Delloro KenDelloro Ken 1 2

• 1 Here's some general advice stackoverflow/a/51111498/495455 – Jeremy Thompson Commented Nov 19, 2024 at 1:26
• You could store the datetime of the last update for that row in the database. If the datetime retrieved by your program matches the datetime of the last update then do the update, else alert the user that the data has already been changed and show them what it has changed to. (You would have to do an "upsert" rather than a delete-and-insert.) – Andrew Morton Commented Nov 20, 2024 at 13:08

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Most of the time I try to handle this with a submit button where it takes the values of the checkboxes and does everything you want them to do at once. As you have it, it looks like it's doing 2 seperate DB queries (not concurrent) everytime the user checks/unchecks the box. Sometimes I can see where that would be required but if it isn't, then see if you can find a way to simplify the process.

Either way, I think you probably want to look into creating a 'Stored Procedure' in the DB and utilize a 'Transaction' which is similar to a Try/Catch in that it can be made to execute all of the queries in a single transaction or none at all if it encounters an error. There are a lot of other things that can be done with Transactions (i.e. rollback, continue, etc) but that's another topic entirely.

For your purposes, try a Transaction within a Stored Procedure. It should accomplish what you're trying to do.

Im Creating a two way update where the system won't clash it data refresh inside the Checklistbox but i can't make it update the way i want because the timing if the refresh update isn't exactly the same and make the later refresh update display than the one who update first.

---Code:---

    Sub CB1ItemSaving()

        Dim strquery As String = "DELETE FROM [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] where CTRLNo = '" & lblctrlNo.Text & "' and Description = '" & lbldesc1.Text & "'"

        If RunQuery(strquery) Then
        End If

        Dim strquery1 As String = "INSERT INTO [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] (CTRLNo,Description, Item, isSelected) VALUES (@CTRLNo,@Description, @Item, @isSelected)"

        For i = 0 To CLB1.Items.Count - 1
            Dim isChecked As Boolean = (CLB1.GetItemCheckState(i) = CheckState.Checked)

            If gSQlConn.State = ConnectionState.Closed Then gSQlConn.Open()
            Using cmd As New SqlCommand(strquery1, gSQlConn)
                cmd.Parameters.AddWithValue("@CTRLNo", lblctrlNo.Text)
                cmd.Parameters.AddWithValue("@Description", lbldesc1.Text)
                cmd.Parameters.AddWithValue("@Item", CLB1.Items(i).ToString())
                cmd.Parameters.AddWithValue("@isSelected", isChecked)
                cmd.ExecuteNonQuery()

            End Using

        Next
    End Sub



    Private Sub RefreshTimer_Tick(sender As Object, e As EventArgs) Handles RefreshTimer.Tick
        Try
            
            'EnableDoubleBuffering(CLB1)
            RefreshTop5Data()
            CBL1Counter()
            Condition()
            CLBClear()
            CB1ItemSaving()
            CLBSaves()
        Catch ex As Exception
        End Try
  
    End Sub

I tried Using a Timer but it won't update the way i want that the data that should be updating is two way like when i update the first one, the second one should update and vice versa but the result of mine is when the first one who refresh won't save the data and the latter one saved the data

Im Creating a two way update where the system won't clash it data refresh inside the Checklistbox but i can't make it update the way i want because the timing if the refresh update isn't exactly the same and make the later refresh update display than the one who update first.

---Code:---

    Sub CB1ItemSaving()

        Dim strquery As String = "DELETE FROM [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] where CTRLNo = '" & lblctrlNo.Text & "' and Description = '" & lbldesc1.Text & "'"

        If RunQuery(strquery) Then
        End If

        Dim strquery1 As String = "INSERT INTO [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] (CTRLNo,Description, Item, isSelected) VALUES (@CTRLNo,@Description, @Item, @isSelected)"

        For i = 0 To CLB1.Items.Count - 1
            Dim isChecked As Boolean = (CLB1.GetItemCheckState(i) = CheckState.Checked)

            If gSQlConn.State = ConnectionState.Closed Then gSQlConn.Open()
            Using cmd As New SqlCommand(strquery1, gSQlConn)
                cmd.Parameters.AddWithValue("@CTRLNo", lblctrlNo.Text)
                cmd.Parameters.AddWithValue("@Description", lbldesc1.Text)
                cmd.Parameters.AddWithValue("@Item", CLB1.Items(i).ToString())
                cmd.Parameters.AddWithValue("@isSelected", isChecked)
                cmd.ExecuteNonQuery()

            End Using

        Next
    End Sub



    Private Sub RefreshTimer_Tick(sender As Object, e As EventArgs) Handles RefreshTimer.Tick
        Try
            
            'EnableDoubleBuffering(CLB1)
            RefreshTop5Data()
            CBL1Counter()
            Condition()
            CLBClear()
            CB1ItemSaving()
            CLBSaves()
        Catch ex As Exception
        End Try
  
    End Sub

I tried Using a Timer but it won't update the way i want that the data that should be updating is two way like when i update the first one, the second one should update and vice versa but the result of mine is when the first one who refresh won't save the data and the latter one saved the data

• vb

Share Improve this question Follow edited Nov 19, 2024 at 10:27 Black cat 6,61355 gold badges3434 silver badges6969 bronze badges asked Nov 19, 2024 at 0:21 Delloro KenDelloro Ken 1 2

• 1 Here's some general advice stackoverflow/a/51111498/495455 – Jeremy Thompson Commented Nov 19, 2024 at 1:26
• You could store the datetime of the last update for that row in the database. If the datetime retrieved by your program matches the datetime of the last update then do the update, else alert the user that the data has already been changed and show them what it has changed to. (You would have to do an "upsert" rather than a delete-and-insert.) – Andrew Morton Commented Nov 20, 2024 at 13:08

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Most of the time I try to handle this with a submit button where it takes the values of the checkboxes and does everything you want them to do at once. As you have it, it looks like it's doing 2 seperate DB queries (not concurrent) everytime the user checks/unchecks the box. Sometimes I can see where that would be required but if it isn't, then see if you can find a way to simplify the process.

Either way, I think you probably want to look into creating a 'Stored Procedure' in the DB and utilize a 'Transaction' which is similar to a Try/Catch in that it can be made to execute all of the queries in a single transaction or none at all if it encounters an error. There are a lot of other things that can be done with Transactions (i.e. rollback, continue, etc) but that's another topic entirely.

For your purposes, try a Transaction within a Stored Procedure. It should accomplish what you're trying to do.

本文标签： VBNET Two way UpdateStack Overflow