Munchester
New member
- Joined
- Oct 25, 2010
- Messages
- 3
- Programming Experience
- 5-10
I've been trying to solve this problem for about a week with no success, and it is getting very frustrating.
I have an SQL CE database with a strongly typed dataset. Each table has a primary key "ID" which is an auto-increment bigint field. The dataset is set to be the data source for my DataGridView, and I'm able to add / remove / update rows and have the changes saved back to the database.
The problem is that if I add a new row and then edit it again, I get a DBConcurrencyException error when I try to call Update on the dataset. A quick look at the DataGridView and/or the dataset explains why: new records have -1, -2, etc. for the ID instead of the value generated by the database. Unfortunately, I can't figure out for the life of me how to fix this!
I've read that selecting "Refresh the data table" under advanced options for the dataset will fix this, but that option is grayed out in SQL CE. I've also tried using "SELECT @@IDENTITY" to update the ID after a new row has been created but frankly I'm not sure where to add code for that when using a strongly typed dataset.
I've even tried re-inventing the wheel and using a series of SQL commands instead of using a dataset at all, but that is a big wheel for me for me to re-invent.
I'm really surprised I'm having this much trouble with this seemingly simple task!
I can only get it to work if after Update is called I re-populate the dataset (fill and set datasource), and only then when called from a button. If I try the same code in any event (tried RowLeave, CellEndEdit, and about a dozen others) then I get an InvalidOperationException with the text "Operation is not valid because it results in a reentrant call to the SetCurrentCellAddressCore function." Not a surprise since most of these events are raised when binding a datasource, making an infinite loop.
All I want is for changes to be saved to the SQL CE database immediately, which I realize would be horrible design if connecting to a remote database on the network but this is a local SQL CE database with only one user. That user would be doing a lot of data entry and it would be a pain to get halfway through it and have something crash and lose everything, or to constantly interrupt the work by having to click save. I'm also open to suggestions on a better way to accomplish this.
Any feedback would be greatly appreciated!
I have an SQL CE database with a strongly typed dataset. Each table has a primary key "ID" which is an auto-increment bigint field. The dataset is set to be the data source for my DataGridView, and I'm able to add / remove / update rows and have the changes saved back to the database.
The problem is that if I add a new row and then edit it again, I get a DBConcurrencyException error when I try to call Update on the dataset. A quick look at the DataGridView and/or the dataset explains why: new records have -1, -2, etc. for the ID instead of the value generated by the database. Unfortunately, I can't figure out for the life of me how to fix this!
I've read that selecting "Refresh the data table" under advanced options for the dataset will fix this, but that option is grayed out in SQL CE. I've also tried using "SELECT @@IDENTITY" to update the ID after a new row has been created but frankly I'm not sure where to add code for that when using a strongly typed dataset.
I've even tried re-inventing the wheel and using a series of SQL commands instead of using a dataset at all, but that is a big wheel for me for me to re-invent.
I'm really surprised I'm having this much trouble with this seemingly simple task!
I can only get it to work if after Update is called I re-populate the dataset (fill and set datasource), and only then when called from a button. If I try the same code in any event (tried RowLeave, CellEndEdit, and about a dozen others) then I get an InvalidOperationException with the text "Operation is not valid because it results in a reentrant call to the SetCurrentCellAddressCore function." Not a surprise since most of these events are raised when binding a datasource, making an infinite loop.
All I want is for changes to be saved to the SQL CE database immediately, which I realize would be horrible design if connecting to a remote database on the network but this is a local SQL CE database with only one user. That user would be doing a lot of data entry and it would be a pain to get halfway through it and have something crash and lose everything, or to constantly interrupt the work by having to click save. I'm also open to suggestions on a better way to accomplish this.
Any feedback would be greatly appreciated!