Fotballresultater

protected void VisLag_Click(object sender, EventArgs e)
{
    // dropdowns are set up with AutoPostBack, we get all changes here
    TextTest1.Visible = false;
    ListBox1.Visible = false;
    ListBox2.Visible = false;
    // get country_id from countryname(shortcut)
    String countryId;
    if (country.CompareTo("England") == 0) countryId = "en";
    else if (country.CompareTo("Italia") == 0) countryId = "it";
    else if (country.CompareTo("Spania") == 0) countryId = "es";
    else countryId = "de";
    // Set up query
     string sql = String.Format(
         @"SELECT name
            FROM db_owner.team
            WHERE (team_id IN
                      (SELECT team_id
                        FROM db_owner.member
                        WHERE (liga_id IN
                        (SELECT liga_id
                          FROM db_owner.liga
                            WHERE (season_id = '{0}') AND (country_id = '{1}') AND (level = '{2}')))));",
             year, countryId, level);
    MakeTeamList(sql);
}
public  void MakeTeamList(string sql)
{
    // connect to database 
    String connectString =
                @"SERVER=donau.hiof.no;
                DATABASE=bs;
                UID=student;
                PASSWORD=student;
                PROVIDER=SQLOLEDB";
    //use OLE
    OleDbConnection con = null;
    OleDbDataReader myReader = null;
    ListBox1.Items.Clear();
    try
    {
        con = new OleDbConnection(connectString);
        con.Open();
        // execute the query
        OleDbCommand myCommand = new OleDbCommand(sql, con);
        myReader = myCommand.ExecuteReader();
        while (myReader.Read())
        {
            String name = myReader.GetString(0);
            ListBox1.Items.Add(name);
        }
        ListBox1.Visible = true;
        return;
    }
    catch (Exception ex)
    {
        // error message in textfield
        TextTest1.Visible = true;
        TextTest1.Text = ex.Message;
        return ;
    }
    finally
    {
        if (con != null)
            con.Close();
        if (myReader != null)
            myReader.Close();
    }
}

protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    // ListBox1 is set up with AutoPostBack = True
    // so we get this as a direct consequence of selection change
    // get country_id from countryname(shortcut)
    String countryId;
    if(country.CompareTo("England")==0) countryId="en";
    else countryId = "de";
    String team = ListBox1.SelectedValue;
    // set up query        
    String sql=String.Format
        (@"SELECT hteam.name, ateam.name AS Expr1, db_owner.fmatch.h_goals, db_owner.fmatch.a_goals
            FROM  db_owner.fmatch INNER JOIN
                  db_owner.team AS hteam ON db_owner.fmatch.h_team_id = hteam.team_id INNER JOIN
                  db_owner.team AS ateam ON db_owner.fmatch.a_team_id = ateam.team_id
            WHERE (hteam.name = '{0}') AND (db_owner.fmatch.liga_id IN
                      (SELECT liga_id
                        FROM  db_owner.liga
                        WHERE (season_id = '{1}') AND (country_id = '{2}') AND (level = '{3}'))) OR
                        (db_owner.fmatch.liga_id IN
                        (SELECT liga_id
                            FROM  db_owner.liga AS liga_1
                            WHERE (season_id = '{1}') AND (country_id = '{2}') AND (level = '{3}'))) 
                                    AND (ateam.name = '{0}');", team, year, countryId, level);
    MakeMatchList(sql);
 }
public void MakeMatchList(string sql)
{
    String connectString =
               @"SERVER=donau.hiof.no;
                DATABASE=bs;
                UID=student;
                PASSWORD=student;
                PROVIDER=SQLOLEDB";
    OleDbConnection con = null;
    OleDbDataReader myReader = null;
    ListBox2.Items.Clear();
    try
    {
        con = new OleDbConnection(connectString);
        con.Open();
        // execute a query
        OleDbCommand myCommand = new OleDbCommand(sql, con);
        myReader = myCommand.ExecuteReader();
        while (myReader.Read())
        {
            String name = myReader.GetString(0)
                 + " - " + myReader.GetString(1)
                 + " -- " + myReader.GetInt32(2).ToString() 
                 + ":" + myReader.GetInt32(2).ToString();
            ListBox2.Items.Add(name);
        }
        ListBox2.Visible = true;
        return;
    }
    catch (Exception ex)
    {
        // error message
        TextTest1.Visible = true;
        TextTest1.Text = ex.Message;
        return;
    }
    finally
    {
        if (con != null)
            con.Close();
        if (myReader != null)
            myReader.Close();
    }
}