none
Updating an SQL table from changes made to a VB.NET data grid view RRS feed

  • Question

  • Hey guys wondering if anyone can help with this, weird things are happening when updating a data table from my datagridview and i cant figure it out..

    I have a data grid view called petdatagridview when i make changes to certain rows in the grid view and hit the update button everything works fine, i can change multiple cell values of different rows without problems, but this doesn't work with all rows for some reason. certain rows will throw a concurrency violation (but not all) and if i hit the refresh button on the form (reloads table into the datagridview) then try and alter a cell and hit update it tells me that there is no new changes in the datagridview, im really stumped..

    Heres my code:

    under the class heading......

    Public Class managePetInfo

        Dim MyCommand As New MySqlCommand
        Dim Myadapter As New MySqlDataAdapter
        Dim MyBuilder As MySqlCommandBuilder
        Dim conn As New MySqlConnection(connectionstring)
        Dim MyDataTable As New DataTable
        Dim MyDataset As DataSet = New DataSet
        Dim ds As DataSet = New DataSet

    In the form load....


        Public Sub managePetInfo_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    'Load pet data into petdatagridview
            Try
                'Load pet data into petdatagridview
                petDataGridView.DataSource = DataAccessLayer.LoadPetData()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try    

    'set command text, connection, fill data adapter, etc
            MyCommand.CommandText = "select * from pet"
            MyCommand.Connection = conn
            Myadapter.SelectCommand = MyCommand
            MyBuilder = New MySqlCommandBuilder(Myadapter)

            Myadapter.Fill(MyDataset, "pet")
            MyDataTable = MyDataset.Tables("pet")
            petDataGridView.DataSource = MyDataTable

        End Sub

    In the update button event.....

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

            If MyDataset.HasChanges() = False Then
                MessageBox.Show("The table contains no changes to save.")
            Else

                Dim rowsAffected As Integer = Myadapter.Update(MyDataTable)

                If rowsAffected = 0 Then
                    MessageBox.Show("No rows were affected by the save operation.")
                Else
                    MessageBox.Show(rowsAffected & " rows were affected by the save operation.")
                End If
            End If

            Validate()
            Myadapter.Update(MyDataset.Tables("pet"))

            MyDataset.AcceptChanges()
        
        End Sub

    Ive had a lot of issues trying to get this to work, if anyone can see any issues in my code or can suggest anything, anything at all i would greatly appreciate it. Or if anyone has there own version of code that updates an sql table from data grid view changes that they can show me. It would be immensely helpful.

    Oh and not sure if it is relevant but here is the function used to refresh the data grid view, which is the same function that loads data into the data grid view:

    Public Shared Function LoadPetData()

            Dim conn As New MySqlConnection(connectionstring)
            Dim table As New DataTable()
            Dim adapter As New MySqlDataAdapter("SELECT * FROM pet", conn)

            Try
                managePetInfo.petDataGridView.DataSource = table
                adapter.Fill(table)
            Catch ex As Exception

                MsgBox(ex.Message)
            End Try
            Return table

    End Function

    Please help me!
    Sunday, May 5, 2019 12:02 AM

Answers

  • Hello,

    A concurrency violation is being thrown because the driver (the driver is MySql under the DataAdapter) thinks that the data has been updated by another process.

    When performing an update the underlying ADO driver (for your DataAdapter) compares the values for the current database record with what it expects before it performs the update. This check is done to detect whether some other process has updated the database. If the data is not as expected then a DBConcurrencyException is generated.

    Whether this is just you working on the database table or there are others, see the following article, read all the way to the end too.

    https://docs.microsoft.com/en-us/visualstudio/data-tools/handle-a-concurrency-exception?view=vs-2019 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Sunday, May 5, 2019 1:03 AM
    Moderator

All replies

  • Hello,

    A concurrency violation is being thrown because the driver (the driver is MySql under the DataAdapter) thinks that the data has been updated by another process.

    When performing an update the underlying ADO driver (for your DataAdapter) compares the values for the current database record with what it expects before it performs the update. This check is done to detect whether some other process has updated the database. If the data is not as expected then a DBConcurrencyException is generated.

    Whether this is just you working on the database table or there are others, see the following article, read all the way to the end too.

    https://docs.microsoft.com/en-us/visualstudio/data-tools/handle-a-concurrency-exception?view=vs-2019 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Sunday, May 5, 2019 1:03 AM
    Moderator
  • Thanks for the reply!

    I am the only one working with the application and database so yeah, there must be an error in my code somewhere..

    I'll read the article you listed and hopefully it will help..

    Thanks for taking the time.

    Sunday, May 5, 2019 3:55 AM