Question Adding rows to a DataGridView control using a strongly typed dataset

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!
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,475
Location
Sydney, Australia
Programming Experience
10+
For SQL Server, the SQL code to insert a new record and refresh the ID would look like this:
VB.NET:
INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2); SELECT ID = SCOPE_IDENTITY()
You can't do this for all databases. For example, the Jet OLE DB provider doesn't support multiple SQL statements per command, so you can't do it for Access. I'm not sure about SQL Server CE, but try editing the SQL code for the InsertCommand of your TableAdapter to something similar and you'll soon find out.
 

Munchester

New member
Joined
Oct 25, 2010
Messages
3
Programming Experience
5-10
Thank you for the response, however SQL CE does not support multiple SQL statements per command, so I would need to use two separate commands. Not a big deal but there is still the problem of where to put that code.

I'm using a strongly typed dataset generated by visual studio, and if I follow the definition of the Update command I see that it calls the Update command of the standard table adapter class, which calls the appropriate Insert, Update, or Delete command and returns the number of rows. How am I supposed to modify my dataset in a way that will report back the ID number(s) of any inserted row to the data table that was just updated?
 

Munchester

New member
Joined
Oct 25, 2010
Messages
3
Programming Experience
5-10
That's kind of what I figured I'd have to do, but thank you for confirming that. I still can't believe at how difficult it is to get a DataGridView to save data as it's entered without having to click a button, but it's something my users will have to deal with I guess.
 
Top Bottom