Datagrid and Managing Data


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 14 of 14

Thread: Datagrid and Managing Data

  1. #1
    Join Date
    Aug 2004
    Posts
    45

    Datagrid and Managing Data

    Hello all!

    Currently I have a project I am working on that requires the saving of data from a datagrid on the CurrentCellChanged event. I can handle this just fine but at almost the same place everytime I get the wonderful error "Update Command affected 0 records." from the DBConcurrencyException class. I understand that this is because the data is out of sync. The software I am creating will only have one user on it at a time. I am needing to find out how to handle this situation to overwrite the data everytime as there is not another user updating data somewhere.

    Currently I am using the SQLDataAdapter, SQLCommandBuilder, Datasets, Dataviews and DataTables to populate and edit the data in the datagrid. If there is a better way please feel free to let me know. I have been cranking of this issue way longer then I need to be and would like to get it resolved and out of my hair.

    Thanks in advance,
    BStenfors
    Sincerely,
    Bryan

  2. #2
    Join Date
    Dec 2004
    Posts
    163
    Bryan,

    I suspect that you are getting that error for some other reason than a true concurrency violation. After all, nobody is actually changing the data as you test your app, right?

    At any rate, if you really want to ignore concurrency violations, supply your own UpdateCommand and DeleteCommand to your data adapter, rather than allowing the command builder to create those commands.

    The command builder is adding concurrency checking that you say you don't need. Supply commands that just do updates and deletes, regardless of concurrency issues.

    Kerry Moorman

  3. #3
    Join Date
    Aug 2004
    Posts
    45
    So just do this by setting the UpdateCommand Property to a old fashion SQL UPDATE Statement?
    Sincerely,
    Bryan

  4. #4
    Join Date
    Dec 2004
    Posts
    61
    I used to have the problem like yours, but the problem is solved.
    I guess you're trying to update like this:
    Code:
    // You're trying to call Update() directly
    // c#/vb .NET 2.0 
    sqlDataAdapter.Update();
    
    // Now this is the most safe way
    
    // c# .NET 2.0
    sqlDataAdapter.Update(this.dataSet.Tables["TableName"].Select(null, null, DataViewRowState.Deleted));
    sqlDataAdapter.Update(this.dataSet.Tables["TableName"].Select(null, null, DataViewRowState.Addes));
    sqlDataAdapter.Update(this.dataSet.Tables["TableName"].Select(null, null, DataViewRowState.ModifiedCurrent));
    
    ' vb .NET 2.0
    
    sqlDataAdapter.Update(Me.dataSet.Tables["TableName"].Select(Nothing, Nothing, DataViewRowState.Deleted));
    sqlDataAdapter.Update(Me.dataSet.Tables["TableName"].Select(Nothing, Nothing, DataViewRowState.Addes));
    sqlDataAdapter.Update(Me.dataSet.Tables["TableName"].Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent));

  5. #5
    Join Date
    Aug 2004
    Posts
    45
    Kerry,

    I tried your method and I hope I can figure out what is going wrong. I implemented this method (manually setting the UpdateCommand.CommandText) but still get the UpdateCommand affected 0 records error.

    Have any ideas what I might be doing wrong?

    Thanks,
    BStenfors
    Sincerely,
    Bryan

  6. #6
    Join Date
    Dec 2004
    Posts
    163
    Bryan,

    What does your code look like?

    Kerry Moorman

  7. #7
    Join Date
    Aug 2004
    Posts
    45
    I am currently at work but will get it on the forum tonight.

    I do use the DataTable ColumnChanged event also which may have some factor in this but I would like to only use one event if possible.

    Thanks,
    BStenfors
    Sincerely,
    Bryan

  8. #8
    Join Date
    Aug 2004
    Posts
    45
    This is the link to the site that I used to code your method. Mine obviously doesn't look exact but this what I used.

    http://www.codeproject.com/cs/databa...thoutsqlcb.asp
    Sincerely,
    Bryan

  9. #9
    Join Date
    Aug 2004
    Posts
    45
    Kerry,

    Here is the code I am using exactly.

    Public Shared Sub UpdateCropInfo(ByVal oAdapter As SqlDataAdapter, ByVal oDS As DataSet, ByVal oConn As SqlConnection)
    With oAdapter
    ''Inserting
    Dim oInsert As New SqlCommand("INSERT INTO tblCropAcreage (FarmID, YearID, CommodityID, MiscellaneousAcresID, CropAcres) VALUES (@FarmID, @YearID, @CommodityID, @MiscellaneousAcresID, @CropAcres)")
    oInsert.CommandType = CommandType.Text
    .InsertCommand = oInsert
    .InsertCommand.Connection = oConn
    '.InsertCommand.CommandText = "INSERT INTO tblCropAcreage (FarmID, YearID, CommodityID, MiscellaneousAcresID, CropAcres) VALUES (@FarmID, @YearID, @CommodityID, @MiscellaneousAcresID, @CropAcres)"
    .InsertCommand.Parameters.Add("@FarmID", SqlDbType.NVarChar, 4, "FarmID")
    .InsertCommand.Parameters.Add("@YearID", SqlDbType.NVarChar, 4, "YearID")
    .InsertCommand.Parameters.Add("@CommodityID", SqlDbType.NVarChar, 4, "CommodityID")
    .InsertCommand.Parameters.Add("@MiscellaneousAcresID", SqlDbType.NVarChar, 4, "MiscellaneousAcresID")
    .InsertCommand.Parameters.Add("@CropAcres", SqlDbType.Decimal, 9, "CropAcres")

    ''Deleting
    Dim oDelete As New SqlCommand()
    .DeleteCommand = oDelete
    .DeleteCommand.Connection = oConn
    .DeleteCommand.CommandType = CommandType.Text
    .DeleteCommand.CommandText = "DELETE FROM tblCropAcreage WHERE CropAcresID = @CropAcresID"
    .DeleteCommand.Parameters.Add("@CropAcresID", SqlDbType.Int, 4, "CropAcresID")

    ''Updating
    Dim oUpdate As New SqlCommand()
    .UpdateCommand = oUpdate
    .UpdateCommand.Connection = oConn
    .UpdateCommand.CommandType = CommandType.Text
    .UpdateCommand.CommandText = "UPDATE tblCropAcreage SET FarmID = @FarmID, YearID = @YearID, CommodityID = @CommodityID, MiscellaneousAcresID = @MiscellaneousAcresID, CropAcres = @CropAcres WHERE CropAcresID = @CropAcresID"
    .UpdateCommand.Parameters.Add("@CropAcresID", SqlDbType.NVarChar, 9, "CropAcresID")
    .UpdateCommand.Parameters.Add("@FarmID", SqlDbType.NVarChar, 4, "FarmID")
    .UpdateCommand.Parameters.Add("@YearID", SqlDbType.NVarChar, 4, "YearID")
    .UpdateCommand.Parameters.Add("@CommodityID", SqlDbType.NVarChar, 4, "CommodityID")
    .UpdateCommand.Parameters.Add("@MiscellaneousAcresID", SqlDbType.NVarChar, 4, "MiscellaneousAcresID")
    .UpdateCommand.Parameters.Add("@CropAcres", SqlDbType.Decimal, 9, "CropAcres")

    .Update(oDS, "tblCropAcreage")
    End With
    End Sub
    Sincerely,
    Bryan

  10. #10
    Join Date
    Aug 2004
    Posts
    45
    Kerry,

    Here is the code I am using exactly.

    Public Shared Sub UpdateCropInfo(ByVal oAdapter As SqlDataAdapter, ByVal oDS As DataSet, ByVal oConn As SqlConnection)
    With oAdapter
    ''Inserting
    Dim oInsert As New SqlCommand("INSERT INTO tblCropAcreage (FarmID, YearID, CommodityID, MiscellaneousAcresID, CropAcres) VALUES (@FarmID, @YearID, @CommodityID, @MiscellaneousAcresID, @CropAcres)")
    oInsert.CommandType = CommandType.Text
    .InsertCommand = oInsert
    .InsertCommand.Connection = oConn
    '.InsertCommand.CommandText = "INSERT INTO tblCropAcreage (FarmID, YearID, CommodityID, MiscellaneousAcresID, CropAcres) VALUES (@FarmID, @YearID, @CommodityID, @MiscellaneousAcresID, @CropAcres)"
    .InsertCommand.Parameters.Add("@FarmID", SqlDbType.NVarChar, 4, "FarmID")
    .InsertCommand.Parameters.Add("@YearID", SqlDbType.NVarChar, 4, "YearID")
    .InsertCommand.Parameters.Add("@CommodityID", SqlDbType.NVarChar, 4, "CommodityID")
    .InsertCommand.Parameters.Add("@MiscellaneousAcresID", SqlDbType.NVarChar, 4, "MiscellaneousAcresID")
    .InsertCommand.Parameters.Add("@CropAcres", SqlDbType.Decimal, 9, "CropAcres")

    ''Deleting
    Dim oDelete As New SqlCommand()
    .DeleteCommand = oDelete
    .DeleteCommand.Connection = oConn
    .DeleteCommand.CommandType = CommandType.Text
    .DeleteCommand.CommandText = "DELETE FROM tblCropAcreage WHERE CropAcresID = @CropAcresID"
    .DeleteCommand.Parameters.Add("@CropAcresID", SqlDbType.Int, 4, "CropAcresID")

    ''Updating
    Dim oUpdate As New SqlCommand()
    .UpdateCommand = oUpdate
    .UpdateCommand.Connection = oConn
    .UpdateCommand.CommandType = CommandType.Text
    .UpdateCommand.CommandText = "UPDATE tblCropAcreage SET FarmID = @FarmID, YearID = @YearID, CommodityID = @CommodityID, MiscellaneousAcresID = @MiscellaneousAcresID, CropAcres = @CropAcres WHERE CropAcresID = @CropAcresID"
    .UpdateCommand.Parameters.Add("@CropAcresID", SqlDbType.NVarChar, 9, "CropAcresID")
    .UpdateCommand.Parameters.Add("@FarmID", SqlDbType.NVarChar, 4, "FarmID")
    .UpdateCommand.Parameters.Add("@YearID", SqlDbType.NVarChar, 4, "YearID")
    .UpdateCommand.Parameters.Add("@CommodityID", SqlDbType.NVarChar, 4, "CommodityID")
    .UpdateCommand.Parameters.Add("@MiscellaneousAcresID", SqlDbType.NVarChar, 4, "MiscellaneousAcresID")
    .UpdateCommand.Parameters.Add("@CropAcres", SqlDbType.Decimal, 9, "CropAcres")

    .Update(oDS, "tblCropAcreage")
    End With
    End Sub
    Sincerely,
    Bryan

  11. #11
    Join Date
    Dec 2004
    Posts
    61
    BStenfors,

    I guess there is nothing wrong with your sql codes because if it is wrong you'll get the SqlException not the DBConcurrencyException, except for the
    Code:
    .Update(oDS, "tblCropAcreage")
    That codes would not be necessary wrong if you try update only single resultset, but if you try to update all the changes from your dataset, you’ll have to do task via step-by-step way. Have you ever try my codes from my last post?

    By the way, this is my standard way for updating dataSet
    Code:
    ' vb .net 2.0
    
    ' if i don't call EndCurrentEdit() than DataSet.HasChanges will always false,
    ' because it wouldn't see any changes until EndCurrentEdit() is called.
    Me.BindingContext().Item("MyDataSet", "MyTable").EndCurrentEdit()
    
    ' or you can call EndEdit from your bindingSource, i prefer to use 
    ' bindingSource rather than to use BindingContext
    bindingSource.EndEdit()
    
    ' i do this because i want to check wheter the dataSet has changes or not.
    If MyDataSet.HasChanges() Then
       ' i call the overloaded update method that accept array of DataRow
       ' because i want to update only the filtered rows.
       ' i want to filter only the row state, so in the Select() method from the 
       ' dataSet i set the first and the second parameters to Nothing.
       MyDataAdapter.Update(MyDataSet.Tables("MyTable").Select(Nothing, Nothing, DataViewRowState.Deleted)
       MyDataAdapter.Update(MyDataSet.Tables.("MyTable").Select(Nothing, Nothing, DataViewRowState.Added)
       MyDataAdapter.Update(MyDataSet.Tables("MyTable").Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent)
    End If
    
    ' After finish with the update then i call the AcceptChanges() so all 
    ' the rows that have state is DataViewRowState.ModifiedCurrent will 
    ' have the state back to DataViewRowState.UnChanged.
    MyDataSet.AcceptChanges()
    We try to update via step-by-step way, we update only what we need, so from my experience that is the most safe way to keep you away from DBConcurrencyException. And also don't forget to set the complete command parameters for Update/Insert/Delete statements just like the Visual Studio did if you use the DataSource/DataSet wizard, it's another way to prevent us from the DBConcurrencyException.
    me_code2004

  12. #12
    Join Date
    Aug 2004
    Posts
    45
    me_code2004

    I just tried your method and I was praying that it would work. Unfortunately no dice. I am still trying to find out. I am wondering if it is other code that is causing this to happen. Do you have any suggestions on a basic idea to test this out?

    Thanks,
    BStenfors
    Sincerely,
    Bryan

  13. #13
    Join Date
    Dec 2004
    Posts
    61
    BStenfors,

    Did you have debug the RowUpdating Event from your DataAdapter?
    Code:
    ' vb .net 2.0
    
    ' local variables
    Dim WithEvents mySqlDataAdaper as SqlDataAdapter
    
    Private Sub mySqlDataAdapter_RowUpdating(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatingEventArgs) Handles mySqlDataAdapter.RowUpdating
       ' you can check the e's properties (ex: command, status, error, etc).
       ' so if you get error here, you can start to figure out.
    End Sub
    
    Private Sub mySqlDataAdapter_RowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs) Handles mySqlDataAdapter.RowUpdated
       ' if you can get here then there was no error.
    End Sub
    I guess you have problems with your record when you're trying to update or with your dataset/database (constraint problem, etc).
    me_code2004

  14. #14
    Join Date
    Aug 2004
    Posts
    45
    No I did not do the error checking on row update. Yes it is always happening on a update. In this specific scenario it is happening on the very first update. So I fill in the first field to add the new row and it works flawlessly. I go to add data to the second field (aka make an update) and bamb Update error. I am dealing with a simple SELECT * FROM tblGroundWork statement. I also use the DT_ColumnChanged Event if that would srew anything up at all. What kind of (constraint, etc.) would screw this up? I can investigate to see if that is the case if I know what to be investigating.

    Thanks a ton for your help! I have been fighting this for a long long long time and it is the only thing keeping me from releasing my software to the public.

    Thanks again!
    Sincerely,
    Bryan

Similar Threads

  1. datagrid
    By kei in forum VB Classic
    Replies: 7
    Last Post: 12-04-2000, 05:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center