Bound DataGrid/ADO DataControl Sample
Posted on 2004-04-19
I use to be a make-it-yourself programmer, but this time I've decided to keep my brain and do it the easy way. I need of a sample of a master-detail table management and I'm using both DataGrid and ADO Data controls (well, two of each one). Usually I like to work with unbound controls and make table operations (add, delete, etc.) with pure SQL. However, this time I need to keep it simple, to ease further maintenance (not mine).
Ok, surprise. I'm having a hell with this "easy" approach. After I link the controls with the DB, and set the DataGrids to allow Add and Update, problems start to arise. For example,
At first run, when the DB is empty, if I edit some column and try to go to the next one, the error is "The current row is not available". My current trick to solve this is to use AddNew and then Delete after the Refresh in the form Load event. That makes available the %&$%@#! current row. :)
When the DB has data, once I hit F5, if the first DataGrid has the focus, the first column gets blank, and I have to hit ESC twice to restore the value. If I move to another row, the column gets updated or tries to update. My solution here was to make another control have the first TabIndex (how nasty!).
Cause the two DataGrids are linked, I have to change and refresh the "detail" Recordset everytime the "master" one moves to another record. This seems to be ok, but I've noted when I enter a column in the detail DataGrid, if I move to a distinct record in the master DataGrid, the contents of the column (detail) that has the edit cursor does not change, that's, some content of the previous detail are copied to the newer one (call guiness!).
Trick after trick I've ended with the disabling of the AllowAddNew and AllowDelete properties, to do it manually with Command Buttons (New, Save, Cancel, Delete). However, they are a doorway to more problems, or more problem-generator tricks.
The top of all this, it's that there are some errors that (apparently) cannot be catched, and some of them cause the DB enters to an invalid state requiring a restart. I cannot live with that.
Maybe I can get (trick-after-trick) a 100% working solution, but something tells me that I'm doing the things the wrong way. Shouln't be the use of bound controls easier than the use of unbound counterparts??
What I'm looking for is a sample of this case, a master-detail form, using two DataGrids (dgMaster, dgDetail) and two ADO DataControls (adodcMaster, adodcDetail). Preferably with no use of extra buttons (though if you think they makes your life easier, go ahead), and having the following database (I use Access):
CREATE TABLE Master (
MasterInt int PRIMARY KEY,
MasterChar char(8) NOT NULL,
CREATE TABLE Detail (
DetailVarchar varchar(8) NOT NULL,
FKDetail PRIMARY KEY (DetailInt,DetailNumeric),
FKDetailInt FOREIGN KEY (DetailInt) REFERENCES Master(MasterInt)
The fields have not been chosen based in some particular case. It's only the sample I've used in my tests.
One thing that it's quite important is to keep Locked (but visible) the foreign key in the detail DataGrid (auto-updating it just before to do the row update) to prevent integrity problems.
Not sure if my question means a lot of work. If you think I should continue with the tricks cause there's not perfect solution, so tell me. If not, post your code and I will test it. I hope the max points be worth for you. I'm not giving them for your time, but for your expertise.