none
Cannot update database when modifying newly added records in dadagridview (concurrency access violation) RRS feed

  • Question

  • I can add a new record from the datagridview by clicking on the btnUpdateDataBase_Click but if I try to modify a cell in this newly created record I get the Concurrency access violation.

    I can modify the existing records (not newly created). I can delete any records.

    After extensive research and testing, the problem seems to be with the absence of an id field in the datagridview when a new records are added.

    But I cannot find a solution to this problem.

    thanks,

    Option Strict On

    Option Explicit On

    Imports System.Data.SqlClient
    Public Class frmCallerIDTest

        Private DTAnnuaire As New DataTable("Annuaire")
        Dim CommandeSQLSelect As String
        Private SQLDaAnnuaire As SqlDataAdapter
        Private SQLCommandBuild As SqlCommandBuilder
        Private AnnuaireBindingSource As New BindingSource

        Private Sub frmCallerIDTest_Load(sender As Object, e As EventArgs) Handles MyBase.Load

            CommandeSQLSelect = "SELECT Numéro,IDPersonnelle,Nom,Prénom,Rapport,Téléphone1,Téléphone2,Fax, 
    [TéléAvertisseur],Compagnie,Cellulaire,Adresse,Ville,[ProvinceÉtat],Pays,[CodePostal],DateDeNaissance,Filtré,Mémo,id FROM CallerIDAnnuaire"
            SQLDaAnnuaire = New SqlDataAdapter(CommandeSQLSelect, ConnectionSQL)
            SQLCommandBuild = New SqlCommandBuilder(SQLDaAnnuaire)
            SQLDaAnnuaire.Fill(DTAnnuaire)
            AnnuaireBindingSource.DataSource = DTAnnuaire
            dgvTest.DataSource = AnnuaireBindingSource

        End Sub
        Private Sub btnUpdateDataBase_Click(sender As Object, e As EventArgs) Handles btnUpdateDataBase.Click
            SQLDaAnnuaire.Update(DTAnnuaire)
        End Sub
    End Class

    Tuesday, August 28, 2018 11:01 PM

Answers

  • Hi,

    Try to modify your code, my code can run successfully.

    Imports System.Data.SqlClient
    Public Class Form1
        Dim SQLDaAnnuaire As SqlDataAdapter
        Dim CommandeSQLSelect As String
        Private AnnuaireBindingSource As New BindingSource
        Dim DTAnnuaire As DataTable = New DataTable
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
            Dim ConnectionSQL As SqlConnection = New SqlConnection(constr)
            CommandeSQLSelect = "Select * From Student"
            SQLDaAnnuaire = New SqlDataAdapter(CommandeSQLSelect, ConnectionSQL)
            Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(SQLDaAnnuaire)
            SQLDaAnnuaire.Fill(DTAnnuaire)
            AnnuaireBindingSource.DataSource = DTAnnuaire
            DataGridView1.DataSource = AnnuaireBindingSource
        End Sub
    'Update the data in the datagridview after the data is updated to the database, you must have a primary key
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            SQLDaAnnuaire.Update(DTAnnuaire)
        End Sub
    End Class

    Best Regards,

    Alex


    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.

    • Marked as answer by Cricrou1 Sunday, September 2, 2018 7:34 PM
    Thursday, August 30, 2018 4:04 AM

All replies

  • Try 

    Private SQLCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder With 
        {
            .ConflictOption = ConflictOption.OverwriteChanges
        }


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, August 29, 2018 1:14 AM
    Moderator
  • Inserted this line after:

    SQLCommandBuild = New SqlCommandBuilder(SQLDaAnnuaire)

    SQLCommandBuild.ConflictOption = ConflictOption.OverwriteChanges

    Same result.

    For further explanation:

    In the dagadridview, if I insert the next higher id value (taken from the sql database with SSMS) the modified datagridview line is accepted without error. This is why I wrote above that It might be related to the autoincrement id field.

    It is not the first time that I stumble on the interface of the datagridview and  database. This time I decided to use these tools (DataAdapter, SQLCommandBuilder, Binding Source) to rapidly develop this module but It turned out that I spent more time on this than to write the crud logic in code. It almost work but I cannot update a recently added record. To make it work I need to restart the application and modify the record.




    Wednesday, August 29, 2018 3:32 AM
  • Hi,

    Try to modify your code, my code can run successfully.

    Imports System.Data.SqlClient
    Public Class Form1
        Dim SQLDaAnnuaire As SqlDataAdapter
        Dim CommandeSQLSelect As String
        Private AnnuaireBindingSource As New BindingSource
        Dim DTAnnuaire As DataTable = New DataTable
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
            Dim ConnectionSQL As SqlConnection = New SqlConnection(constr)
            CommandeSQLSelect = "Select * From Student"
            SQLDaAnnuaire = New SqlDataAdapter(CommandeSQLSelect, ConnectionSQL)
            Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(SQLDaAnnuaire)
            SQLDaAnnuaire.Fill(DTAnnuaire)
            AnnuaireBindingSource.DataSource = DTAnnuaire
            DataGridView1.DataSource = AnnuaireBindingSource
        End Sub
    'Update the data in the datagridview after the data is updated to the database, you must have a primary key
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            SQLDaAnnuaire.Update(DTAnnuaire)
        End Sub
    End Class

    Best Regards,

    Alex


    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.

    • Marked as answer by Cricrou1 Sunday, September 2, 2018 7:34 PM
    Thursday, August 30, 2018 4:04 AM
  • Thanks Alex, your code is working and led me to test by trial and error. I included the code of button1 in Form1_Load. Then, I sequentially commented the code in button1 until it worked correctly. I found out that after inputting a new value in the datagridview and updating the database, the database is updated with this new value and includes a new id value but the datagridview id value is not updated until the Datatable.clear and Dadapter.fill are executed again.

    I was expecting the BindingSource to do all the required updates with the datagridview including the update of the id filed of the datagridview but this is not happening.

    Thank you very much again.

    Here's the final working test code inspired by your code.

    Imports System.Data.SqlClient
    Public Class Form1
        Dim SQLDaAnnuaire As SqlDataAdapter
        Dim CommandeSQLSelect As String
        Private AnnuaireBindingSource As New BindingSource
        Dim DTAnnuaire As DataTable = New DataTable

        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            CommandeSQLSelect = "Select * From callerIDAnnuaire"
            SQLDaAnnuaire = New SqlDataAdapter(CommandeSQLSelect, ConnectionSQL)
            Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(SQLDaAnnuaire)
            SQLDaAnnuaire.Fill(DTAnnuaire)
            AnnuaireBindingSource.DataSource = DTAnnuaire
            DataGridView1.DataSource = AnnuaireBindingSource
        End Sub

        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            SQLDaAnnuaire.Update(DTAnnuaire)
            DTAnnuaire.Clear()
            SQLDaAnnuaire.Fill(DTAnnuaire)
        End Sub
    End Class

    Sunday, September 2, 2018 7:34 PM