-
Apr 28th, 2021, 08:20 PM
#1
Thread Starter
Hyperactive Member
[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.
-
Apr 28th, 2021, 10:23 PM
#2
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.
-
Apr 29th, 2021, 05:03 AM
#3
Thread Starter
Hyperactive Member
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):
Here are the three rows (post-edit):
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.
-
Apr 29th, 2021, 07:18 AM
#4
Re: DataAdapter.Update command for junction table w/o an AutoNumber field
Originally Posted by Mark@SF
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
-
Apr 29th, 2021, 07:46 AM
#5
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:
Dim vendorMemberTable = dsMain.Tables.Add("VendorMember")
With vendorMemberTable.Columns
vendorMemberTable.PrimaryKey = {.Add("VendorTypeID", GetType(Integer)), .Add("VendorID", GetType(Integer))}
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:
Dim updateCommand As New OleDbCommand
updateCommand.CommandText = "UPDATE [VendorMember] SET VendorTypeID = @VendorTypeID, VendorID = @VendorID WHERE VendorTypeID = @OriginalVendorTypeId AND VendorID = @OriginalVendorId"
With updateCommand.Parameters
.Add("@VendorTypeID", OleDbType.Integer, 0, "VendorTypeID")
.Add("@VendorID", OleDbType.Integer, 0, "VendorID")
.Add("@OriginalVendorTypeId", OleDbType.Integer, 0, "VendorTypeID").SourceVersion = DataRowVersion.Original
.Add("@OriginalVendorId", OleDbType.Integer, 0, "VendorID").SourceVersion = DataRowVersion.Original
End With
Last edited by jmcilhinney; Apr 29th, 2021 at 07:54 AM.
-
Apr 29th, 2021, 07:49 AM
#6
Thread Starter
Hyperactive Member
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.
-
Apr 29th, 2021, 07:58 AM
#7
Thread Starter
Hyperactive Member
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.
-
Apr 29th, 2021, 08:26 AM
#8
Re: DataAdapter.Update command for junction table w/o an AutoNumber field
Originally Posted by Mark@SF
I really appreciate that you took additional time to provide some suggestions for streamlining my code.
Always happy to be critical of others.
-
Apr 29th, 2021, 08:56 AM
#9
Thread Starter
Hyperactive Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|