I am using non-null, autoincrementing unsigned integers as my primary key in MySQL 4.11 (NT), the column name is 'id'. I am creating strongly-typed datasets by dragging and configuring a connection, commands, and dataadapters (including building the insert, update and delete commands with the Command generator tab of the dataadapter configuration) onto my form, then selecting "Generate dataset" with multiple dataadapters selected. All of the commands have a where clause "where .deleted = 0" so that I can soft-delete rows in code and reinstate them later if needed. This bit seems to work OK.
When I add rows to the table, I am creating new rows in code with something like:
Code: Select all
Dim drNew as TypedDataSet.tablenameRow
Dim drNewArray(0) as TypedDataSet.tablenameRow
drNew = ds.NewtablenameRow
with drNew
.column1=value1
.column2=value2
...
.columnX=valueX
End With
ds.tablename.AddtablenameRow(drNew)
drNewArray(0)=drNew
daTablename.Update(drNewArray)
When I do this with MySQLDirect.NET, I'm getting *strange* key column values. For example, the last several times I've been consistently getting 36, 37, 38 etc as the key values in one table and 53, 54, 55 in another, even though these already exist in the target table. Sometimes the first generated ID is missing in the sequence (hard-deleted row), but subsequent ones are, and if I manually add a row and fix the ID to the one first auto-generated, it still comes back with the same ID on the next run.
When I was doing this with MySQL Connector/.NET, I was following the same procedure except for explicitly setting id=0 along with the data values in the column, and Connector/.NET was writing the correct id value when it did the update. If I try the same thing here, the row in the dataset has an ID of zero even though the row in MySQL has a server-assigned ID.
Am I doing something crazy here, or is there a problem with generating unique IDs when adding rows?
I can't use negative autoincrementing IDs in the dataset, as I need the IDs to be valid in the table for the related rows in other tables that will all be commited or cancelled in one operation. I can't define all the relationships in the typed dataset, as some of the tables are lookups that use used from multiple columns in the parent table.
Any suggestions?