barrykellett
Programmer
I have a Football League database which stores several tables including a table with Team Details and a table which stores the results of fixtures of those teams.
For Example, My teams table looks like this:
TeamID SeasonID LeagueID DivisionID Name
1 1 1 2 Abbey Villa III
2 1 1 2 Agape
3 1 1 2 Ballygowan
4 1 1 2 Bangor Swifts III
5 1 1 2 Bangor YM III
6 1 1 2 Belfast Deaf UTD
7 1 1 2 Bryansburn Rgrs III
8 1 1 2 Newtown Forest
9 1 1 2 Oakfield
10 1 1 2 Queens Grads III
11 1 1 2 Stranmillis 37th
12 1 1 2 Willowfield
13 1 1 2 Vision Athletic
14 1 1 2 43rd OB
15 1 1 2 8th OB
16 1 1 2 82nd OB
And my Fixtures table looks like this:
FixtureID TeamID OpponentID HomeGoals AwayGoals
5 2 4 2 1
6 5 2 3 2
7 8 15 4 4
8 9 8 4 4
9 7 4 2 2
10 7 9 1 2
11 16 2 3 2
12 7 11 1 3
13 15 5 3 3
14 2 3 1 2
15 2 10 12 2
In ASP I have tried to write a page which will query these two tables to get the team name, and then query each fixture for that team and draw a table in HTML which will include the number of games each team has played, the number of wins, losses and draws, the total goals scored and conceded and the total points tally.
I am having trouble stepping through the data to get what I want output.
Here is the code for the page that I am attempting to do this for. Presently it is set only to step through the fixtures and calculate for home teams. But If i could get that even working it would be simple to adapt it to cover both home and away teams.
Thanks
For Example, My teams table looks like this:
TeamID SeasonID LeagueID DivisionID Name
1 1 1 2 Abbey Villa III
2 1 1 2 Agape
3 1 1 2 Ballygowan
4 1 1 2 Bangor Swifts III
5 1 1 2 Bangor YM III
6 1 1 2 Belfast Deaf UTD
7 1 1 2 Bryansburn Rgrs III
8 1 1 2 Newtown Forest
9 1 1 2 Oakfield
10 1 1 2 Queens Grads III
11 1 1 2 Stranmillis 37th
12 1 1 2 Willowfield
13 1 1 2 Vision Athletic
14 1 1 2 43rd OB
15 1 1 2 8th OB
16 1 1 2 82nd OB
And my Fixtures table looks like this:
FixtureID TeamID OpponentID HomeGoals AwayGoals
5 2 4 2 1
6 5 2 3 2
7 8 15 4 4
8 9 8 4 4
9 7 4 2 2
10 7 9 1 2
11 16 2 3 2
12 7 11 1 3
13 15 5 3 3
14 2 3 1 2
15 2 10 12 2
In ASP I have tried to write a page which will query these two tables to get the team name, and then query each fixture for that team and draw a table in HTML which will include the number of games each team has played, the number of wins, losses and draws, the total goals scored and conceded and the total points tally.
I am having trouble stepping through the data to get what I want output.
Here is the code for the page that I am attempting to do this for. Presently it is set only to step through the fixtures and calculate for home teams. But If i could get that even working it would be simple to adapt it to cover both home and away teams.
Code:
<html>
<head>
<title>League Table</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsLeague 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database
Dim Position, Played, won, lost, drawn, goalsfor, goalsagainst, points, TeamIdent, TeamName 'Team league details'
Position = Cint(1)
Played = Cint(0)
won = Cint(0)
lost = Cint(0)
drawn = Cint(0)
goalsfor = Cint(0)
goalsagainst = Cint(0)
points = Cint(0)
TeamIdent = Cint(0)
'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("DAFL.mdb")
'Create an ADO recordset object
Set rsLeague = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT tbl_Fixtures.TeamID, tbl_teams.Name, tbl_Fixtures.HomeGoals, tbl_Fixtures.AwayGoals FROM tbl_teams, tbl_Fixtures WHERE tbl_teams.TeamID = tbl_Fixtures.TeamID;"
'Open the recordset with the SQL query
rsLeague.Open strSQL, adoCon
'Loop through the recordset
Response.Write ("<TABLE Border=""1"" Cellpadding=""10"">")
Response.Write ("<TR>")
Response.Write (" <TD>Position</TD>")
Response.Write (" <TD>Team</TD>")
Response.Write (" <TD>Played</TD>")
Response.Write (" <TD>Won</TD>")
Response.Write (" <TD>Lost</TD>")
Response.Write (" <TD>Drawn</TD>")
Response.Write (" <TD>GF</TD>")
Response.Write (" <TD>GA</TD>")
Response.Write (" <TD>Points</TD>")
Response.Write (" </TR>")
Do While not rsLeague.EOF
goalsfor = rsLeague("HomeGoals")
goalsagainst = rsLeague("AwayGoals")
TeamName = rsLeague("Name")
'Write the HTML to display the current record in the recordset
IF goalsfor > goalsagainst THEN
Played = Played + 1
Won = Won + 1
points = points + 3
goalsfor = goalsfor + rsleague("HomeGoals")
goalsagainst = goalsagainst + rsleague("AwayGoals")
rsLeague.MoveNext
END IF
IF goalsfor < goalsagainst THEN
Played = Played + 1
Lost = Lost + 1
goalsfor = goalsfor + rsleague("HomeGoals")
goalsagainst = goalsagainst + rsleague("AwayGoals")
rsLeague.MoveNext
END IF
IF goalsfor = goalsagainst THEN
Played = Played + 1
Drawn = Drawn + 1
Points = Points + 1
goalsfor = goalsfor + rsleague("HomeGoals")
goalsagainst = goalsagainst + rsleague("AwayGoals")
rsLeague.MoveNext
END IF
Response.Write ("<TR>")
Response.Write (" <TD>"& Position &"</TD>")
Response.Write (" <TD>"& TeamName &"</TD>")
Response.Write (" <TD>"& Played &"</TD>")
Response.Write (" <TD>"& Won &"</TD>")
Response.Write (" <TD>"& Drawn &"</TD>")
Response.Write (" <TD>"& Lost &"</TD>")
Response.Write (" <TD>"& goalsfor &"</TD>")
Response.Write (" <TD>"& goalsagainst &"</TD>")
Response.Write (" <TD>"& Points &"</TD>")
Response.Write ("<br>")
Position = Position + 1
'rsLeague.MoveFirst
Played = Cint(0)
won = Cint(0)
lost = Cint(0)
drawn = Cint(0)
goalsfor = Cint(0)
goalsagainst = Cint(0)
points = Cint(0)
'Move to the next record in the recordset
'rsLeague.MoveNext
Loop
'Reset server objects
rsLeague.Close
Set rsLeague = Nothing
Set adoCon = Nothing
%>
</body>
</html>