none
Does ADO.NET / Access ALWAYS produce Concurrency Violations??? RRS feed

  • Question

  • I have a very small, simple database in Access. I have it bound to a DataGridView in VB.NET. I can add, change and delete records via the DataGridView, and the changes are reflected in Access. A different section of code (aka Checking Code) reads the database and under certain conditions it will change a field in the database. I thought everything was working pretty well, until I realized that changes made via the DataGridView weren't being detected by the Checking Code.

    Since this is a simple database, I opted to use a ADO.NET RecordSet:

    Code Snippet

    cnAlerts = New ADODB.Connection

    cnAlerts.Open(connStr)

    rsAlerts = New ADODB.Recordset

    rsAlerts.CursorLocation = CursorLocationEnum.adUseServer

    rsAlerts.ActiveConnection = cnAlerts

    rsAlerts.Open("SELECT * FROM Alert ORDER BY Symbol", cnAlerts, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic)

     

     

    Before I added the CursorLocation, the .BOF and .EOF were False when the database was empty?!? UseServer seemed to fix that problem. I've tried different CursorTypes to no avail. The Checking Code is:

     

    Code Snippet

    If rsAlerts.BOF And rsAlerts.EOF Then ' no alerts

    Exit Sub

    Else

    rsAlerts.MoveFirst()

    End If

    For i = 5 To aUBound

    Do While rsAlerts.Fields.Item(SYMBOL_COL).Value <= Symbols(i)

    If rsAlerts.Fields.Item(SYMBOL_COL).Value = Symbols(i) Then

    Select Case rsAlerts.Fields.Item(OPERATOR_COL).Value

    Case "increases to"

    . . .

    Case "decreases to"

    . . .

    End Select

    rsAlerts.MoveNext()

    If rsAlerts.EOF Then

    Exit For

    End If

    End If

    Loop

    Next i

     

     

    The Checking Code runs about every 10 seconds, and the data in the DataGridView / Access database may change at any time. Why isn't the Checking Code "seeing" the changes made to the database??? BTW, this is all in the same thread. And I only have one copy of the database on my C: drive. And I'm not using AcceptChanges in my program. Thanks...

    Wednesday, April 9, 2008 9:03 PM

Answers

  • I finally found some sample code that helped me. Here's my solution:

     

    ' This code is executed once

    Dim cnAlert As New System.Data.OleDb.OleDbConnection(connStr)

    Dim daAlert As New OleDbDataAdapter("SELECT * FROM Alert ORDER BY Symbol", cnAlert)

    Dim dtAlert As New DataTable

     

    daAlert.UpdateCommand = New OleDbCommand

    daAlert.UpdateCommand.Connection = cnAlert

    daAlert.UpdateCommand.CommandText = "UPDATE Alert SET Triggered = ? WHERE Symbol = ? AND Operator = ?"

    daAlert.UpdateCommand.Parameters.Add("?", OleDbType.Boolean, 1, "Triggered")

    daAlert.UpdateCommand.Parameters.Add("?", OleDbType.VarChar, 6, "Symbol")

    daAlert.UpdateCommand.Parameters.Add("?", OleDbType.VarChar, 20, "Operator")

     

    ' This code is executed repeatedly

    dtAlert.Rows.Clear()

    daAlert.Fill(dtAlert)

    intRow = 0

    For i = 5 To aUBound

    Do While dtAlert.Rows(intRow).ItemArray(SYMBOL_COL) <= Symbols(i)

    If dtAlert.Rows(intRow).ItemArray(SYMBOL_COL) = Symbols(i) Then

    Select Case dtAlert.Rows(intRow).ItemArray(OPERATOR_COL)

    Case "increases to"

    If LastPrice(i) >= dtAlert.Rows(intRow).ItemArray(LEVEL_COL) And _

    dtAlert.Rows(intRow).ItemArray(TRIGGERED_COL) = False Then

    row = dtAlert.Rows(intRow)

    row(TRIGGERED_COL) = True

    daAlert.Update(dtAlert)

     

    Monday, May 5, 2008 5:28 PM

