Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
|
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 25th, 2007, 08:13 PM
Friend of Wrox
 
Join Date: Apr 2004
Posts: 204
Thanks: 0
Thanked 0 Times in 0 Posts
Default Concurrency Violation Problem - Please HELP!

Hi,

I'm hoping someone can help me. I've been at it for hours and I'm at a complete loss. Please have a look and if you think you know something, anything, that could help me see what I might be missing here, please send a reply.

I'm trying to update a table using the DataAdapter.Update method. I've done this before successfully, using the SQLCommandBuilder object, and passing it a simple SELECT Query, e.g. "SELECT * FROM tbl_PageVersions".
Code:
'Note this is for a Content Management System i'm building
'The two tables, tbl_Pages and tbl_PageVersions are related on 
'tbl_Pages.pkPageID = tbl_PageVersions.fkPageID

Public Shared Sub newVersion(ByVal pageID As Int32, ByVal content As String, ByVal versionName As String)

    'Get connection and command from Data layer.  Automatically sets conecttion string etc.
    Dim cnxn As SqlConnection = getConnection()
    Dim cmd As SqlCommand = getCommand(cnxn)

    cmd.Parameters.Add(New SqlParameter("@PageID", pageID))
    cmd.CommandText = "INSERT INTO tbl_PageVersions (fkPageID) VALUES (@PageID) SELECT @@Identity"

    'Use the database to generate the next Identity value
    'I do this because I don't trust ADO AutoIncrement columns to stay in sync with the database
    Dim dr As DataRow = dsPages.Tables("Versions").NewRow
    dr("fkPageID") = getPageID(versionID)
    dr("pkPageVersionID") = cmd.ExecuteScalar

    dr.AcceptChanges()  'changes rowState from Inserted to Unchanged (although the very next instruction changes it to ModifiedCurrent)
    dr("txtVersionName") = versionName
    dr("txtContent") = content
    dsPages.Tables("Versions").Rows.Add(dr)

    RefreshRow (dr)  ' calls the dataAdapter.Update method

    cnxn.dispoe()
    cmd.dispose()
The above code manaully Inserts a record in the SQL database, passing in just the foreign key. It passes back the new Primary Key with the SELECT @@Identity thingy.

I then create a new row in the ADO dataTable. I set it's pkPageVersionID (the primary key) to the new key the database just returned. Then I call the Row's AcceptChanges method. I do this so that the dataAdapter's Update method won't try to insert a new row. It'll just see the row as a modified original row, and will use an UPDATE command to update it.

Then I finish updating the datarow with the values passed into the method. Finally, I call a subroutine which will call the dataAdapter's UPDATE method (shown below).

Now this may seem a ass-backwards way of doing things. I mean, why not just pass all the data to the data store in the INSERT query. Well, I have a reason and, what's more, it doesn't seem to matter. I get the same result, as you will see later.

Here is the RefreshRow method...
Code:
     Private Shared Sub RefreshRow(ByRef dr As DataRow)
        Dim drArray(0) As DataRow
        drArray(0) = dr 'allows me to use the overload version that takes an array of datarows

        Dim cnxn As SqlConnection = getConnection()
        Dim cmd As SqlCommand = getCommand(cnxn)

        If dr.Table.TableName = "Pages" Then
            cmd.CommandText = "SELECT * FROM tbl_Pages"
        Else
            cmd.CommandText = "SELECT * FROM tbl_PageVersions"
        End If

        Dim da As New SqlDataAdapter(cmd)
        AddHandler da.RowUpdating, AddressOf HandleUpdate
        Dim commandBuilder As New SqlCommandBuilder(da)
        da.Update(drArray)

        cnxn.Close()
        da.Dispose()
        cmd.Dispose()
        cnxn.Dispose()

    End Sub
First, you'll notice the trick I do when I pass in a datarow, but immediately convert it to a datarow array. This allows me to use the version of da.Update() that takes an array of dataRows. I've also tried this passing in a dataTable, and a whole dataSet, but I get the same results.

Also, notice the handler I added...
Code:
        AddHandler da.RowUpdating, AddressOf HandleUpdate
I wrote an event handler so that I could look at the actual command that was being generated by the SqlCommandBuilder. Here is the handler...
Code:
    Protected Shared Sub HandleUpdate(ByVal sender As Object, ByVal e As SqlRowUpdatingEventArgs)
        'do nothing (I just put a breakpoint here)
    End Sub
When I debug, and I look at the command it always comes up as Nothing.

Now even if I don't call the dataAdapter.Update method here, if I just Insert a row into the database, grab its identity, insert a new row into my dataTable, set it's values, then call its AcceptChanges() method so it won't even show up as modified, I still get a concurrency error the very nrxt time another part of the application calls a dataAdapter.Update method (I've been using this approach successfully in other parts of the program).

I know my approach is funky here, and that's what's causing the error (it's not that Bill Gates is just inheritantly evil or anything like that).

Please send me any advice at all, even if you don't know, or think it might be wrong. Anything at all could help me see what I'm evidently missing here.

Thank you.

Aaron

 
Old March 25th, 2007, 08:15 PM
Friend of Wrox
 
Join Date: Apr 2004
Posts: 204
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry about the formatting. I though using those code tags would help it be more readable, but it kept everything on one line.

Aaron






Similar Threads
Thread Thread Starter Forum Replies Last Post
concurrency violation updating child table karenai VB Databases Basics 1 January 27th, 2008 02:29 AM
Concurrency Violation Exception Olorin ADO.NET 10 October 4th, 2007 12:21 PM
Concurrency Violation Problem - Please HELP! Aaron Edwards ASP.NET 2.0 Basics 0 March 25th, 2007 08:17 PM
Concurrency problem sanjaymannnet ASP.NET 1.0 and 1.1 Professional 0 July 10th, 2006 05:42 AM
Concurrency Violation performing Database updates LaFeverMF VB Databases Basics 1 February 15th, 2004 01:51 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.