locked
DbConcurrency exception? Single user with local db.. very confused RRS feed

  • Question

  • Hi Everyone,

    This really has me stumped...

    I installed a copy of VB2008 Express Edition along with SQL Server Compact Edition to develop a small app.
    I have a datagrid bound to one table - CARS.  I display all the fields in the datagrid and set it to readonly.

    I created a connection to the sql table and a CarsDataSet was created along with the usual data adapter and carsBindingSource.  The Cars table consists of a primary key, CarID, which is an autoincrement Identity column.

    The columns consist of the usual motor vehicle description strings (Make, Model, Year, etc), a few integers pointing to related tables, and about 30-40 boolean fields that represent the car options. 

    To make data entry easier, I set up a tab control to break up the vehicle info into two tabs - Details and Options.  The Details tab consists of text boxes and combo boxes.  The Options tab has all the checkboxes for every possible option.

    All of these controls are bound to the fields in the CarsBindingSource, which happened automatically when I dropped the detail fields onto the designer.  I left the CarsBindingNavigator alone, and it seems to work fine.  The problem occurs when I click the Save button (disk icon) on the Binding Navigator. 

    When the form first opens up, the Details tab is active.  I start by clicking the Add New button (+ icon) which creates a new row in the datagrid.  Then I proceed to fill out all the text and combo boxes with the car info.  Once I finish these fields and click the Options tab, I notice the grid is updated with the data that I just entered, leaving all options displayed with their default value of FALSE.

    Next I choose the options by checking some boxes, and when I click the Save button to send all the data back to the Cars table, I get the dbconcurrency exception saying that 0 rows were updated.

    I cant figure out what the problem could be.  I read that this exception occurs in a multi-user environment where data in the underlying database is changed outside of the current application.  This make sense to me, as someone could easily change a row of data after I have loaded it into the dataset.  But this is a local database table, and I am the only user.  Additionally, the table is empty when I start the application, so there should not be any records in there anyway.

    Is there any way that I can avoid this check altogether?  Since it will always be a single user app, I would like the table to be updated with the latest row changes wihtout worrying about overwriting someone else's changes.

    Thanks in advance,
    -Ben M.
    Thursday, April 24, 2008 2:25 PM

Answers

  • I was thinking about this a little more and there might be a simplier solution.  In the form load event you are filling the Makes table twice.  Maybe when you are filling the makes table the second time it marking all the makes records as changed and when you send the data to the database you are getting an error because none of the records are being changed.  I would change

     

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'CarsdbDataSet.Styles' table. You can move, or remove it, as needed.
            Me.StylesTableAdapter.Fill(Me.CarsdbDataSet.Styles)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Colors' table. You can move, or remove it, as needed.
            Me.ColorsTableAdapter.Fill(Me.CarsdbDataSet.Colors)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Models' table. You can move, or remove it, as needed.
            Me.ModelsTableAdapter.Fill(Me.CarsdbDataSet.Models)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Makes' table. You can move, or remove it, as needed.
            Me.MakesTableAdapter.Fill(Me.CarsdbDataSet.Makes)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Makes' table. You can move, or remove it, as needed.
            Me.MakesTableAdapter.Fill(Me.CarsdbDataSet.Makes)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Cars' table. You can move, or remove it, as needed.
            Me.CarsTableAdapter.Fill(Me.CarsdbDataSet.Cars)
    End Sub

     

     

    to

     

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'CarsdbDataSet.Styles' table. You can move, or remove it, as needed.
            Me.StylesTableAdapter.Fill(Me.CarsdbDataSet.Styles)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Colors' table. You can move, or remove it, as needed.
            Me.ColorsTableAdapter.Fill(Me.CarsdbDataSet.Colors)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Models' table. You can move, or remove it, as needed.
            Me.ModelsTableAdapter.Fill(Me.CarsdbDataSet.Models)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Makes' table. You can move, or remove it, as needed.
            Me.MakesTableAdapter.Fill(Me.CarsdbDataSet.Makes)

            'TODO: This line of code loads data into the 'CarsdbDataSet.Cars' table. You can move, or remove it, as needed.
            Me.CarsTableAdapter.Fill(Me.CarsdbDataSet.Cars)
    End Sub


     

    Saturday, April 26, 2008 11:34 AM

