Still having a problem using DataSets
-
Hi Everyone: I still have a nagging problem when it comes to using DataSets. When I add new records to a dataset that contains nothing but the table’s schema, the dataset starts the auto increment field primary key field off at zero instead of one. I’m using an article titled “How to update a database from a DataSet object using Visual Basic NET”, to help me understand how to use datasets for data manipulation. The article states, “To access primary key information, call FillSchema, and then set the MissingSchemaAction property of your DataAdapter to AddWithKey, or manually set the primary key in your code.” I have done this and here is my problem. When I add new records to an empty dataset, before I update the database the records look like this 0 Record 1 1 Record 2 2 Record 3 3 Record 4 As long as I don’t use the navigation buttons to move back to other records. I can save the DataSet to the database and it will look like this: 1 Record 1 2 Record 2 3 Record 3 4 Record 4 Now I’m going to show you where the monkey wrench comes into play. I load the dataset from the database. The dataset that is returned looks like this: 1 Record 1 2 Record 2 3 Record 3 4 Record 4 If I add a new record to the dataset like: 1 Record 1 2 Record 2 3 Record 3 4 Record 4 0 Record 5 I go back to another record to perform an edit and the dataset looks like this: 1 Record 1 2 Record 2 3 RECORD 3 4 0 Record 5 If I commit the changes made on the DataSet to the database, both addition and edited record. The database table changes to this: 1 Record 1 2 Record 2 3 RECORD 3 4 5 Record 5 I don't understand why this is occuring. The dataset is based on the table schema, so logically it should know the first column is the primary key column and the primary key column is auto incremented. It starts at one and increments by one every time we add a new record. To me it looks like the DataSet doesn't know that when I add a new record to the dataset to take the ID number of the last record and increment it by one to get the next primary key number. The DataSet also doesn't seem to append the new record at the end of the dataset. In other words advance to the end of the dataset, add the new record with the new ID number and so forth. Why is this happening and what can I do to fix it? Thanks, Quecumber256
-
Hi Everyone: I still have a nagging problem when it comes to using DataSets. When I add new records to a dataset that contains nothing but the table’s schema, the dataset starts the auto increment field primary key field off at zero instead of one. I’m using an article titled “How to update a database from a DataSet object using Visual Basic NET”, to help me understand how to use datasets for data manipulation. The article states, “To access primary key information, call FillSchema, and then set the MissingSchemaAction property of your DataAdapter to AddWithKey, or manually set the primary key in your code.” I have done this and here is my problem. When I add new records to an empty dataset, before I update the database the records look like this 0 Record 1 1 Record 2 2 Record 3 3 Record 4 As long as I don’t use the navigation buttons to move back to other records. I can save the DataSet to the database and it will look like this: 1 Record 1 2 Record 2 3 Record 3 4 Record 4 Now I’m going to show you where the monkey wrench comes into play. I load the dataset from the database. The dataset that is returned looks like this: 1 Record 1 2 Record 2 3 Record 3 4 Record 4 If I add a new record to the dataset like: 1 Record 1 2 Record 2 3 Record 3 4 Record 4 0 Record 5 I go back to another record to perform an edit and the dataset looks like this: 1 Record 1 2 Record 2 3 RECORD 3 4 0 Record 5 If I commit the changes made on the DataSet to the database, both addition and edited record. The database table changes to this: 1 Record 1 2 Record 2 3 RECORD 3 4 5 Record 5 I don't understand why this is occuring. The dataset is based on the table schema, so logically it should know the first column is the primary key column and the primary key column is auto incremented. It starts at one and increments by one every time we add a new record. To me it looks like the DataSet doesn't know that when I add a new record to the dataset to take the ID number of the last record and increment it by one to get the next primary key number. The DataSet also doesn't seem to append the new record at the end of the dataset. In other words advance to the end of the dataset, add the new record with the new ID number and so forth. Why is this happening and what can I do to fix it? Thanks, Quecumber256
There is nothing to fix. You just have to realise that there is a difference between the key values in the database, and the key values that the dataset uses to identify the records before they have been added to the database. The dataset could use a method to create key values that is more like the method that the database uses, but that would only mean that the values would end up the same more often, but not always. There is no telling what the key value will be in the database until you have actually added the record to the database. Also, the key field doesn't always increment by one. It can be set to start at any value and increment by any value, or even pick a random number.
--- single minded; short sighted; long gone;
-
There is nothing to fix. You just have to realise that there is a difference between the key values in the database, and the key values that the dataset uses to identify the records before they have been added to the database. The dataset could use a method to create key values that is more like the method that the database uses, but that would only mean that the values would end up the same more often, but not always. There is no telling what the key value will be in the database until you have actually added the record to the database. Also, the key field doesn't always increment by one. It can be set to start at any value and increment by any value, or even pick a random number.
--- single minded; short sighted; long gone;
I'm binding textbox controls on a form to a dataset. So what you are telling me is; I can only do one operation at a time using a dataset. If the database table is empty, then the dataset generated for this table can only have records added. If I genenerate a dataset from a table that contains existing records I can only do one operation; either edit records or add records. I can't add new records and then use the navigation buttons on the BindingNavigator control to go to other records that need to be edited. If this is the nature of the ADO.NET methodology then I'll have to disable the navigation buttons when the user selects add new records, and the reverse of this is to disable the ability to add records when in edit mode. Am I right is my assumption? Thanks Quecumber256
-
I'm binding textbox controls on a form to a dataset. So what you are telling me is; I can only do one operation at a time using a dataset. If the database table is empty, then the dataset generated for this table can only have records added. If I genenerate a dataset from a table that contains existing records I can only do one operation; either edit records or add records. I can't add new records and then use the navigation buttons on the BindingNavigator control to go to other records that need to be edited. If this is the nature of the ADO.NET methodology then I'll have to disable the navigation buttons when the user selects add new records, and the reverse of this is to disable the ability to add records when in edit mode. Am I right is my assumption? Thanks Quecumber256
Quecumber256 wrote:
So what you are telling me is; I can only do one operation at a time using a dataset.
No. That's no what he said. A DataSet, or DataTable, can hanve multiple operations at the same time. But, when records are added to the DataSet, the PK fields will get temporary values since the dataSet has no way of knowing what the next available key value should be. When the DataSet is finally written back to the database, the database will assign a new PK value, not the DataSet.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Quecumber256 wrote:
So what you are telling me is; I can only do one operation at a time using a dataset.
No. That's no what he said. A DataSet, or DataTable, can hanve multiple operations at the same time. But, when records are added to the DataSet, the PK fields will get temporary values since the dataSet has no way of knowing what the next available key value should be. When the DataSet is finally written back to the database, the database will assign a new PK value, not the DataSet.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007I think I have found the glitch. When a user is moving through the dataset the current position pointer is at the record the user is editing. If the user chooses to add a new record it will add a new record at the current position; underneath the record that was just edited. Here is a sample of what happened when I edited and added records in one operation and then updated the dataset. TableName: tblBindings 7 TST2 7 2nd Test Record Added to Existing DS 11 14 12 TST7 12 Record 7 13 TST8 13 Record 8 14 TST9 14 Record 9 15 TST2 7 2nd Test Record Edited to Existing DS Is there a way to force the record pointer in the dataset to go to the end of the dataset, and after it is at the end of the dataset then append the new record onto the dataset? Thanks, Quecumber256
-
I think I have found the glitch. When a user is moving through the dataset the current position pointer is at the record the user is editing. If the user chooses to add a new record it will add a new record at the current position; underneath the record that was just edited. Here is a sample of what happened when I edited and added records in one operation and then updated the dataset. TableName: tblBindings 7 TST2 7 2nd Test Record Added to Existing DS 11 14 12 TST7 12 Record 7 13 TST8 13 Record 8 14 TST9 14 Record 9 15 TST2 7 2nd Test Record Edited to Existing DS Is there a way to force the record pointer in the dataset to go to the end of the dataset, and after it is at the end of the dataset then append the new record onto the dataset? Thanks, Quecumber256
No. The DataSet, nor any database, doesn't care about the order of records. Sorting is best left up to the UI, not the data layer.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
No. The DataSet, nor any database, doesn't care about the order of records. Sorting is best left up to the UI, not the data layer.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007On the UI form I bound the four textboxes to the four columns in the dataset. txtID is bound to RecID, txtOrdinal is bound to column Ordinal, txtCode is bound to column Code, and txtName is bound to column Name. This way when I navigate through the dataset I know which record I'm looking at. I read an artical this morning about setting the DataSet Autoincrement to -1 and 0. This is suppose to insure that the ID#'s differ between records added and records that were retrieve from the database. If I add new records using a negitive auto increment and records that are read into the dataset from a database will have positive increments it should take care of my overwrite and record disjunction problems. Thanks, Quecumber256