Solved

Bound DataGrid/ADO DataControl Sample

Posted on 2004-04-19
27
805 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 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

11 Experts available now in Live!

Get 1:1 Help Now