none
Updating with SQL DataAdapter is not affecting the first row. VB.net and SQL Server. RRS feed

  • Question

  • Hey guys, so I'm trying to update some data from a datagridview to my SQL database. Once I make changes, it affects all rows except the first one. I'll show you the code:

     Private Sub ShowData()
            connection = New SqlConnection(svrcon)
            sql = $"SELECT * FROM Aguinaldo WHERE NombreAguinaldo = '{NombreTxt.Text}' AND FechaGenerada = '{fecha}'"
            Try
                cn.Open()
                adapter = New SqlDataAdapter(sql, cn)
                adapter.Fill(ds)
                DataGridView1.DataSource = ds.Tables(0)
                cn.Close()
            Catch ex As Exception
                MessageBox.Show($"Error: {ex.ToString}", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub

    *This is how I show my data in the datagrid view. Connection string is declared as public. 

     Private Sub updateDG()
            Try
                cmdBuilder = New SqlCommandBuilder(adapter)
                changes = ds.GetChanges()
                If changes IsNot Nothing Then
                    adapter.Update(changes)
                    ds.AcceptChanges()
                    DataGridView1.AllowUserToOrderColumns = False
                End If
                MessageBox.Show("¡DATOS DE AGUINALDO GUARDADOS EXITOSAMENTE!", "DATOS GUARDADOS", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                MessageBox.Show($"Error: {ex.ToString}", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub

    *And this is my update method, as I said previously, it updates every row except the first one.

    When I want to update the first row manually, it gives me this error:

    

    Please I need your help!


    Tuesday, December 17, 2019 9:43 PM

All replies

  • Hello,

    This happens when a) another user made a change to the data b) you made a change say in SSMS, see the following documentation for details

    In short, this exception is being thrown because the internal adapter thinks that the data has been updated by another process.

    Now you can if totally sure none of the above is true try this in the update method.

    Dim cmdBuilder = New SqlCommandBuilder(adapter) With {.ConflictOption = ConflictOption.OverwriteChanges}
    

    See also

    https://docs.microsoft.com/en-us/dotnet/api/system.data.conflictoption?view=netframework-4.8


    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

    Wednesday, December 18, 2019 10:49 AM
    Moderator
  • Hi Viravalo, 

    Thank you for posting here.

    I make a test based on your code, but the program works well and I get no exception.

    Could you provide more details about the exception with which line of the code thrown the exception in English? It will help us analyze your problem and make a test.

    We are waiting for your update.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 19, 2019 7:36 AM
    Moderator
  • @Xingyu, the issues is concurrency which means the client original data does not match the server data thus a concurrency issue, this type of issues is data not code. This is the reason for ConflictOption.OverwriteChanges.

    To duplicate such an issue means to manipulate data on the server and make changes on the client to put things out of sync. Otherwise when the adapter tries to resolve detected changes server data is ignored yet this can cause issues such as when a record did exists now it does not when updating.


    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

    Thursday, December 19, 2019 10:58 AM
    Moderator
  • I tried the code but its still not updating. :/
    Thursday, December 19, 2019 5:55 PM
  • I'll try to explain it better.

    First I create the info with store procedure and show it in a datagridview. Then I have a Button that will calculate the columns with "0".

    So here the button calculates the totals and now I'll update the data.

    As you see, it updates all except the first row.

    Now if I manually want to update the first row...

    I get this error.

      Try
                If NombreTxt.Text = "" Then
                    MessageBox.Show("¡ERROR, DEBE AGREGAR UN NOMBRE A LA PLANILLA!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Else
                    cn.Open()
                    Dim da As New SqlDataAdapter("Insertar_Aguinaldo_Fijos", cn)
                    da.SelectCommand.CommandType = CommandType.StoredProcedure
                    da.SelectCommand.Parameters.AddWithValue("Nombre", NombreTxt.Text)
                    da.SelectCommand.Parameters.AddWithValue("Fecha", fecha)
                    Dim dt As New DataTable
                    da.Fill(dt)
                    DataGridView1.DataSource = dt
                    cn.Close()
                    ShowData()
                    DesignGrid()
                    CrearBtn.Enabled = False
                End If
            Catch ex As Exception
                MessageBox.Show($"Error: {ex.ToString}", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

    This is the code to insert the data in my database and show it in the DGV.

     Private Sub ShowData()
            connection = New SqlConnection(svrcon)
            sql = $"SELECT * FROM Aguinaldo WHERE NombreAguinaldo = '{NombreTxt.Text}' AND FechaGenerada = '{fecha}'"
            Try
                cn.Open()
                adapter = New SqlDataAdapter(sql, cn)
                adapter.Fill(ds)
                DataGridView1.DataSource = ds.Tables(0)
                cn.Close()
            Catch ex As Exception
                MessageBox.Show($"Error: {ex.ToString}", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    
        Private Sub updateDG()
            Try
                cmdBuilder = New SqlCommandBuilder(adapter)
                changes = ds.GetChanges()
                If changes IsNot Nothing Then
                    adapter.Update(changes)
                    ds.AcceptChanges()
                    DataGridView1.AllowUserToOrderColumns = False
                End If
                MessageBox.Show("¡DATOS DE AGUINALDO GUARDADOS EXITOSAMENTE!", "DATOS GUARDADOS", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                MessageBox.Show($"Error: {ex.ToString}", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    Show data and update Code again.

    Thursday, December 19, 2019 6:08 PM
  • Hi Viravalo, 

    Thanks for your feedback.

    I had created a database and made a test based on your code.

    Design of my database.

    I had inserted some data into the database, and updated the data successfully using your code.

    As Kareninstructor suggested, the type of issues is not code, your client original data may not match the server data.

    Here are two related references about the exception, and you can refer to the suggestions in them.

    1. Concurrency violation: the UpdateCommand affected 0 of the expected 1 records
    2. Concurrency violation: the UpdateCommand affected 0 of the expected 1 records(C#)

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 20, 2019 7:33 AM
    Moderator