Board index Database

ADO Newbie: How to code a one to many relationship form with ADODC controls

ADO Newbie: How to code a one to many relationship form with ADODC controls

Post by Lonnie VanZand » Sun, 02 Mar 2003 13:58:27



I'm attempting to implement a rather simple form in VB6 with a one to many
relationship between Table A and Table B (e.g. Customers and Orders). The
backend DB is Access 2002 and the connection provider is Jet 4. I have an
ADODC to navigate through Customers and fill in corresponding data-bound
textboxes. This works fine. I have a DataGrid with a second ADODC that uses
a separate connection and a command RecordSource of "Select * from Orders
where customerid = '" & CustomerIdTextbox.Text & "'". I reset the
RecordSource of the Orders ADODC within the MoveComplete handler of the
CustomerADODC. This works fine.

But, if a Customer has no Orders, then the OrdersADODC won't allow me to add
new Orders records from within the DataGrid. What additional event handlers
need to be written and which default ADODC properties need to be changed?

Forgive me my ignorance - I've studied the included online help and some
Internet faq material but haven't yet stumbled over an answer to this
seemingly common pattern...

Oh, I don't believe I want a FlexGrid because the Customer information isn't
presented in table format. But, maybe I'm wrong. Perhaps the FlexGrid
control has the logic to handle multi-table recordsets even if one isn't
presenting everything in grids...

Thank you,
Lonnie VanZandt

 
 
 
Top

ADO Newbie: How to code a one to many relationship form with ADODC controls

Post by Lonnie VanZand » Sun, 02 Mar 2003 14:54:53


Perhaps the Data Environment and the Data Shape command can be of
assistance? I have some reading to do. If anyone has an existing example of
how to do this, please forward it to me...


Quote:> I'm attempting to implement a rather simple form in VB6 with a one to many
> relationship between Table A and Table B (e.g. Customers and Orders). The
> backend DB is Access 2002 and the connection provider is Jet 4. I have an
> ADODC to navigate through Customers and fill in corresponding data-bound
> textboxes. This works fine. I have a DataGrid with a second ADODC that
uses
> a separate connection and a command RecordSource of "Select * from Orders
> where customerid = '" & CustomerIdTextbox.Text & "'". I reset the
> RecordSource of the Orders ADODC within the MoveComplete handler of the
> CustomerADODC. This works fine.

> But, if a Customer has no Orders, then the OrdersADODC won't allow me to
add
> new Orders records from within the DataGrid. What additional event
handlers
> need to be written and which default ADODC properties need to be changed?

> Forgive me my ignorance - I've studied the included online help and some
> Internet faq material but haven't yet stumbled over an answer to this
> seemingly common pattern...

> Oh, I don't believe I want a FlexGrid because the Customer information
isn't
> presented in table format. But, maybe I'm wrong. Perhaps the FlexGrid
> control has the logic to handle multi-table recordsets even if one isn't
> presenting everything in grids...

> Thank you,
> Lonnie VanZandt


 
 
 
Top

ADO Newbie: How to code a one to many relationship form with ADODC controls

Post by Lonnie VanZand » Sun, 02 Mar 2003 15:25:25


Now its starting to feel a bit more like Delphi (5 years ago or so, sigh.)

Apparently, one can use the Data Environment to construct the hierarchical
recordset and then drag and drop the Recordsets onto a blank form. VB6 then
creates text fields for the parent and a flex grid for the children. It
takes some more magic to get updateable recordsets - but this is heading in
the right direction. Goodbye ADODC apparently...

Lonnie.


> Perhaps the Data Environment and the Data Shape command can be of
> assistance? I have some reading to do. If anyone has an existing example
of
> how to do this, please forward it to me...



> > I'm attempting to implement a rather simple form in VB6 with a one to
many
> > relationship between Table A and Table B (e.g. Customers and Orders).
The
> > backend DB is Access 2002 and the connection provider is Jet 4. I have
an
> > ADODC to navigate through Customers and fill in corresponding data-bound
> > textboxes. This works fine. I have a DataGrid with a second ADODC that
> uses
> > a separate connection and a command RecordSource of "Select * from
Orders
> > where customerid = '" & CustomerIdTextbox.Text & "'". I reset the
> > RecordSource of the Orders ADODC within the MoveComplete handler of the
> > CustomerADODC. This works fine.

> > But, if a Customer has no Orders, then the OrdersADODC won't allow me to
> add
> > new Orders records from within the DataGrid. What additional event
> handlers
> > need to be written and which default ADODC properties need to be
changed?

