Hi,

First off, sorry about the cross-post, if anyone noticed it. I posted this in ADO.NET as well (although it looks a lot better over here, cause I didn't use those code tags).

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".

'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...
     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...
        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...
    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