GridView showing record count from another table 1

[bobmmp]

I have a gridview that dispays the a table with possible project conditions. The project is a VB project.

The Gridview displays the table From and SqlDataSource.
The first number is the is the PK

1.Open | 0
2.Pending | 0
3.Closed | 0

There is a seperate table called projects that one of the fields is called Status and will either have the values of 1,2, or 3 (FK) representing open, pending or closed above

I am trying to get the today number of each condition / Status (number of rows containing a condition and not the sum) and replace the 0 in the above table. I tried setting up a Templete field and doing databinding to an additional datasource with no luck... Stuck. I did read an article showing how to using an ObjectDataSource to add totals to the footer, but those results were from the same table and not an external table. I would prefer to use SqlDataSource over ObjectDataSource because of my experience level.

Any help is appreciated!
Thanks


Bob B.

http://www.adigitalhost.com

 

[ca8msm]

Why can't you do this in your SQL statement?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[bobmmp]

I a gave some though to doing this, but was afraid I would have to in include a COUNT(*) and was afraid that it might not be very efficent since a COUNT(*) basicly loops through all the records. Also, I was confused on how to add that result to my gridview. Could you provide an example? Or point me to a good article.

BTW, thanks for your reply.

Bob B.

http://www.adigitalhost.com

 

[ca8msm]

The count will only be inefficient if your database structure is inefficient and will probably be the best and quickest method to retrieve the data. I'd go with that option.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[bobmmp]

This was my end solution that worked.. will see what happens when there are thousands of projects and not hundreds.

SELECT COUNT(Project.Status) AS Stats, StatusList.StatusName, StatusList.StatusID
FROM Projects
INNER JOIN StatusList ON Project.Status = StatusList.StatusID AND Project.Status = StatusList.StatusID
GROUP BY StatusList.StatusName, StatusList.StatusID

Tables
StatusList - (As a total of 4 rows/records)
StatusID (PK)
StatusName
Project - (has about 200 records so far)
ProjectID (PK)
ProjectName
ProjectDesc
Status (FK)
StatusDate

How this will perform with thousands of records, not a clue. But it works fine so far with hundreds

Hope this helps somebody else!


Bob B.

http://www.adigitalhost.com

 

[ca8msm]

Thousands of records shouldn't be a problem at all and as long as you set up indexes on any columns that are needed, you shouldn't have any problems once you get into hundreds of thousands or millions either.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.