Gridview Guru, please help

Aug 9, 2007 at 12:24 PM
OK, Im stuck

Im trying to build my own results viewer of a results database I have on my site. The results database is seperate and I want to be able to view results on it according to criteria in dropdown boxes and text boxes.

So, I have, at the moment just 2 dropdown boxes that I have populated with the race name and the club name respectively, these dropdowns are postback and only return unique data. When someone clicks the drop down they can choose a race name and a club name, the Gridview will return the data according to thier choice. GREAT!

Now, I updated the dropdowns to default to show a --choose a race-- and a --choose a club-- item by default, I append the unique data under these items. Again works great.

However, I want the gridview to return all data if the default --choose a race -- and --choose a club-- is selected. SO for example if someone chooses a race but not a club, it will return all clubs who have run that race as no club was selected, i.e. it was null (which I will set as the value).

Here is my select statement.
SELECT FirstName, LastName, Age, club, race, Position, Catagory, Time, date FROM raceresults WHERE ((club = @club) AND (race = @race)) ORDER BY Position

Anyone have any ideas? I have looked on the internet and think I might have to go down the meathods route but that is going over my head a bit at the moment.

Cheers

Darren
Aug 9, 2007 at 3:10 PM
You have to build a custom select statement.
A WHERE clause is a constraint, so if someone chooses a race but not a club you do not need a club-constraint and the select statement should be like this:

Select FirstName, LastName, Age, club, race, Position, Category, Time, date FROM raceresults WHERE race = @race ORDER BY Position

Or you create a stored procedure and there you handle your parameters.
e.g. @club = '0' --> select all clubs, alternatively do not constrain the clubs.
I would not transmitt null as a parameter!

By the way, perhaps you should store your clubs in a 2nd table and join the tables later. At this point you also can do your constraints with the ON operation.
1 race, multiple clubs --> 1:n relation

Hope that helps.
Thorsten
Aug 9, 2007 at 4:26 PM
OK

I understand that the where command is a constraint, and that by selecting a value in the dropdown I contrain the data to that subset. I do want to give my runners the option of constraining the data further by the club name, if they so wish. I also want to be able to give them the option to constrain the data by Club and not by race, and even by LastName with or with the constraints of Club and Race.

So I guess by option is to construct a stored procedure to do this? Im afraid Im out of my league and would really appreciate any pointers on that one, I am trying to follow some of the lessons in http://www.asp.net/learn/data-access/ but this last bit is a bit over my head.

As for Joining tables, I cant see the benefit of putting clubs in another table, I have no way of knowing what clubs may turn up and will use the unique option in the dropdown list to populate those, rather than having to pick them out and update a seperate table.

Thanks for your help (my head hurts)
Aug 10, 2007 at 4:52 PM
Darren, I can't help with your current question, but was just wondering if you received the email I sent several days ago? I sent it through the link on this site but haven't heard back from you.

Ed
Aug 11, 2007 at 11:52 AM
hi Ed

No, I didnt see that, probably ended up in my junk mail folder and I didnt notice it. (not suggesting your mail was junk :-))