All replies

  • This forum is for ADO.Net, which is a different technology from ADO, even when used from managed code.  Try posting the question in this forum: http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.data.ado&lang=en&cr=US
    Wednesday, April 9, 2008 11:31 PM
  • Well, is that my problem? Would this work better if I used DataSets or DataTables instead of a Recordset? The binding between the DataGridView and Access uses:

     

    Me.AlertTableAdapter.Fill(Me.AlertsDataSet.Alert)

     

    Isn't that ADO.NET? Is my problem that I'm mixing ADO with ADO.NET?

    Thursday, April 10, 2008 12:30 AM
  • The original post was not ADO.NET-related, bot was COM-based ADO.

    Me.AlertTableAdapter.Fill(Me.AlertsDataSet.Alert) is ADO.NET. It looks like you have mix of ADO and ADO.NET and it probably causes some issues.

     

    Thursday, April 10, 2008 10:20 AM
    Moderator
  • I got a very nice reply from Bob Barrows at the MSDN Newsgroup you sent me to:

     

    That's an oxymoron: ADO.Net does not have a recordset object. You are using
    a classic COM ADODB connection and recordset via Interop, and there really
    is no reason to be doing so, simple database or otherwise. You're in the
    ..Net world so stick with the .Net objects: use an oledatareader to retrieve
    data, and an olecommand object to perform your updates (it is usually bad
    practice to maintain data with a cursor - DML SQL - update, insert and
    delete, is so much more efficient) so you don't have to open another
    connection to the database. You are preventing session pooling from working
    to your best advantage by doing using different connections.

    What you are undoubtedly falling victim to is Jet's delayed-write
    optimization. Jet caches writes to the database for some period of time to
    improve performance. See
    http://support.microsoft.com/kb/200300
    and (not so relevant, but illuminating):
    http://support.microsoft.com/?kbid=240317

     

    Thursday, April 10, 2008 3:24 PM
  • I rewrote the Checking Code using ADO.NET. I'm close, but I've been stuck for hours on a syntax error in my UPDATE command. This is pretty simple, so could someone please help me? Here's the rewritten code:

    Code Snippet

    Dim AlertConn As New System.Data.OleDb.OleDbConnection(connStr)

    Dim AlertAdapter As New OleDbDataAdapter("SELECT * FROM Alert ORDER BY Symbol", _ AlertConn)

    Dim AlertDataSet As New DataSet()

    AlertConn.Open()

    ' Copy the selected data into a dataset

    AlertAdapter.Fill(AlertDataSet)

     

    Dim AlertTable As DataTable

    Dim intRow As Integer

    Dim row As DataRow

    Dim i As Integer

    For Each AlertTable In AlertDataSet.Tables

    intRow = 0

    For i = 5 To aUBound

    Do While AlertTable.Rows(intRow).ItemArray(SYMBOL_COL) <= Symbols(i)

    If AlertTable.Rows(intRow).ItemArray(SYMBOL_COL) = Symbols(i) Then

    Select Case AlertTable.Rows(intRow).ItemArray(OPERATOR_COL)

    Case "increases to"

    If LastPrice(i) >= AlertTable.Rows(intRow).ItemArray (LEVEL_COL) And _

    AlertTable.Rows(intRow).ItemArray(TRIGGERED_COL) = False Then

    row = AlertTable.Rows(intRow)

    'AlertTable.AcceptChanges()

    row(TRIGGERED_COL) = True ' this is where I change the data

    AlertAdapter.UpdateCommand = New OleDbCommand("UPDATE Alert", AlertConn)

    AlertAdapter.Update(AlertDataSet)

    End If

    End Select

    intRow = intRow + 1

    If intRow = AlertTable.Rows.Count Then Exit For

    End If

    Loop

    Next i

    Next AlertTable

     

     

     

    Thursday, April 10, 2008 3:45 PM
  • The key to the table is Symbol and Operator. I've changed the SQL to:

     

    "UPDATE Alert SET Triggered = @Triggered WHERE Symbol = @Symbol AND Operator = @Operator"

     

    now the error is ex.Message = "No value given for one or more required parameters."

     

    I can't seem to find a simple How To: UPDATE or overview or tutorial... please help!

    Thursday, April 10, 2008 4:42 PM
  • Did you add three parameters to the Parameters collection of your UpdateCommand object. There is a relatively simple example in the below article:

     

    http://msdn2.microsoft.com/en-us/library/33y2221y(VS.71).aspx

     

    BTW, add them in the order they appear in the SQL command. You cannot re-order parameters by name when using the OleDb library.

     

    Thursday, April 10, 2008 8:04 PM
  • After many more hours of reading about various permutations and fooling around by trial and error I finally found a post which lead me to this fix:

     

    Dim myBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(AlertAdapter)

    ' Code to modify data in DataSet here
    myBuilder.GetUpdateCommand()

    AlertAdapter.Update(AlertDataSet)

    Monday, April 14, 2008 5:54 PM
  • The above fix gives me a concurrency violation in the Release version. So I went back to doing the UPDATE explicitly with parameters and it works on the first record (row), but not on subsequent records. As I step thru it it appears to work, but the second and subsequent Updates do not affect the Access table. The key to the table is the combination of the Symbol and Operator. This is maddening. I've spent so many hours on this.

     

    Code Snippet

    Dim AlertTable As DataTable

    Dim intRow As Integer

    Dim row As DataRow

    Dim i As Integer

    Dim UpdateCMD As OleDbCommand = New OleDbCommand("UPDATE Alert SET Triggered = ? WHERE Symbol = ? AND Operator = ?", AlertConn)

    For Each AlertTable In AlertDataSet.Tables

    intRow = 0

    For i = 5 To aUBound

    Do While AlertTable.Rows(intRow).ItemArray(SYMBOL_COL) <= Symbols(i)

    If AlertTable.Rows(intRow).ItemArray(SYMBOL_COL) = Symbols(i) Then

    Select Case AlertTable.Rows(intRow).ItemArray(OPERATOR_COL)

    Case "increases to"

    If LastPrice(i) >= AlertTable.Rows(intRow).ItemArray(LEVEL_COL) And _

    AlertTable.Rows(intRow).ItemArray(TRIGGERED_COL) = False Then

    row = AlertTable.Rows(intRow)

    row(TRIGGERED_COL) = True

    ' Add parameters and set values

    UpdateCMD.Parameters.Add("Triggered", OleDbType.Boolean).Value = True

    UpdateCMD.Parameters.Add("Symbol", OleDbType.VarWChar, 6).Value = Symbols(i)

    UpdateCMD.Parameters.Add("Operator", OleDbType.VarWChar, 20).Value = "increases to"

    AlertAdapter.UpdateCommand = UpdateCMD

    AlertAdapter.Update(AlertDataSet)

    frmAlerts.AlertTableAdapter.Fill(frmAlerts.AlertsDataSet.Alert) ' reload data from Access into dgvAlerts

    End If

     

     

    Wednesday, April 16, 2008 3:32 PM
  • It could be because you are creating parameters again and again inside of loop for the same command. It means with each iteration you increase number of passed parameters to the command and it could lead to that failuret. You should move part of code, which creates parameters, outside of the loop and inside of it only assign values. Something like

     

    Dim AlertTable As DataTable

    Dim intRow As Integer

    Dim row As DataRow

    Dim i As Integer

    Dim UpdateCMD As OleDbCommand = New OleDbCommand("UPDATE Alert SET Triggered = ? WHERE Symbol = ? AND Operator = ?", AlertConn)

     

     

    UpdateCMD.Parameters.Add("Triggered", OleDbType.Boolean)

    UpdateCMD.Parameters.Add("Symbol", OleDbType.VarWChar, 6)

    UpdateCMD.Parameters.Add("Operator", OleDbType.VarWChar, 20)

    AlertAdapter.UpdateCommand = UpdateCMD

     

    For Each AlertTable In AlertDataSet.Tables

    intRow = 0

    For i = 5 To aUBound

    Do While AlertTable.Rows(intRow).ItemArray(SYMBOL_COL) <= Symbols(i)

    If AlertTable.Rows(intRow).ItemArray(SYMBOL_COL) = Symbols(i) Then

    Select Case AlertTable.Rows(intRow).ItemArray(OPERATOR_COL)

    Case "increases to"

    If LastPrice(i) >= AlertTable.Rows(intRow).ItemArray(LEVEL_COL) And _

    AlertTable.Rows(intRow).ItemArray(TRIGGERED_COL) = False Then

    row = AlertTable.Rows(intRow)

    row(TRIGGERED_COL) = True

    ' Add parameters and set values

    UpdateCMD.Parameters("Triggered").Value = True

    UpdateCMD.Parameters("Symbol").Value = Symbols(i)

    UpdateCMD.Parameters("Operator").Value = "increases to"

    AlertAdapter.Update(AlertDataSet)

    frmAlerts.AlertTableAdapter.Fill(frmAlerts.AlertsDataSet.Alert) ' reload data from Access into dgvAlerts

    End If

    Thursday, April 17, 2008 9:46 AM
    Moderator
  • Thanks, VMazur. That certainly makes sense (doh!). Unfortunately, the .Update is throwing:

     

    Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

     

    So, I'm back to that. Any suggestions? It's a single user system. The table is bound to a DataGridView control in a different part of the program, but there's nothing going on there at the time of the error. Thanks for your help...

    Thursday, April 17, 2008 2:46 PM
  • How does your code look like right now? Where exactly does it throw exception now?

     

    Friday, April 18, 2008 9:42 AM
    Moderator
  • Here is the relevant code. I get the concurrency violation on the .Update (see comment below), the first time thru the loop. The Access table has a two field primary key: Symbol and Operator. I know the command builder generates a much more complicated UPDATE command, but it too threw a concurrency violation. I'm not changing the primary key fields in the loop, yet I have to set the Parameters for them? I'm starting to think it would be easier to give up and enroll in med school! Thanks for your help...

     

    '  This code is executed once

    Dim AlertConn As New System.Data.OleDb.OleDbConnection(connStr)

    Dim AlertAdapter As New OleDbDataAdapter("SELECT * FROM Alert ORDER BY Symbol", AlertConn)

    Dim AlertDataSet As New DataSet()

    Dim UpdateCMD As OleDbCommand = New OleDbCommand("UPDATE Alert SET Triggered = ? WHERE Symbol = ? AND Operator = ?", AlertConn)

     

    AlertConn.Open()

    ' Copy the selected data into a dataset

    AlertAdapter.Fill(AlertDataSet)

    UpdateCMD.Parameters.Add("Symbol", OleDbType.VarWChar, 6)

    UpdateCMD.Parameters.Add("Operator", OleDbType.VarWChar, 20)

    UpdateCMD.Parameters.Add("Triggered", OleDbType.Boolean)

    AlertAdapter.UpdateCommand = UpdateCMD

     

    '  This code is executed repeatedly

    For Each AlertTable In AlertDataSet.Tables

    intRow = 0

    For i = 5 To aUBound

    Do While AlertTable.Rows(intRow).ItemArray(SYMBOL_COL) <= Symbols(i)

    If AlertTable.Rows(intRow).ItemArray(SYMBOL_COL) = Symbols(i) Then

    Select Case AlertTable.Rows(intRow).ItemArray(OPERATOR_COL)

    Case "increases to"

    If LastPrice(i) >= AlertTable.Rows(intRow).ItemArray(LEVEL_COL) And _

    AlertTable.Rows(intRow).ItemArray(TRIGGERED_COL) = False Then

    row = AlertTable.Rows(intRow)

    row(TRIGGERED_COL) = True

    ' Add parameters and set values

    UpdateCMD.Parameters("Triggered").Value = True

    UpdateCMD.Parameters("Symbol").Value = Symbols(i)

    UpdateCMD.Parameters("Operator").Value = "increases to"

    ' the following line throws the Concurrency violation

    AlertAdapter.Update(AlertDataSet)

    frmAlerts.AlertTableAdapter.Fill(frmAlerts.AlertsDataSet.Alert) ' reload data from Access into dgvAlerts

    End If

    End Select

    intRow = intRow + 1

    If intRow = AlertTable.Rows.Count Then Exit For

    End If

    Loop

    Next i

    Next AlertTable

    Friday, April 18, 2008 1:18 PM
  • In the hopes that someone will please help me, I've produce a simple test app that you can do at home.

    1. Create a VB Windows App and put it in a folder called ADOTest.

    2. Create an Access (I've tried Access 2000 and Access 2002) table named ADOTest with two fields:

    Symbol, 6 char string (make Symbol the primary key)

    Triggerred, a Yes/No Boolean field

    3. Add a record where Symbol = "MSFT" and Triggered = False.

    4. Make sure the System.Data.OleDb namespace is in your project.

    5. Use the Data menu to Add a New Data Source. Use the wizard to add the ADOTest table to your project.

    6. Add a Button1 to the Form.

    7. Copy the following code into your Project and adjust the connStr to point to your ADOTest folder.

    8. Compile and step thru the project and see the concurrency violation for yourself. It can't get much simpler than this. Does ADO.NET suck or is just me (I'm hoping it's me). Thanks for your help!!!

     

    Public Class Form1

    Const SYMBOL_COL As Integer = 0

    Const TRIGGERED_COL As Integer = 1

    Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Apps\ADOTest\ADOTest.mdb;Persist Security Info=True"

    Dim ADOTestConn As New System.Data.OleDb.OleDbConnection(connStr)

    Dim ADOTestAdapter As New OleDbDataAdapter("SELECT * FROM ADOTest ORDER BY Symbol", ADOTestConn)

    Dim ADOTestDataSet As New DataSet()

    Dim UpdateCMD As OleDbCommand = New OleDbCommand("UPDATE ADOTest SET Triggered = ? WHERE Symbol = ?", ADOTestConn)

     

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    ADOTestConn.Open()

    UpdateCMD.Parameters.Add("Symbol", OleDbType.VarChar, 6)

    UpdateCMD.Parameters.Add("Triggered", OleDbType.Boolean)

    ADOTestAdapter.UpdateCommand = UpdateCMD

    ' Copy the selected data into a dataset

    ADOTestAdapter.Fill(ADOTestDataSet)

    End Sub

     

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim ADOTestTable As DataTable

    Dim intRow As Integer

    Dim row As DataRow

    Try

    ' Copy the selected data into a dataset

    ADOTestAdapter.Fill(ADOTestDataSet)

    For Each ADOTestTable In ADOTestDataSet.Tables

    intRow = 0

    If ADOTestTable.Rows(intRow).ItemArray(SYMBOL_COL) = "MSFT" Then

    row = ADOTestTable.Rows(intRow)

    row(TRIGGERED_COL) = True

    ' Add parameters and set values

    UpdateCMD.Parameters("Triggered").Value = True

    UpdateCMD.Parameters("Symbol").Value = ADOTestTable.Rows(intRow).ItemArray(SYMBOL_COL)

    ADOTestAdapter.Update(ADOTestDataSet)

    ADOTestDataSet.AcceptChanges()

    End If

    intRow = intRow + 1

    If intRow = ADOTestTable.Rows.Count Then Exit For

    Next ADOTestTable

    Catch ex As Exception

    MsgBox(ex.Message)

    End Try

    End Sub

    End Class

     

    Tuesday, April 22, 2008 12:50 AM
  • I finally found some sample code that helped me. Here's my solution:

     

    ' This code is executed once

    Dim cnAlert As New System.Data.OleDb.OleDbConnection(connStr)

    Dim daAlert As New OleDbDataAdapter("SELECT * FROM Alert ORDER BY Symbol", cnAlert)

    Dim dtAlert As New DataTable

     

    daAlert.UpdateCommand = New OleDbCommand

    daAlert.UpdateCommand.Connection = cnAlert

    daAlert.UpdateCommand.CommandText = "UPDATE Alert SET Triggered = ? WHERE Symbol = ? AND Operator = ?"

    daAlert.UpdateCommand.Parameters.Add("?", OleDbType.Boolean, 1, "Triggered")

    daAlert.UpdateCommand.Parameters.Add("?", OleDbType.VarChar, 6, "Symbol")

    daAlert.UpdateCommand.Parameters.Add("?", OleDbType.VarChar, 20, "Operator")

     

    ' This code is executed repeatedly

    dtAlert.Rows.Clear()

    daAlert.Fill(dtAlert)

    intRow = 0

    For i = 5 To aUBound

    Do While dtAlert.Rows(intRow).ItemArray(SYMBOL_COL) <= Symbols(i)

    If dtAlert.Rows(intRow).ItemArray(SYMBOL_COL) = Symbols(i) Then

    Select Case dtAlert.Rows(intRow).ItemArray(OPERATOR_COL)

    Case "increases to"

    If LastPrice(i) >= dtAlert.Rows(intRow).ItemArray(LEVEL_COL) And _

    dtAlert.Rows(intRow).ItemArray(TRIGGERED_COL) = False Then

    row = dtAlert.Rows(intRow)

    row(TRIGGERED_COL) = True

    daAlert.Update(dtAlert)

     

    Monday, May 5, 2008 5:28 PM