GridView select statement not showing anything

Question

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="Match_ID" DataSourceID="SqlDataSource4">
            <Columns>
                <asp:BoundField DataField="Team_name" HeaderText="Team_name" SortExpression="Team_name" />
                <asp:BoundField DataField="Match_ID" HeaderText="Match_ID" InsertVisible="False" ReadOnly="True" SortExpression="Match_ID" />
                <asp:BoundField DataField="Home_team_ID" HeaderText="Home_team_ID" SortExpression="Home_team_ID" />
                <asp:BoundField DataField="Away_team_ID" HeaderText="Away_team_ID" SortExpression="Away_team_ID" />
                <asp:BoundField DataField="Home_team_score" HeaderText="Home_team_score" SortExpression="Home_team_score" />
                <asp:BoundField DataField="Away_team_score" HeaderText="Away_team_score" SortExpression="Away_team_score" />
                <asp:BoundField DataField="Game_date" HeaderText="Game_date" SortExpression="Game_date" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:SportsData2ConnectionString %>" SelectCommand="SELECT Team.Team_name, MatchStatistics.Match_ID, MatchStatistics.Home_team_ID, MatchStatistics.Away_team_ID, MatchStatistics.Home_team_score, MatchStatistics.Away_team_score, MatchStatistics.Game_date FROM MatchStatistics INNER JOIN Team ON MatchStatistics.Home_team_ID = Team.Team_ID AND MatchStatistics.Away_team_ID = Team.Team_ID ORDER BY MatchStatistics.Game_date DESC"></asp:SqlDataSource>

I want to create a GridView displaying all records inside MatchStatistics. To do so I need to gather Team_name from the Team table. However, even when MatchStatistics has data the select statement doesn't work. What's wrong with it?

My intended GridView should be all of the contents of MatchStatistics with Home and away Team IDs replaced with their Team_names. Similar to below but with dropdownlists in place of home and away team ID.

your INNER JOIN seems a bit suspicious. Does it work if you remove the `AND MatchStatistics.Away_team_ID = Team.Team_ID` part? Try running your query in SQL management studio to test it out before you put it in your code. Also, what does _the select statement doesn't work_ mean? are you getting errors? — zgood, May 05 '16 at 19:06
It works if I remove `AND MatchStatistics.Away_team_ID = Team.Team_ID` so do I only need one of these values or both? The select statement I posted returns no records. I'd like to not display ID's in the table, instead their names. — A. McLean, May 05 '16 at 19:13
It seems to me, that the problem is the row called `Team_name`, which will be the in the joined table multiple times, since you are double joining the same table, so the GridView wouldn't know which one to show. Maybe this will help ["What's the best way to join on the same table twice"](http://stackoverflow.com/questions/4267929/whats-the-best-way-to-join-on-the-same-table-twice) — chmodder, May 05 '16 at 19:52
I think what @BrianMains said is closer to what I want. However it's giving me a match ID for each team name in home and away. Is there a way I can return single match ID's ? E.g. "1, 2, 3" not "1, 1, 2, 2, 3..." etc — A. McLean, May 05 '16 at 19:56
May I ask which team you expect it to show in the GridView field `Team_name`? — chmodder, May 05 '16 at 20:15
I don't need that column, only the data accessing it so it is irrelevant. I only want all the columns inside `MatchStatistics` but have it say the relevant team names inside `Away_Team_ID` and `Home_Team_ID` but I can't do that without combining the select statement with `Team` table surely? — A. McLean, May 05 '16 at 22:51
No, you need to join the tables, if you want some teamdata like the name. I Updated my answer to match your requirements. — chmodder, May 06 '16 at 02:06

score 0 · Answer 1 · answered May 05 '16 at 19:19

0

I think you want to use OR:

FROM MatchStatistics 
INNER JOIN Team 
ON MatchStatistics.Home_team_ID = Team.Team_ID 
**OR** MatchStatistics.Away_team_ID = Team.Team_ID

As it is, you are expecting the team to match the home AND away, I think you want to find any home or away teams.

answered May 05 '16 at 19:19

Brian Mains

50,520
35
148
257

Now I get 2 rows for each `Match_ID` with `Team_ID` and `Team_name` being different for each. I only want to list 1 row for each match – A. McLean May 05 '16 at 19:29
Yes but which team are you trying to match, the home team, or the away team, or a team the user chooses? – Brian Mains May 05 '16 at 20:41
I'm thinking @chmodder's solution what you are really looking for. – Brian Mains May 05 '16 at 20:42
I'm trying to display a list of matches made earlier by the user so that it displays a match_ID, home and away team, home and away scores and its date. Having two columns each with the home team and away team in the `Team_name` column is not what I want – A. McLean May 05 '16 at 22:49
Use what @chmodder is doing then, as that is what you want. – Brian Mains May 06 '16 at 13:56

chmodder · Answer 2 · 2016-05-06T02:01:21.870

0

Try this query:

SELECT 
    t1.Team_name AS Home_Team_Name,
    t2.Team_name AS Away_Team_Name,
    m.Match_ID, 
    m.Home_team_score, 
    m.Away_team_score, 
    m.Game_date 
FROM MatchStatistics m
INNER JOIN Team t1 ON t1.Team_ID = m.Home_team_ID
INNER JOIN Team t2 ON t2.Team_ID = m.Away_team_ID
ORDER BY m.Game_date DESC

This should give you the teamnames instead of the teamId's.

Remember to delete the first Column in the GridView and update the the DataFields and SortExpressions to match the new column-names from the query:

Match_ID
Home_Team_Name
Away_Team_Name
Home_team_score
Away_team_score
Game_date

You can also check the sqlfiddle (MySQL) i made to test it. Just know that i used some slightly different column-names in the fiddle, if you should feel the need to copy something from it.

edited May 06 '16 at 02:01

answered May 05 '16 at 20:29

chmodder

184
1
3
17

This is closer to what I want but not quite. I've posted in the OP exactly what I'm aiming to do. It may just be that I'm having difficulty in setting my drop-down list to select the list of teams and set it as either Home team ID or away team ID depending on the column. – A. McLean May 05 '16 at 23:10
Let me see if I get it right. You don't want Id to be shown but instead you want the team names to be shown? If that is the case, try to add ` SELECT t1.Team_name AS Home_teamname, t2.Team_name AS Away_teamname.....` instead of `SELECT t1.team_name AS Home_team_name, m.Home_team_id, m.Away_team_ID` and see what happens. – chmodder May 05 '16 at 23:32
That is brilliant, it works! However, when editing I want to make the two home and away team lists DropDownLists that display a list of all team names. The value to be inside them should be the team ID that replaces the team IDs inside home and away if they need to be changed. Is this even possible with this select statement? I get back `'DropDownList6' has a SelectedValue which is invalid because it does not exist in the list of items. Parameter name: value` when trying to databind it to `Team_ID` – A. McLean May 06 '16 at 22:56
Glad it works. I'm not really sure about what you are trying to achieve with your dropdownlist. Maybe you still need the Team_Id's? If so, try to add them (`t1.Team_Id AS HomeTeamId, t2.Team_Id AS AwayTeamId`) to the query without creating new column fields in your gridview. – chmodder May 06 '16 at 23:46
Ignoring the dropdownlists, It now wont let me update any row giving me the error `The parameterized query '(@Match_ID int,@Home_team_ID int,@Away_team_ID int,@Home_team_sc' expects the parameter '@Match_ID', which was not supplied.` even when I've parameterised`Match_ID` – A. McLean May 07 '16 at 17:27

GridView select statement not showing anything

2 Answers2