Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 25th, 2007, 08:13 PM
Friend of Wrox
 
Join Date: Apr 2004
Location: San Francisco, CA, USA.
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

Reply With Quote
  #2 (permalink)  
Old March 25th, 2007, 08:15 PM
Friend of Wrox
 
Join Date: Apr 2004
Location: San Francisco, CA, USA.
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

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
concurrency violation updating child table karenai VB Databases Basics 1 January 27th, 2008 01: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 12:51 PM



All times are GMT -4. The time now is 12:24 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.