All replies

  • Could you post some code.  I you should not be getting this error
    Thursday, April 24, 2008 3:48 PM
  • Which code would you like me to post?

    Most of the code in this project was autogenerated by dragging and dropping the Cars table (grid/details) onto the form.  Im not sure exactly where that code is located.  I do have several functions and subroutines that I wrote to do other tasks unrelated to the database updates.

    Please advise,
    -Ben
    Thursday, April 24, 2008 4:30 PM
  • How about the code for when you save the data to the database
    Thursday, April 24, 2008 4:44 PM
  • Sure,

    Here is the form load:

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'CarsdbDataSet.Styles' table. You can move, or remove it, as needed.
            Me.StylesTableAdapter.Fill(Me.CarsdbDataSet.Styles)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Colors' table. You can move, or remove it, as needed.
            Me.ColorsTableAdapter.Fill(Me.CarsdbDataSet.Colors)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Models' table. You can move, or remove it, as needed.
            Me.ModelsTableAdapter.Fill(Me.CarsdbDataSet.Models)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Makes' table. You can move, or remove it, as needed.
            Me.MakesTableAdapter.Fill(Me.CarsdbDataSet.Makes)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Makes' table. You can move, or remove it, as needed.
            Me.MakesTableAdapter.Fill(Me.CarsdbDataSet.Makes)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Cars' table. You can move, or remove it, as needed.
            Me.CarsTableAdapter.Fill(Me.CarsdbDataSet.Cars)
    End Sub


    Here is the code for the Save button in the binding navigator:

    Private Sub CarsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CarsBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.CarsBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.CarsdbDataSet)
    End Sub

    Please let me know if you would like to see any additional code.

    Thanks,
    -Ben



    Thursday, April 24, 2008 5:00 PM
  • Sorry it took a few days to get back to you.  The table adapter manager's updateall method will tell all the table adapters for all the tables in the dataset to update the database.  The most likely cause is that you 2 related tables where you are only adding a row to one of the 2 tables.  For example you have a orders and orders-details table and you only add a new order but no data to the orders-details table.

    Saturday, April 26, 2008 11:06 AM
  • I was thinking about this a little more and there might be a simplier solution.  In the form load event you are filling the Makes table twice.  Maybe when you are filling the makes table the second time it marking all the makes records as changed and when you send the data to the database you are getting an error because none of the records are being changed.  I would change

     

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'CarsdbDataSet.Styles' table. You can move, or remove it, as needed.
            Me.StylesTableAdapter.Fill(Me.CarsdbDataSet.Styles)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Colors' table. You can move, or remove it, as needed.
            Me.ColorsTableAdapter.Fill(Me.CarsdbDataSet.Colors)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Models' table. You can move, or remove it, as needed.
            Me.ModelsTableAdapter.Fill(Me.CarsdbDataSet.Models)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Makes' table. You can move, or remove it, as needed.
            Me.MakesTableAdapter.Fill(Me.CarsdbDataSet.Makes)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Makes' table. You can move, or remove it, as needed.
            Me.MakesTableAdapter.Fill(Me.CarsdbDataSet.Makes)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Cars' table. You can move, or remove it, as needed.
            Me.CarsTableAdapter.Fill(Me.CarsdbDataSet.Cars)
    End Sub

     

     

    to

     

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'CarsdbDataSet.Styles' table. You can move, or remove it, as needed.
            Me.StylesTableAdapter.Fill(Me.CarsdbDataSet.Styles)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Colors' table. You can move, or remove it, as needed.
            Me.ColorsTableAdapter.Fill(Me.CarsdbDataSet.Colors)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Models' table. You can move, or remove it, as needed.
            Me.ModelsTableAdapter.Fill(Me.CarsdbDataSet.Models)
            'TODO: This line of code loads data into the 'CarsdbDataSet.Makes' table. You can move, or remove it, as needed.
            Me.MakesTableAdapter.Fill(Me.CarsdbDataSet.Makes)

            'TODO: This line of code loads data into the 'CarsdbDataSet.Cars' table. You can move, or remove it, as needed.
            Me.CarsTableAdapter.Fill(Me.CarsdbDataSet.Cars)
    End Sub


     

    Saturday, April 26, 2008 11:34 AM