Bound DataGrid/ADO DataControl Sample

Hello,

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,
    MasterDate      datetime
);

CREATE TABLE Detail (
    DetailInt       int,
    DetailNumeric   numeric(8),
    DetailVarchar   varchar(8)  NOT NULL,
    DetailBit       bit,
CONSTRAINT
    FKDetail        PRIMARY KEY (DetailInt,DetailNumeric),
CONSTRAINT
    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.


Thanx.
AlveinAsked:
Who is Participating?
 
leonstrykerConnect With a Mentor Commented:
>>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). <snip>  However, this time I need to keep it simple, to ease further maintenance (not mine).<<

There is nothing simple about ADODC.  If you want to keep it simple, then do not use it.  Use ADO directly and bind the recordsets to the DataGrids.  Use the selection from the first grid as the filter value in the second.

Leon
0
 
AlveinAuthor Commented:
Thanx Leon for your reply. There's a big true here. I only use the ADO DataControls to keep the connections. Also, I don't use them to navigate or so. I could hide them and the behaviors would be the same. There's not much difference in to create and open the connection and recordsets manually. In fact, that's the first thing I did, with the same problems.

I think basicly those problems are related to DataGrids bounded usage.
0
 
leonstrykerCommented:
Alvein,

Is it possible to bind only the master recordset to the grid and query the detail as the master is navigated?  If the amount of data you bring in is not large it should not be a problem.

Leon
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
AlveinAuthor Commented:
What I'm currently doing is, the Master DataGrid gets bound in form Load. The Detail DataGrid gets bound (the query changes) everytime the the current row in Master DataGrid moves. The amount of data is small. If not, I would use unbound controls. ;)

That's what you're suggesting?

PS. I wrote "bounded" last time...lol
0
 
leonstrykerCommented:
When you say: " when I enter a column in the detail DataGrid", what exactly are you doing there?  Do you need to be able to access the data in the detail for editing?  How about making it not editable, but allow the seletion of rows. Then you can create some textboxes which will display the contents of the currently selected row of the detail form and edit/save the values in the text boxes.

Leon
0
 
AlveinAuthor Commented:
"When I enter a column": simple click on any column with data, so the DataGrid edit cursor lies there.

....

Yes, the idea is to have the grid editable. To make it not editable is a tiny step away from my usual approach of to include an unbound FlexGrid and issue action queries.

I've implemented the TextBoxes method before. The bad new is USERS don't like it. It's easier to code (that's, you control 100% the behaviors) but not suitable for real life.

Anyways, I see you're givin -alternatives- (thanx though). Not explanations or sample code. So should I understand that there's no an intuitive, fast and straight way to use editable bound DataGrids with a small amount of tricks and patches?

:(
0
 
leonstrykerCommented:
>>So should I understand that there's no an intuitive, fast and straight way to use editable bound DataGrids with a small amount of tricks and patches?<<

Unfortunently not.  I wish there were.  I would use them as well.  The truth of matter is that most controls beyond the basic ones (textbox, label, combobox, frame, etc) which come with VB6 are not really production quality and you need to start looking for third party controls that do what you want them to.

::((
0
 
AlveinAuthor Commented:
hi. What does that mean, a refund?
0
 
leonstrykerCommented:
No, it means I think I answered your question and deserve the points.  If you disagree please let me know why.  If not then please close the question, otherwise in 4 days a mod will close it.

Leon
0
 
AlveinAuthor Commented:
Leon,

Tell me sincerely, you think you deserve the max amount of points for tell me what I had already discovered ("this stuff is hard") ??
0
 
leonstrykerCommented:
Alvein,

That is for you to answer.  If you go to a store and see an item on sale for a $1 and you know it should cost $10, what do you do?

You always have the option of posting to the Community Support area and ask them to reduce teh value of the question (make sure to state your reasons and link to the question.)

Leon
0
 
AlveinAuthor Commented:
I requested either for sample source code or a solution for my remarked problems (that's not $1 anyways). I didn't get anything. You posted some comments. Ok. But does that mean everytime you ask for help and -only one- expert answers, you should accept his/her answer in the end?? think about it.
0
 
leonstrykerCommented:
On the other hand "Not possible" is a valid answer
0
 
AlveinAuthor Commented:
In such case, you could make a lot of points posting blah's (nothing personal, ok?) in empty threads.

I think you are taking advantage of your admin capabilities.
0
 
leonstrykerCommented:
In which case you should post to the Community Support area and bring it to their attention.
0
 
AlveinAuthor Commented:
Hello,

I understand very well that comment was only a recommendation, but you cannot ask the President to decide if the President should be reelected, right? I don't see fair enough that the "recommendator" and the (only) poster be the same person.

Anyways,

>"a) you can't with that criteria, or b) if the criteria are not negotiable, then you can't do it simply"

Not sure about the accurate analysis. I Don't understand....is there some missing word?
0
 
leonstrykerCommented:
I do not really care about the points here, so lets say we make this a:

PAQ/No Refund

leonstryker
0
 
AlveinAuthor Commented:
Why "No Refund"
0
 
leonstrykerCommented:
Because the question was answered.
0
 
AlveinAuthor Commented:
Sincerely, I think the fairest option is to give you some points and give me a refund for the rest.
0
 
leonstrykerCommented:
At this point I am going to let the Admin handle this.
0
 
leonstrykerCommented:
Sounds fine by me.
0
 
AlveinAuthor Commented:
Thank you Annie.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.