Solved

Bound DataGrid/ADO DataControl Sample

Posted on 2004-04-19
27
811 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:Alvein
  • 12
  • 11
27 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 200 total points
ID: 10868015
>>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
 

Author Comment

by:Alvein
ID: 10869269
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 10870595
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
 

Author Comment

by:Alvein
ID: 10870799
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 10870858
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
 

Author Comment

by:Alvein
ID: 10873698
"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
 
LVL 29

Expert Comment

by:leonstryker
ID: 10873747
>>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
 

Author Comment

by:Alvein
ID: 11338464
hi. What does that mean, a refund?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11338547
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
 

Author Comment

by:Alvein
ID: 11338887
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 11339016
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Alvein
ID: 11339333
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 11339772
On the other hand "Not possible" is a valid answer
0
 

Author Comment

by:Alvein
ID: 11339842
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 11339896
In which case you should post to the Community Support area and bring it to their attention.
0
 

Author Comment

by:Alvein
ID: 11345303
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 11345341
I do not really care about the points here, so lets say we make this a:

PAQ/No Refund

leonstryker
0
 

Author Comment

by:Alvein
ID: 11346897
Why "No Refund"
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11346920
Because the question was answered.
0
 

Author Comment

by:Alvein
ID: 11347250
Sincerely, I think the fairest option is to give you some points and give me a refund for the rest.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11347315
At this point I am going to let the Admin handle this.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11347540
Sounds fine by me.
0
 

Author Comment

by:Alvein
ID: 11763774
Thank you Annie.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now