> > Forgive me my ignorance - I've studied the included online help and some
> > Internet faq material but haven't yet stumbled over an answer to this
> > seemingly common pattern...

> > Oh, I don't believe I want a FlexGrid because the Customer information
> isn't
> > presented in table format. But, maybe I'm wrong. Perhaps the FlexGrid
> > control has the logic to handle multi-table recordsets even if one isn't
> > presenting everything in grids...

> > Thank you,
> > Lonnie VanZandt

 
 
 
Top

ADO Newbie: How to code a one to many relationship form with ADODC controls

Post by <SteveT> » Mon, 03 Mar 2003 00:03:07


Would something like this do for the DataGrid Recordsource?

"SELECT  *
FROM tblOrders LEFT JOIN tblOrders ON tblCustomer.ID = tblOrders.ID
WHERE tblCustomer.ID = " & CustomerIdTextbox.Text

Your ID is a number, yes?

I'm just testing in an Access query but seems to give the results you were
looking for, an updateable
recordset even if no records exist in tblOrders.

Steve


> Now its starting to feel a bit more like Delphi (5 years ago or so, sigh.)

> Apparently, one can use the Data Environment to construct the hierarchical
> recordset and then drag and drop the Recordsets onto a blank form. VB6
then
> creates text fields for the parent and a flex grid for the children. It
> takes some more magic to get updateable recordsets - but this is heading
in
> the right direction. Goodbye ADODC apparently...

> Lonnie.



> > Perhaps the Data Environment and the Data Shape command can be of
> > assistance? I have some reading to do. If anyone has an existing example
> of
> > how to do this, please forward it to me...



> > > I'm attempting to implement a rather simple form in VB6 with a one to
> many
> > > relationship between Table A and Table B (e.g. Customers and Orders).
> The
> > > backend DB is Access 2002 and the connection provider is Jet 4. I have
> an
> > > ADODC to navigate through Customers and fill in corresponding
data-bound
> > > textboxes. This works fine. I have a DataGrid with a second ADODC that
> > uses
> > > a separate connection and a command RecordSource of "Select * from
> Orders
> > > where customerid = '" & CustomerIdTextbox.Text & "'". I reset the
> > > RecordSource of the Orders ADODC within the MoveComplete handler of
the
> > > CustomerADODC. This works fine.

> > > But, if a Customer has no Orders, then the OrdersADODC won't allow me
to
> > add
> > > new Orders records from within the DataGrid. What additional event
> > handlers
> > > need to be written and which default ADODC properties need to be
> changed?

> > > Forgive me my ignorance - I've studied the included online help and
some
> > > Internet faq material but haven't yet stumbled over an answer to this
> > > seemingly common pattern...

> > > Oh, I don't believe I want a FlexGrid because the Customer information
> > isn't
> > > presented in table format. But, maybe I'm wrong. Perhaps the FlexGrid
> > > control has the logic to handle multi-table recordsets even if one
isn't
> > > presenting everything in grids...

> > > Thank you,
> > > Lonnie VanZandt

 
 
 
Top

1. ADODC control in a ADO Coded form

Greetings,

I have a form (generated ADO code using the Wizard) to work with a table
in ACCESS.

I needed to add another table (for lookup reference) to this form, since
I didn't want to mess with the generated code/set procedures, I added a
ADO control.  This is where it becomes difficult.  The control is to
look out to another table and retrieve the "codes" the user is allowed
based on the category the user selects.

In example:
There are two combo boxes, in the first combo box are the hard coded
CATEGORIES that the user selects from.  After selecting one of the
Categories the user then goes to the second combo box and selects the
CODE from the list of available Codes in the Category.

This is the problem:
The SQL to retrieve the "Codes" is dependant on the String value that
contains the Category.  The SQL code is typed into the Property Page,
obtained by clicking the Row Source Item of the ADODC, does not deal
with quotes hence, can not obtain a string value.  The Row Source is
needed to tell the ADODC where to obtain it's information from.

If I haven't made this too vague or complex, any help is greatly
appreciated

Mike

2. Newbie:Question about binding a program

3. Bind an ado recordset directly to an ado data control (adodc)

4. Ingres II name server hang

5. ADO code generated recordset and binding to form controls

6. Automating transaction log monitoring and dumping

7. How to use ADO to create one to one relationship between two tables

8. Help!!!! questions in VB4.0

9. ADODC controls with only one connection ?

10. cannot initialize adodc control from the code

11. How to put a Form parameter in Adodc Control Record Source property

12. VB (ADO or ADODC) which one is better ?

13. newbie question - setting relationships with manual code


All times are UTC
Board index