Q: SQL

[Geoff Jones]

Hiya

Can anybody help me with the following?

I have an Access Database. There are two tables in it. tblCustomers and
tblOrders.

tblCustomers:

Customer_ID
Customer Address

tblOrders

Order_ID
Customer_FK
Product Ordered

There is a one to many relationship between the two tables.

How can I generate the orders that a given customer has made? I do not wish
to modifty the database in any way. All that I need to do is generate the
results as another table.

I know how to do this using DataTables, a DataSet and Relationships.
HOWEVER, I'm wondering if there is a QUICK way to do this using an SQL
command; ideally without creating DataAdaptors.

I'd be most grateful for any help and example code.

Many thanks in advance

Geoff

[Bob Powell [MVP]]

The minimum you need is a connection. Drag the database from the server
explorer onto the form and a connection will be made. This should be an
OleDbConnection using the Jet 4.0 driver. Create an OleDbCommand object in
code and write the SQL by hand OR use the Access query wizard to design the
query and drag that from the stored procedures section in the database
explorer onto the form. a command will be created.

To be honest, creating a data adapter and hence a dataset is probably
quicker in the end because you can do all of the databinding etc from the
IDE. Using the method described above you still have to open the database
connection, execute the command, read the records from a datareader and use
the rows individually.

--
Bob Powell [MVP]
Visual C#, System.Drawing

Find great Windows Forms articles in Windows Forms Tips and Tricks
http://www.bobpowell.net/tipstricks.htm

Answer those GDI+ questions with the GDI+ FAQ
http://www.bobpowell.net/faqmain.htm

All new articles provide code in C# and VB.NET.
Subscribe to the RSS feeds provided and never miss a new article.

"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...

Hiya

Can anybody help me with the following?

I have an Access Database. There are two tables in it. tblCustomers and
tblOrders.

tblCustomers:

Customer_ID
Customer Address

tblOrders

Order_ID
Customer_FK
Product Ordered

There is a one to many relationship between the two tables.

How can I generate the orders that a given customer has made? I do not
wish to modifty the database in any way. All that I need to do is generate
the results as another table.

I know how to do this using DataTables, a DataSet and Relationships.
HOWEVER, I'm wondering if there is a QUICK way to do this using an SQL
command; ideally without creating DataAdaptors.

I'd be most grateful for any help and example code.

Many thanks in advance

Geoff

[Geoff Jones]

Many thanks Bob

Could you give an example of teh code that would do this?

Geoff

"Bob Powell [MVP]" <bob@_spamkiller_bobpowell.net> wrote in message
news:%2******************@TK2MSFTNGP14.phx.gbl...

The minimum you need is a connection. Drag the database from the server
explorer onto the form and a connection will be made. This should be an
OleDbConnection using the Jet 4.0 driver. Create an OleDbCommand object in
code and write the SQL by hand OR use the Access query wizard to design
the query and drag that from the stored procedures section in the database
explorer onto the form. a command will be created.

To be honest, creating a data adapter and hence a dataset is probably
quicker in the end because you can do all of the databinding etc from the
IDE. Using the method described above you still have to open the database
connection, execute the command, read the records from a datareader and
use the rows individually.

--
Bob Powell [MVP]
Visual C#, System.Drawing

Find great Windows Forms articles in Windows Forms Tips and Tricks
http://www.bobpowell.net/tipstricks.htm

Answer those GDI+ questions with the GDI+ FAQ
http://www.bobpowell.net/faqmain.htm

All new articles provide code in C# and VB.NET.
Subscribe to the RSS feeds provided and never miss a new article.

"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...

Hiya

Can anybody help me with the following?

I have an Access Database. There are two tables in it. tblCustomers and
tblOrders.

tblCustomers:

Customer_ID
Customer Address

tblOrders

Order_ID
Customer_FK
Product Ordered

There is a one to many relationship between the two tables.

How can I generate the orders that a given customer has made? I do not
wish to modifty the database in any way. All that I need to do is
generate the results as another table.

I know how to do this using DataTables, a DataSet and Relationships.
HOWEVER, I'm wondering if there is a QUICK way to do this using an SQL
command; ideally without creating DataAdaptors.

I'd be most grateful for any help and example code.

Many thanks in advance

Geoff

[Cor Ligthert]

Geoff,

AFAIK is using the dataadapter just the shortest way, so you make it
terrible difficult to answer your question. There are more methods, however
making a relation you need when you don't do it completly by hand in ADONET
always the dataset.

I hope this helps anyway,

Cor

[Geoff Jones]

AFAIK? Sorry, I don't understand.

Geoff

"Cor Ligthert" <no************@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

Geoff,

AFAIK is using the dataadapter just the shortest way, so you make it
terrible difficult to answer your question. There are more methods,
however making a relation you need when you don't do it completly by hand
in ADONET always the dataset.

I hope this helps anyway,

Cor

[C-Services Holland b.v.]

Geoff Jones wrote:

AFAIK? Sorry, I don't understand.

Geoff

AFAIK = As Far As I Know

--
Rinze van Huizen
C-Services Holland b.v.

[Cor Ligthert]

Geoff,

Beside that AFAIK (Thanks Rinze)

There are more methods, however when you want to use a relation, than you
need (when you don't do it completly by hand) in ADONET always the dataset.

I hope that this makes it a little bit more readable?

:-)

Cor

[Geoff Jones]

Ah, you live and learn eh - LOL

Geoff

"C-Services Holland b.v." <cs*@REMOVEcsh4u.nl> wrote in message
news:5N********************@zeelandnet.nl...

Geoff Jones wrote:

AFAIK? Sorry, I don't understand.

Geoff

AFAIK = As Far As I Know

--
Rinze van Huizen
C-Services Holland b.v.

[Cor Ligthert]

LOL, ? Sorry, I don't understand.

:-)

Cor

[Geoff Jones]

Yes, thanks Cor

Geoff

"Cor Ligthert" <no************@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

Geoff,

Beside that AFAIK (Thanks Rinze)

There are more methods, however when you want to use a relation, than you
need (when you don't do it completly by hand) in ADONET always the
dataset.

I hope that this makes it a little bit more readable?

:-)

Cor