none
DBConcurrencyException - MSAccess DB: Don't know why this is so hard! RRS feed

  • Question

  • Working with an MS Access (2010) Database.

    There is no other code than the code below that is accessing the database.

    I get a DBConcurrency Exception... And I can't for the life of me figure out why.  I'm pretty new to ADO.Net, so I'm hoping this will be an easy one.

    All I'm doing is loading a single record from a single table into a dataset, changing one column value and then updating.  But I'm getting a

    DBConcurrencyException:

    Dim strSQL As String = "SELECT ClientName, ClientNumber, DatabaseName, ClientID FROM Clients WHERE ClientNumber = " & ClientNumber.ToString & ";"
         
    Dim daClients As New OleDb.OleDbDataAdapter(strSQL, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Settings.MasterDBPath)
    Dim ds As New DataSet
    Dim tblClients As DataTable
    Dim cmd As OleDb.OleDbCommand

    'Create the dataadapter's update method
     cmd = New OleDb.OleDbCommand("UPDATE Clients SET ClientName = ?, DatabaseName = ?  WHERE ClientID = ?", daClients.SelectCommand.Connection)

     cmd.Parameters.Add("ClientID", OleDb.OleDbType.BigInt, 50, "ClientID")
     cmd.Parameters.Add("ClientName", OleDb.OleDbType.VarChar, 50, "ClientName")
     cmd.Parameters.Add("DatabaseName", OleDb.OleDbType.VarChar, 50, "DatabaseName")
     
     daClients.UpdateCommand = cmd

    'Fill the dataset
    daClients.Fill(ds, "Clients")
    tblClients = ds.Tables("Clients")

    tblClients.Rows(0)("ClientName") = "Hello"

    'Execute the update query... this is what throws the exception
    daClients.Update(tblClients)

    'Even if you could point me in a direction to help me debug, that would be great


    • Edited by Ian540 Saturday, May 19, 2012 3:43 PM
    Saturday, May 19, 2012 3:35 PM

Answers

  • OK,

    I answered my own question....

    Parameters on the command object need to be added in the order they appear in the Update query:

    cmd.Parameters.Add("ClientName", OleDb.OleDbType.VarChar, 50, "ClientName")
    cmd.Parameters.Add("DatabaseName", OleDb.OleDbType.VarChar, 50, "DatabaseName")
    cmd.Parameters.Add("ClientID", OleDb.OleDbType.BigInt, 50, "ClientID")

    Thanks to anybody who might have taken a look at this.

    Ian


    Ian

    • Marked as answer by Ian540 Sunday, May 20, 2012 5:53 PM
    Sunday, May 20, 2012 5:51 PM

All replies

  • OK,

    I answered my own question....

    Parameters on the command object need to be added in the order they appear in the Update query:

    cmd.Parameters.Add("ClientName", OleDb.OleDbType.VarChar, 50, "ClientName")
    cmd.Parameters.Add("DatabaseName", OleDb.OleDbType.VarChar, 50, "DatabaseName")
    cmd.Parameters.Add("ClientID", OleDb.OleDbType.BigInt, 50, "ClientID")

    Thanks to anybody who might have taken a look at this.

    Ian


    Ian

    • Marked as answer by Ian540 Sunday, May 20, 2012 5:53 PM
    Sunday, May 20, 2012 5:51 PM
  • Hi Ian,

    Thanks for sharing your solution here.

    Best Regards,


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 21, 2012 7:27 AM
    Moderator