Fill TableAdapter with Stored Procedure?

[FreeriderUK]

Hi all,

first of all, I should explain what I'm trying to do since I may be going about it the wrong way...

I have a Dataset which contains 3 related tables -

CustomerType:
CustomerTypeID
CustomerTypeDescription

Customers:
CustomerID
CustomerTypeID
CustomerName

Jobs:
JobID
CustomerID
ScheduledDate

I have a combobox which selects the CustomerType - from which I get a list of customers (in a Listbox) and their jobs (as details).

So far so good.

Using the selected CustomerType, I also want to "fill" a datagridview (a different dataset) with ALL jobs (including CustomerName) sorted by ScheduledDate - (Related post)

What's the best way to achieve this?
I've tried Stored Procedures but ran into a couple of problems which I can explain if necessary...

Using SQL Server Express.

 

[cjard]

Fill TA with Stored Procedure

We dont fill TAs, we fill datasets. Filling a TA is like asking how to fill a tap with water

Using the selected CustomerType, I also want to "fill" a datagridview (a different dataset) with ALL jobs (including CustomerName) sorted by ScheduledDate - (Related post)

I dont understand; you imply that there is a predicate "Using the selected CustomerType" but then imply that there is no predicate "ALL jobs"

What's the best way to achieve this?

If you can clarify the question, perhaps include some sample data of what columns you expect to see, then I'm sure I can help

 

[FreeriderUK]

If you can clarify the question, perhaps include some sample data of what columns you expect to see, then I'm sure I can help

OK. I'll try...

The following shows the three tables and some data:

CustomerType table
-------------------
CustomerType '1'
CustomerType '2'
CustomerType '3'

Customers table
---------------
Customer One (CustomerID = 5) (CustomerType = '1')
Customer Two (CustomerID = 10) (CustomerType = '2')
Customer Three (CustomerID = 15) (CustomerType = '2')
Customer Four (CustomerID = 20) (CustomerType = '2')
Customer Five (CustomerID = 25) (CustomerType = '3')

Jobs table
----------
Job No. 100 (JobID = 5) CustomerID = '5':
Job No. 130 (JobID = 6) (CustomerID = '10')
Job No. 135 (JobID = 7) (CustomerID = '10')
Job No. 140 (JobID = 8) (CustomerID = '10')
Job No. 250 (JobID = 9) (CustomerID = '15')
Job No. 251 (JobID = 10) (CustomerID = '20')


In the combobox, I select the CustomerType - '2' for example.
Which displays in my Listbox:
Customer Two
Customer Three
Customer Four

If I click on Customer Two, I can see jobs:
Job No. 130
Job No. 135
Job No. 140

GREAT!

What I would ALSO like (when I select the CustomerType - '2' in the combobox) is a list of all Jobs for Customers which have CustomerType = '2' to be listed in a Listbox - which from this sample data would be:
Job No. 130 (JobID = 6) (CustomerID = '10')
Job No. 135 (JobID = 7) (CustomerID = '10')
Job No. 140 (JobID = 8) (CustomerID = '10')
Job No. 250 (JobID = 9) (CustomerID = '15')
Job No. 251 (JobID = 10) (CustomerID = '20')

---

I am able to achieve this by using something like this in a Stored Procedure:

SELECT Jobs.Description
FROM CustomerType 
JOIN ON CustomerType.CustomerTypeID = Customers.CustomerTypeID 
JOIN Jobs ON Customers.CustomerID = Jobs.CustomerID
WHERE CustomerType.CustomerTypeID = @CustomerType

But the problem I was having was I had to select the combobox twice before I got the results I expected (the all Jobs Listbox seemed to be using the CustomerTypeID I had previously selected. So, I though I might be going about it the wrong way.

Hope all this make sense...

 

[cjard]

See attached. Important points:

There is no use of the relation between CusType and Cust
Jobs and Cust both have a extra query to fill info based on cus type
Selecting CusType causes 2 Fill operations to run
The bottom DGV shows info from the job table purely based on selected custid
The top Customer DGV again shows info fromthe Cust tablebased on chosen CustID
The topjobs view uses the DataRelation on the cust bind source rather than a direct view to the table.
There are 3 bind sources, one direct to jobs, one direct to cust and one receiving only related jobs from cust

 

[FreeriderUK]

Thanks for the help cjard. Much appreciated.
I've even been able to get this working in my app.!

 

[FreeriderUK]

How would I add the Cust.Description to the bottom DGV?
I tried adding it into the TA SQL query, but it then loses the Update command.

[EDITED]
I tried it again and it works ok now.

 

[FreeriderUK]

No it doesn't!

I can add it using the above example as it's Access, but I'm using SQL database.

When I re-configure the TA query (selecting 'Generate Insert, Update and Delete statements'), the wizard returns error:
Dynamic SQL generation is not supported against multiple base tables

What am I missing?