Results 1 to 9 of 9

Thread: [RESOLVED] DataAdapter.Update command for junction table w/o an AutoNumber field

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Resolved [RESOLVED] DataAdapter.Update command for junction table w/o an AutoNumber field

    I need to update a junction table (many-to-many relationship) in a MS Access database.

    The junction table does not have an Autonumber (Primary Key) field. It only has 2 fields:

    VendorTypeID
    VendorID

    I need to update rows in the junction table for a given VendorTypeID, changing the VendorTypeID to a different value.

    Here's how I can do this using an OleDbCommand command:

    Code:
    If con.State <> ConnectionState.Open Then con.Open()
    For Each row In rows
        strSQL = "UPDATE [VendorMember] SET VendorTypeID = " & lngFutureTypeID & " WHERE VendorTypeID = " & lngTypeID
        Using cmd As New OleDbCommand(strSQL, con)
            cmd.ExecuteNonQuery()
            intCount += 1
        End Using
    Next row
    con.Close()
    I don't know how to do this by calling the DataAdpter.Update method since the junction table has no Autonumber field.

    For example:

    Code:
    Dim updateData As New OleDb.OleDbCommand("UPDATE [VendorMember]” &
    “SET VendorTypeID = @VendorNewTypeID " &
    "WHERE VendorTypeID = @VendorOldTypeID", con)
    
    daVendorMember.UpdateCommand = updateData
    What would be the right daVendorMember.UpdateCommand for this situation?
    Last edited by Mark@SF; Apr 29th, 2021 at 05:30 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DataAdapter.Update command for junction table w/o an AutoNumber field

    I think that you are under some misconceptions about how a data adapter works. The point of a data adapter is to populate a DataTable from a database and/or save changes from a DataTable to a database. If you want to use a data adapter then you should be retrieving the existing data from the database into a DataTable, making the appropriate change to the data in the DataTable in your application and then using the data adapter to save those changes back to the database. The data adapter only cares about what's in the DataTable.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: DataAdapter.Update command for junction table w/o an AutoNumber field

    jmc -

    I think that you are under some misconceptions about how a data adapter works.
    No doubt, you're right

    My scenario:
    1. Select three rows from dsMain.Table("VendorMember").
    2. Change the VendorTypeID in each of the three rows from "3" to "5".
    3. Call the daVendorMember.Update method and pass it the three rows.

    Here is my code:

    Code:
    For Each row As DataRow In rows
        row.BeginEdit()
        row("VendorTypeID") = lngFutureTypeID
        row.EndEdit()
    Next row
    
    intCount = daVendorMember.Update(rows) '...implicitly calls dsMain.Tables("VendorMember").AcceptChanges()
    Here are the three rows (pre-edit):

    Code:
    	3, 10
    	3, 42
    	3, 117
    Here are the three rows (post-edit):

    Code:
    	5, 10
    	5, 42
    	5, 117
    So far, this is what I expect. The problem occurs when I call the daVendorMember.Update method.

    When I call the Update method on the three changed rows, the junction table in the MS Access database gets updated, but all 92 rows of the junction table are updated and every record in the junction table now has the same VendorTypeID and VendorID values. These values are the edited values from the 3rd row of the three selected rows (5, 117).

    The daVendorMember.Update.CommandText:

    Code:
    Dim updateData As New OleDb.OleDbCommand("UPDATE [VendorMember] SET VendorTypeID = @VendorTypeID, VendorID = @VendorID", con)
    This the first time I've used a junction table. It does not have an autonumber primary key field but it does have a unique constraint:

    Code:
    dsMain.Tables.Add("VendorMember")
    dsMain.Tables("VendorMember").Columns.Add("VendorTypeID").DataType = System.Type.GetType("System.Int32")
    dsMain.Tables("VendorMember").Columns.Add("VendorID").DataType = System.Type.GetType("System.Int32")
    
    With dsMain.Tables("VendorMember")
        keys(0) = .Columns("VendorTypeID")
        keys(1) = .Columns("VendorID")
        .PrimaryKey = keys
    End With
    Code:
    '...VendorTypeID and VendorID must be unique within VendorMember table
    dsMain.Tables("VendorMember").Constraints.Clear()
    ReDim dataColumns(1)
    dataColumns(0) = dsMain.Tables("VendorMember").Columns("VendorTypeID")
    dataColumns(1) = dsMain.Tables("VendorMember").Columns("VendorID")
    myUniqueConstraint = New UniqueConstraint(dataColumns)
    dsMain.Tables("VendorMember").Constraints.Add(myUniqueConstraint)
    In other MS Access database tables that have an autonumber primary key field, I use a DataAdapter.Update.CommandText that has a "WHERE" clause that includes the table's primary key field.

    I think my problem is that the daVendorMember.Update.CommandText does not have a "WHERE" clause so even though I am only passing it three rows, it performs the update on every row of the junction table. It seems to be doing this for each of the three rows (Update method is called three times) which is probably why after the Update method completes all rows in the junction table are updated to the same values as the 3rd (final) passed row.

    What surprised me was that even though I only passed three rows to the daVendorMember.Update method, the update occurred across all rows in the MS Access database table. I thought that the Update method was only scoped for the passed rows.

    How can I fix this issue? Change the structure of the MS Access database's junction table to include an autonumber primary key? Change my daVendorMember.Update.CommandText? I would like to avoid using an OleDbCommand like I show in my original post to this thread.

    Thank you for your help with this question.
    Last edited by Mark@SF; Apr 29th, 2021 at 05:35 AM.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: DataAdapter.Update command for junction table w/o an AutoNumber field

    Quote Originally Posted by Mark@SF View Post
    I think my problem is that the daVendorMember.Update.CommandText does not have a "WHERE" clause so even though I am only passing it three rows, it performs the update on every row of the junction table.
    That's exactly the problem. You didn't provide it a way to uniquely identify each row. So what you need is the OLD Vendor Type ID, the NEW Vendor Type ID, and the Vendor ID...

    Code:
    UPDATE [VendorMember] SET VendorTypeID = @VendorTypeID, VendorID = @VendorID WHERE VendorTypeID = @OldVendorTypeId and VendorID = @VendorId
    How you manage all that... I'm not sure.

    OR... you add an autonumber/identity PKey to the table. At that point, you could also use CommandBuilder to automatically create the update command for you. Actually, if you mark your two fields as a PKey, you might be able to do that now, and not even worry about building the SQL yourself, let the builder take care of it for you.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DataAdapter.Update command for junction table w/o an AutoNumber field

    First things first, let's clean up some of that code. Here:
    Code:
    For Each row As DataRow In rows
        row.BeginEdit()
        row("VendorTypeID") = lngFutureTypeID
        row.EndEdit()
    Next row
    
    intCount = daVendorMember.Update(rows) '...implicitly calls dsMain.Tables("VendorMember").AcceptChanges()
    the calls to BeginUpdate and EndUpdate are pointless. This:
    Code:
    dsMain.Tables.Add("VendorMember")
    dsMain.Tables("VendorMember").Columns.Add("VendorTypeID").DataType = System.Type.GetType("System.Int32")
    dsMain.Tables("VendorMember").Columns.Add("VendorID").DataType = System.Type.GetType("System.Int32")
    
    With dsMain.Tables("VendorMember")
        keys(0) = .Columns("VendorTypeID")
        keys(1) = .Columns("VendorID")
        .PrimaryKey = keys
    End With
    can get an overhaul:
    vb.net Code:
    1. Dim vendorMemberTable = dsMain.Tables.Add("VendorMember")
    2.  
    3. With vendorMemberTable.Columns
    4.     vendorMemberTable.PrimaryKey = {.Add("VendorTypeID", GetType(Integer)), .Add("VendorID", GetType(Integer))}
    5. End With
    As for the problem, I did misunderstand a bit but tg has the right of it. Using different versions of the same columns via parameters is easy:
    vb.net Code:
    1. Dim updateCommand As New OleDbCommand
    2.  
    3. updateCommand.CommandText = "UPDATE [VendorMember] SET VendorTypeID = @VendorTypeID, VendorID = @VendorID WHERE VendorTypeID = @OriginalVendorTypeId AND VendorID = @OriginalVendorId"
    4.  
    5. With updateCommand.Parameters
    6.     .Add("@VendorTypeID", OleDbType.Integer, 0, "VendorTypeID")
    7.     .Add("@VendorID", OleDbType.Integer, 0, "VendorID")
    8.     .Add("@OriginalVendorTypeId", OleDbType.Integer, 0, "VendorTypeID").SourceVersion = DataRowVersion.Original
    9.     .Add("@OriginalVendorId", OleDbType.Integer, 0, "VendorID").SourceVersion = DataRowVersion.Original
    10. End With

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: DataAdapter.Update command for junction table w/o an AutoNumber field

    techgnome -

    Thanks for your help with this issue.

    I was able to create a DataAdapter that works for this situation:

    Code:
    strSQL = "UPDATE [VendorMember] SET VendorTypeID = @VendorNewTypeID WHERE VendorTypeID = @VendorOldTypeID"
    Dim updateData As New OleDb.OleDbCommand(strSQL, con)
    updateData.Parameters.Add("@VendorNewTypeID", OleDb.OleDbType.BigInt).Value = lngFutureTypeID
    updateData.Parameters.Add("@VendorOldTypeID", OleDb.OleDbType.BigInt).Value = lngTypeID
    da.UpdateCommand = updateData
    When the da.Update method is called, it raises a DBConcurrencyException error.

    After some more "head scratching", I think this is because in this case (no "WHERE" clause in the da.UpdateCommand.CommandText) it looks like the da.Update method is implicitly called for each row that is passed to it. On the first pass any rows that meet the selection criteria are updated and then on all subsequent passes the selection criteria has no matches (because the first pass already updated all rows in the passed set of rows). Therefore, on subsequent passes a DBConcurrencyException is raised because no rows match the criteria. I set the ContinueUpdateOnError property = True to continuing the updates on this exception.

    Code:
    da.ContinueUpdateOnError = True
    intCount = da.Update(rows) '...implicitly calls dsMain.Tables("VendorMember").AcceptChanges()
    da.ContinueUpdateOnError = False
    Also, the because the DataAdapter.Update method is implicitly called for each row that is passed to it, the value of intCount returned by the method will be for last pass of the method. Even though I know that three rows were updated, intCount =1 (not 3).

    I am not sure that I fully understand how the Update method of a DataAdapter works, but my code is now working. Having said that, I don't really like having to set the DataAdapter.ContinueUpdateOnError to True and ignoring errors.

    If anything that I've stated here regarding how the DataAdapter.Update method works is not correct, please let me know.
    Last edited by Mark@SF; Apr 29th, 2021 at 07:54 AM.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: DataAdapter.Update command for junction table w/o an AutoNumber field

    jmc -

    Thanks for your help. I was typing when your reply arrived. My reply to techgnome partially addresses some of your comments. I'll study your suggestions and reply in a bit.

    I really appreciate that you took additional time to provide some suggestions for streamlining my code.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DataAdapter.Update command for junction table w/o an AutoNumber field

    Quote Originally Posted by Mark@SF View Post
    I really appreciate that you took additional time to provide some suggestions for streamlining my code.
    Always happy to be critical of others.

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: DataAdapter.Update command for junction table w/o an AutoNumber field

    jmc -

    Thanks for your help. I modified my code and now it runs without raising the DBConcurrencyException error.

    Code:
    strSQL = "UPDATE [VendorMember] SET VendorTypeID = @NewVendorTypeID " &
        "WHERE VendorTypeID = @OldVendorTypeID AND VendorID = @VendorID"
    
    Dim updateData As New OleDb.OleDbCommand(strSQL, con)
    
    With updateData.Parameters
        .Add("@NewVendorTypeID", OleDb.OleDbType.BigInt).Value = lngFutureTypeID
        .Add("@OldVendorTypeID", OleDb.OleDbType.BigInt).Value = lngTypeID
        .Add("@VendorID", OleDb.OleDbType.BigInt).SourceVersion = DataRowVersion.Original
        .Item("@VendorID").SourceColumn = "VendorID"
    End With
    
    da.UpdateCommand = updateData
    
    For Each row As DataRow In rows
        row("VendorTypeID") = lngFutureTypeID
    Next row
    
    intCount = da.Update(rows) '...implicitly calls dsMain.Tables("VendorMember").AcceptChanges()
    The intCount value that is returned from the da.Update method call is "3" which is the expected value.

    Thanks again for your help!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width