Option Strict On
Option Explicit On 

Imports System.Data
Imports System.Data.SqlClient

Public Class EmailManager
    Inherits System.Web.UI.Page
    Protected WithEvents Conn As New SqlConnection(ConfigurationSettings.AppSettings("sConnection"))
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Label2 As System.Web.UI.WebControls.Label
    Protected WithEvents Label3 As System.Web.UI.WebControls.Label
    Protected WithEvents Label4 As System.Web.UI.WebControls.Label
    Protected WithEvents SportDDL_Grade1 As System.Web.UI.WebControls.DropDownList
    Protected WithEvents GradeDDL1 As System.Web.UI.WebControls.ListBox
    Protected WithEvents EmailsTB1 As System.Web.UI.WebControls.TextBox
    Protected WithEvents UpdateBtn1 As System.Web.UI.WebControls.Button
    Protected WithEvents DownloadBtn1 As System.Web.UI.WebControls.Button
    Protected WithEvents Label5 As System.Web.UI.WebControls.Label
    Protected WithEvents SportDDL2 As System.Web.UI.WebControls.DropDownList
    Protected WithEvents SeasonDDL1 As System.Web.UI.WebControls.DropDownList
    Protected WithEvents UpdateBtn2 As System.Web.UI.WebControls.Button
    Protected WithEvents DownloadBtn2 As System.Web.UI.WebControls.Button
    Protected WithEvents EmailsTB2 As System.Web.UI.WebControls.TextBox
    Protected WithEvents Label7 As System.Web.UI.WebControls.Label
    Protected WithEvents SportDDL3 As System.Web.UI.WebControls.DropDownList
    Protected WithEvents GradeDDL2 As System.Web.UI.WebControls.ListBox
    Protected WithEvents Label6 As System.Web.UI.WebControls.Label
    Protected WithEvents SportDDL4 As System.Web.UI.WebControls.DropDownList
    Protected WithEvents SeasonDDL2 As System.Web.UI.WebControls.DropDownList
    Protected WithEvents EmailsTB4 As System.Web.UI.WebControls.TextBox
    Protected WithEvents UpdateBtn4 As System.Web.UI.WebControls.Button
    Protected WithEvents DownloadBtn4 As System.Web.UI.WebControls.Button
    Protected WithEvents UpdateBtn3 As System.Web.UI.WebControls.Button
    Protected WithEvents DownloadBtn3 As System.Web.UI.WebControls.Button
    Protected WithEvents EmailsTB3 As System.Web.UI.WebControls.TextBox
    Protected WithEvents DownloadLabel1 As System.Web.UI.WebControls.Label
    Protected WithEvents DownloadLabel2 As System.Web.UI.WebControls.Label
    Protected WithEvents DownloadLabel3 As System.Web.UI.WebControls.Label
    Protected WithEvents DownloadLabel4 As System.Web.UI.WebControls.Label
    Protected WithEvents DownloadLBtn2 As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents DownloadLBtn3 As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents DownloadLBtn4 As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents EmailsLabel1 As System.Web.UI.HtmlControls.HtmlGenericControl
    Protected WithEvents EmailsLabel2 As System.Web.UI.HtmlControls.HtmlGenericControl
    Protected WithEvents EmailsLabel3 As System.Web.UI.HtmlControls.HtmlGenericControl
    Protected WithEvents DownloadLBtn1_2 As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents DownloadCYO1 As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents DownloadAthletic1 As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents DownloadLBtn1 As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents DownloadCYO2 As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents DownloadAthletic2 As System.Web.UI.HtmlControls.HtmlAnchor
    Protected WithEvents DownloadCYOlbl1 As System.Web.UI.WebControls.Label
    Protected WithEvents DownloadAthleticlbl1 As System.Web.UI.WebControls.Label
    Protected WithEvents DownloadCYOlbl2 As System.Web.UI.WebControls.Label
    Protected WithEvents DownloadAthleticlbl2 As System.Web.UI.WebControls.Label
    Protected WithEvents EmailsLabel4 As System.Web.UI.HtmlControls.HtmlGenericControl

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        Dim cmd As New SqlCommand

        '<Sohaib Akram - Aug 13, 2011 - Start>

        If Not Page.IsPostBack Then

            EmailsTB1.ReadOnly = True

            Dim DS As New DataSet
            Dim mySqlDataAdapter As New SqlDataAdapter

            With cmd
                .CommandText = "Select * from as_DropDownLists"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn

            End With

            mySqlDataAdapter.SelectCommand = cmd
            mySqlDataAdapter.Fill(DS)

            If (DS.Tables(0).Rows.Count > 0) Then

                'Dim sport() As String
                'Dim season() As String
                'Dim grade() As String

                'sport = Split(CType(DS.Tables(0).Rows(0)(1), String), ",")
                'season = Split(CType(DS.Tables(0).Rows(0)(2), String), ",")
                'grade = Split(CType(DS.Tables(0).Rows(0)(3), String), ",")

                'For i As Integer = 0 To sport.Length - 1
                    'Dim li As New ListItem
                    'li.Value = sport(i).Trim()
                    'li.Text = sport(i).Trim()
                    'SportDDL_Grade1.Items.Add(li)
                'Next

                'Dim li1 As New ListItem
                'li1.Value = "all"
                'li1.Text = "All"
                'li1.Selected = True
                'GradeDDL1.Items.Add(li1)

                'For i As Integer = 0 To grade.Length - 1
                    'Dim li As New ListItem
                    'li.Value = grade(i).Trim()
                    'li.Text = grade(i).Trim()
                    'GradeDDL1.Items.Add(li)
                'Next

                'For i As Integer = 0 To sport.Length - 1
                    'Dim li As New ListItem
                    'li.Value = sport(i).Trim()
                    'li.Text = sport(i).Trim()
                    'SportDDL2.Items.Add(li)
                'Next

                'For i As Integer = 0 To season.Length - 1
                    'Dim li As New ListItem
                    'li.Value = season(i).Trim()
                    'li.Text = season(i).Trim()
                    'SeasonDDL1.Items.Add(li)
                'Next

                'For i As Integer = 0 To sport.Length - 1
                    'Dim li As New ListItem
                    'li.Value = sport(i).Trim()
                    'li.Text = sport(i).Trim()
                    'SportDDL3.Items.Add(li)
                'Next

                'Dim li2 As New ListItem
                'li2.Value = "all"
                'li2.Text = "All"
                'li2.Selected = True
                'GradeDDL2.Items.Add(li2)

                'For i As Integer = 0 To grade.Length - 1
                    'Dim li As New ListItem
                    'li.Value = grade(i).Trim()
                    'li.Text = grade(i).Trim()
                    'GradeDDL2.Items.Add(li)
                'Next

                'For i As Integer = 0 To sport.Length - 1
                    'Dim li As New ListItem
                    'li.Value = sport(i).Trim()
                    'li.Text = sport(i).Trim()
                    'SportDDL4.Items.Add(li)
                'Next

                'For i As Integer = 0 To season.Length - 1
                    'Dim li As New ListItem
                    'li.Value = season(i).Trim()
                    'li.Text = season(i).Trim()
                    'SeasonDDL2.Items.Add(li)
                'Next
            End If

            'Sports 
            Dim DS1 As New DataSet
            Dim mySqlDataAdapter1 As New SqlDataAdapter

            With cmd
                .CommandText = "SELECT Sport FROM as_Sports ORDER BY Sport"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn

            End With

            mySqlDataAdapter1.SelectCommand = cmd
            mySqlDataAdapter1.Fill(DS1)

            If (DS1.Tables(0).Rows.Count > 0) Then
                For i As Integer = 0 To DS1.Tables(0).Rows.Count - 1
                    If Not (IsDBNull(DS1.Tables(0).Rows(i)(0))) Then
                        Dim li As New ListItem
                        li.Value = CType(DS1.Tables(0).Rows(i)(0), String)
                        li.Text = CType(DS1.Tables(0).Rows(i)(0), String)
                        SportDDL_Grade1.Items.Add(li)

                        Dim li111 As New ListItem
                        li111.Value = CType(DS1.Tables(0).Rows(i)(0), String)
                        li111.Text = CType(DS1.Tables(0).Rows(i)(0), String)
                        SportDDL2.Items.Add(li111)

                        Dim li22 As New ListItem
                        li22.Value = CType(DS1.Tables(0).Rows(i)(0), String)
                        li22.Text = CType(DS1.Tables(0).Rows(i)(0), String)
                        SportDDL3.Items.Add(li22)

                        Dim li3 As New ListItem
                        li3.Value = CType(DS1.Tables(0).Rows(i)(0), String)
                        li3.Text = CType(DS1.Tables(0).Rows(i)(0), String)
                        SportDDL4.Items.Add(li3)

                    End If
                Next
            End If

            'Season  
            Dim DS2 As New DataSet
            Dim mySqlDataAdapter2 As New SqlDataAdapter

            With cmd
                .CommandText = "SELECT Season FROM as_Season ORDER BY Season"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn

            End With

            mySqlDataAdapter2.SelectCommand = cmd
            mySqlDataAdapter2.Fill(DS2)

            If (DS2.Tables(0).Rows.Count > 0) Then
                For i As Integer = 0 To DS2.Tables(0).Rows.Count - 1
                    If Not (IsDBNull(DS2.Tables(0).Rows(i)(0))) Then

                        Dim li As New ListItem
                        li.Value = CType(DS2.Tables(0).Rows(i)(0), String)
                        li.Text = CType(DS2.Tables(0).Rows(i)(0), String)
                        SeasonDDL1.Items.Add(li)

                        Dim li11 As New ListItem
                        li11.Value = CType(DS2.Tables(0).Rows(i)(0), String)
                        li11.Text = CType(DS2.Tables(0).Rows(i)(0), String)
                        SeasonDDL2.Items.Add(li11)

                    End If
                Next
            End If

            Dim li1 As New ListItem
            li1.Value = "all"
            li1.Text = "All"
            li1.Selected = True
            GradeDDL1.Items.Add(li1)

            Dim li2 As New ListItem
            li2.Value = "all"
            li2.Text = "All"
            li2.Selected = True
            GradeDDL2.Items.Add(li2)

            'Grade  
            Dim DS3 As New DataSet
            Dim mySqlDataAdapter3 As New SqlDataAdapter

            With cmd
                .CommandText = "SELECT Grade FROM as_Grade ORDER BY Grade"
                .CommandType = CommandType.Text
                If Conn.State = ConnectionState.Closed Then Conn.Open()
                .Connection = Conn

            End With

            mySqlDataAdapter3.SelectCommand = cmd
            mySqlDataAdapter3.Fill(DS3)

            If (DS3.Tables(0).Rows.Count > 0) Then
                For i As Integer = 0 To DS3.Tables(0).Rows.Count - 1
                    If Not (IsDBNull(DS3.Tables(0).Rows(i)(0))) Then

                        Dim li As New ListItem
                        li.Value = CType(DS3.Tables(0).Rows(i)(0), String)
                        li.Text = CType(DS3.Tables(0).Rows(i)(0), String)
                        GradeDDL1.Items.Add(li)

                        Dim li22 As New ListItem
                        li22.Value = CType(DS3.Tables(0).Rows(i)(0), String)
                        li22.Text = CType(DS3.Tables(0).Rows(i)(0), String)
                        GradeDDL2.Items.Add(li22)

                    End If
                Next
            End If

        End If


        '<Sohaib Akram - Aug 13, 2011 - End>

        '<SAQIB ADDED LINES>
        If Conn.State = ConnectionState.Open Then Conn.Close()
        '<SAQIB ADDED LINES/>


    End Sub

    Private Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Unload
        '<SAQIB ADDED LINES>
        If Conn.State = ConnectionState.Open Then Conn.Close()
        '<SAQIB ADDED LINES/>
    End Sub

    '<Sohaib Akram - Aug 13, 2011 - Start>

    Private Sub SetDownloadLinkGrade(ByVal formType As String, ByVal sport As String, ByVal grade As String, ByVal label As Label)

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        Dim cmd As New SqlCommand
        With cmd
            If formType.Equals("Coaching") Then
                .CommandText = "Select count(ID) from as_CoachForm where sport='" & sport & "' and (" & grade & ")"
            ElseIf formType.Equals("Athletic") Then
                If label.Text = "-1" Then
                    .CommandText = "SELECT count(ID) FROM (Select ID,'0' AS C1 from as_CYOLiabilityForm where sport='" & sport & "' and (" & grade & ") " & " and CYOStudentSignature <> '-' AND CYOParentSignature <> '-' AND CYOWitnessSignature <> '-' UNION Select ID,'1' AS C1 from as_CYOLiabilityFormTemp where sport='" & sport & "' and (" & grade & ") and CYOStudentSignature <> '-' AND CYOParentSignature <> '-' AND CYOWitnessSignature <> '-') AS t1"
                Else
                    .CommandText = "SELECT count(ID) FROM (Select ID,'0' AS C1 from as_CYOLiabilityForm where sport='" & sport & "' and (" & grade & ") " & " UNION Select ID,'1' AS C1 from as_CYOLiabilityFormTemp where sport='" & sport & "' and (" & grade & ")) AS t1"
                End If
            End If

            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            label.Text = CType(.ExecuteScalar(), String)
        End With

        If Conn.State = ConnectionState.Open Then Conn.Close()

    End Sub

    Private Sub SetDownloadLinkSeason(ByVal formType As String, ByVal sport As String, ByVal season As String, ByVal label As Label)

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        Dim cmd As New SqlCommand
        With cmd
            If formType.Equals("Coaching") Then
                .CommandText = "Select count(ID) from as_CoachForm where lTrim(rTrim(sport))='" & sport & "' and lTrim(rTrim(season))='" & season & "'"
            ElseIf formType.Equals("Athletic") Then
                If label.Text = "-1" Then
                    .CommandText = "SELECT count(ID) FROM (Select ID,'0' AS C1 from as_CYOLiabilityForm where sport='" & sport & "' and season='" & season & "'" & " and CYOStudentSignature <> '-' and CYOParentSignature <> '-' and CYOWitnessSignature <> '-' UNION Select ID,'1' AS C1 from as_CYOLiabilityFormTemp where sport='" & sport & "' and season='" & season & "' and CYOStudentSignature <> '-' and CYOParentSignature <> '-' and CYOWitnessSignature <> '-') as t1"
                Else
                    .CommandText = "SELECT count(ID) FROM (Select ID,'0' AS C1 from as_CYOLiabilityForm where sport='" & sport & "' and season='" & season & "'" & " UNION Select ID,'1' AS C1 from as_CYOLiabilityFormTemp where sport='" & sport & "' and season='" & season & "') as t1"
                End If
            ElseIf formType.Equals("Evaluate") Then
                .CommandText = "select count(*) from (select ID from as_SportsEvaluationFormnew where lTrim(rTrim(sport))='" & sport & "' and lTrim(rTrim(season))='" & season & "' ) as t"
            End If

            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            label.Text = CType(.ExecuteScalar(), String)
        End With

        If Conn.State = ConnectionState.Open Then Conn.Close()

    End Sub

    Private Sub SetEmailTextBox(ByVal formType As String, ByVal sport As String, ByVal gradeLB As ListBox, ByVal txtBox As TextBox)

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        Dim grade As String = ""

        If Not (gradeLB.Items(1).Selected = True) Then
            For i As Integer = 2 To gradeLB.Items.Count - 1
                If Not (gradeLB.Items(i).Text.Equals("Select Grade")) Then
                    If (gradeLB.Items(i).Selected = True) Then
                        Dim cmd As New SqlCommand
                        With cmd
                            .CommandText = "Select Emails from as_FormEmails where formtype='" & formType & "' and sport='" & sport & "' and grade='" & gradeLB.Items(i).Text & "'"
                            .CommandType = CommandType.Text
                            If Conn.State = ConnectionState.Closed Then Conn.Open()
                            .Connection = Conn
                            Dim grade2 As String = ""
                            If (grade.Equals("")) Then
                                grade += CType(.ExecuteScalar(), String)
                            Else
                                grade2 = CType(.ExecuteScalar(), String)
                                If Not grade2 Is Nothing Then
                                    grade += "," & grade2
                                End If
                            End If
                        End With
                    End If
                End If
            Next
        Else
            For i As Integer = 2 To gradeLB.Items.Count - 1
                If Not (gradeLB.Items(i).Text.Equals("Select Grade")) Then

                    Dim cmd As New SqlCommand
                    With cmd
                        .CommandText = "Select Emails from as_FormEmails where formtype='" & formType & "' and sport='" & sport & "' and grade='" & gradeLB.Items(i).Text & "'"
                        .CommandType = CommandType.Text
                        If Conn.State = ConnectionState.Closed Then Conn.Open()
                        .Connection = Conn
                        Dim grade2 As String = ""
                        If (grade.Equals("")) Then
                            grade += CType(.ExecuteScalar(), String)
                        Else
                            grade2 = CType(.ExecuteScalar(), String)
                            If Not grade2 Is Nothing Then
                                grade += "," & grade2
                            End If
                        End If
                    End With
                End If
            Next
        End If

        Dim grade22 As String = grade
        Dim gradeFinal As String = ""
        Dim emailIDs As String() = grade22.Split(New Char() {","c})

        Dim EId As String = ""
        For Each EId In emailIDs

            If (gradeFinal.IndexOf(EId) = -1) Then
                gradeFinal = gradeFinal & EId & ","
            End If
        Next

        If gradeFinal.Length > 0 Then
            gradeFinal = gradeFinal.Substring(0, gradeFinal.Length - 1)
        End If

        txtBox.Text = gradeFinal

        If Conn.State = ConnectionState.Open Then Conn.Close()

    End Sub

    Private Sub SetEmailTextBox2(ByVal formType As String, ByVal sport As String, ByVal season As String, ByVal txtBox As TextBox)

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        Dim cmd As New SqlCommand
        With cmd
            .CommandText = "Select Emails from as_FormEmails where formtype='" & formType & "' and sport='" & sport & "' and season='" & season & "'"
            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            txtBox.Text = CType(.ExecuteScalar(), String)
        End With

        If Conn.State = ConnectionState.Open Then Conn.Close()

    End Sub

    Private Sub SportDDL_Grade1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SportDDL_Grade1.SelectedIndexChanged

        If GradeDDL1.SelectedIndex > 0 Then
            If Not SportDDL_Grade1.SelectedValue.Equals("Select Sport") Then

                Dim grade As String = ""
                Dim grade1 As String = ""
                If Not (GradeDDL1.Items(1).Selected = True) Then
                    For i As Integer = 0 To GradeDDL1.Items.Count - 1
                        If Not (GradeDDL1.Items(i).Text.Equals("Select Grade")) Then
                            If GradeDDL1.Items(i).Selected = True Then
                                If (grade.Equals("")) Then
                                    grade += "grade = '" & GradeDDL1.Items(i).Text & "'"
                                Else
                                    grade += " or "
                                    grade += "grade = '" & GradeDDL1.Items(i).Text & "'"
                                End If

                                If grade1.Equals("") Then
                                    grade1 = GradeDDL1.Items(i).Text.Trim()
                                Else
                                    grade1 += "," & GradeDDL1.Items(i).Text.Trim()
                                End If
                            End If
                        End If
                    Next
                Else
                    For i As Integer = 2 To GradeDDL1.Items.Count - 1
                        If Not (GradeDDL1.Items(i).Text.Equals("Select Grade")) Then

                            If (grade.Equals("")) Then
                                grade += "grade = '" & GradeDDL1.Items(i).Text & "'"
                            Else
                                grade += " or "
                                grade += "grade = '" & GradeDDL1.Items(i).Text & "'"
                            End If

                            If grade1.Equals("") Then
                                grade1 = GradeDDL1.Items(i).Text.Trim()
                            Else
                                grade1 += "," & GradeDDL1.Items(i).Text.Trim()
                            End If

                        End If
                    Next
                End If
                Dim sportvalue As String
                sportvalue = SportDDL_Grade1.SelectedValue.ToString().Replace("&", "38")
                Call SetDownloadLinkGrade("Athletic", SportDDL_Grade1.SelectedValue, grade, DownloadLabel1)
                If DownloadLabel1.Text.Equals("0") Then
                    DownloadLBtn1.HRef = ""
                Else
                    DownloadLBtn1.HRef = "http://sms.allsaints.cc/CYOLiabilityForm/tabid/134/Default.aspx?download=true&type=grade&sport=" & sportvalue & "&grade=" & HttpUtility.UrlEncode(grade1) & "&table=both"
                End If


                DownloadCYOlbl1.Text = "-1"
                Call SetDownloadLinkGrade("Athletic", SportDDL_Grade1.SelectedValue, grade, DownloadCYOlbl1)
                If DownloadCYOlbl1.Text.Equals("0") Then
                    DownloadCYO1.HRef = ""
                Else
                    DownloadCYO1.HRef = "http://sms.allsaints.cc/CYOLiabilityForm/tabid/134/Default.aspx?download=all&type=grade&sport=" & sportvalue & "&grade=" & HttpUtility.UrlEncode(grade1) & "&table=both&formtype=cyo"
                End If

                DownloadAthleticlbl1.Text = DownloadLabel1.Text
                'Call SetDownloadLinkGrade("Athletic", SportDDL_Grade1.SelectedValue, grade, DownloadAthleticlbl1)
                If DownloadAthleticlbl1.Text.Equals("0") Then
                    DownloadAthletic1.HRef = ""
                Else
                    DownloadAthletic1.HRef = "http://sms.allsaints.cc/CYOLiabilityForm/tabid/134/Default.aspx?download=all&type=grade&sport=" & sportvalue & "&grade=" & HttpUtility.UrlEncode(grade1) & "&table=both&formtype=athletic"
                End If

                Call SetEmailTextBox("Athletic", SportDDL_Grade1.SelectedValue, GradeDDL1, EmailsTB1)
                EmailsTB1.ReadOnly = False
                EmailsLabel1.InnerText = ""
            Else
                DownloadLabel1.Text = "0"
                DownloadLBtn1.HRef = ""

                DownloadCYOlbl1.Text = "0"
                DownloadCYO1.HRef = ""

                DownloadAthleticlbl1.Text = "0"
                DownloadAthletic1.HRef = ""

                EmailsTB1.Text = ""
                EmailsTB1.ReadOnly = False
                EmailsLabel1.InnerText = "Please select sport."
            End If
        Else
            DownloadLabel1.Text = "0"
            DownloadLBtn1.HRef = ""

            DownloadCYOlbl1.Text = "0"
            DownloadCYO1.HRef = ""

            DownloadAthleticlbl1.Text = "0"
            DownloadAthletic1.HRef = ""

            EmailsTB1.Text = ""
            EmailsTB1.ReadOnly = False
            EmailsLabel1.InnerText = "Please select grade."
        End If

    End Sub

    'Private Sub GradeDDL1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    'If Not SportDDL_Grade1.SelectedValue.Equals("Select Sport") Then
    'If Not GradeDDL1.SelectedValue.Equals("Select Grade") Then
    'Call SetDownloadLinkGrade("Athletic", SportDDL_Grade1.SelectedValue, GradeDDL1.SelectedValue, DownloadLabel1)
    'If DownloadLabel1.Text.Equals("0") Then
    'DownloadLBtn1.HRef = ""
    'Else
    'DownloadLBtn1.HRef = "http://sms.allsaints.cc/CYOLiabilityForm/tabid/134/Default.aspx?download=true&type=grade&sport=" & SportDDL_Grade1.SelectedValue & "&grade=" & GradeDDL1.SelectedValue
    'End If

    'Call SetEmailTextBox("Athletic", SportDDL_Grade1.SelectedValue, GradeDDL1.SelectedValue, EmailsTB1)
    'EmailsTB1.ReadOnly = False
    'EmailsLabel1.InnerText = ""
    'Else
    'DownloadLabel1.Text = "0"
    'DownloadLBtn1.HRef = ""
    'EmailsTB1.Text = ""
    'EmailsTB1.ReadOnly = True
    'EmailsLabel1.InnerText = "Please select grade."
    'End If
    'Else
    'DownloadLabel1.Text = "0"
    'DownloadLBtn1.HRef = ""
    'EmailsTB1.Text = ""
    'EmailsTB1.ReadOnly = True
    'EmailsLabel1.InnerText = "Please select sport."
    'End If

    'End Sub

    Private Sub SportDDL2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SportDDL2.SelectedIndexChanged

        If Not SeasonDDL1.SelectedValue.Equals("Select Season") Then
            If Not SportDDL2.SelectedValue.Equals("Select Sport") Then
                Dim SportDDL2Value As String
                SportDDL2Value = SportDDL2.SelectedValue.ToString().Replace("&", "38")
                Call SetDownloadLinkSeason("Athletic", SportDDL2.SelectedValue, SeasonDDL1.SelectedValue, DownloadLabel2)
                If DownloadLabel2.Text.Equals("0") Then
                    DownloadLBtn2.HRef = ""
                Else
                    DownloadLBtn2.HRef = "http://sms.allsaints.cc/CYOLiabilityForm/tabid/134/Default.aspx?download=true&type=season&sport=" & SportDDL2Value & "&season=" & SeasonDDL1.SelectedValue & "&table=both"
                End If

                DownloadCYOlbl2.Text = "-1"
                Call SetDownloadLinkSeason("Athletic", SportDDL2.SelectedValue, SeasonDDL1.SelectedValue, DownloadCYOlbl2)
                If DownloadCYOlbl2.Text.Equals("0") Then
                    DownloadCYO2.HRef = ""
                Else
                    DownloadCYO2.HRef = "http://sms.allsaints.cc/CYOLiabilityForm/tabid/134/Default.aspx?download=all&type=season&sport=" & SportDDL2Value & "&season=" & SeasonDDL1.SelectedValue & "&table=both&formtype=cyo"
                End If

                DownloadAthleticlbl2.Text = DownloadLabel2.Text
                'Call SetDownloadLinkSeason("Athletic", SportDDL2.SelectedValue, SeasonDDL1.SelectedValue, DownloadAthleticlbl2)
                If DownloadAthleticlbl2.Text.Equals("0") Then
                    DownloadAthletic2.HRef = ""
                Else
                    DownloadAthletic2.HRef = "http://sms.allsaints.cc/CYOLiabilityForm/tabid/134/Default.aspx?download=all&type=season&sport=" & SportDDL2Value & "&season=" & SeasonDDL1.SelectedValue & "&table=both&formtype=athletic"
                End If

                Call SetEmailTextBox2("Athletic", SportDDL2.SelectedValue, SeasonDDL1.SelectedValue, EmailsTB2)
                EmailsTB1.ReadOnly = False
                EmailsLabel1.InnerText = ""

            Else
                DownloadLabel2.Text = "0"
                DownloadLBtn2.HRef = ""

                DownloadCYOlbl2.Text = "0"
                DownloadCYO2.HRef = ""

                DownloadAthleticlbl2.Text = "0"
                DownloadAthletic2.HRef = ""

                EmailsTB2.Text = ""
                EmailsTB2.ReadOnly = True
                EmailsLabel2.InnerText = "Please select sport."
            End If
        Else
            DownloadLabel2.Text = "0"
            DownloadLBtn2.HRef = ""

            DownloadCYOlbl2.Text = "0"
            DownloadCYO2.HRef = ""

            DownloadAthleticlbl2.Text = "0"
            DownloadAthletic2.HRef = ""

            EmailsTB2.Text = ""
            EmailsTB2.ReadOnly = True
            EmailsLabel2.InnerText = "Please select season."
        End If

    End Sub

    Private Sub SeasonDDL1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SeasonDDL1.SelectedIndexChanged

        If Not SportDDL2.SelectedValue.Equals("Select Sport") Then
            If Not SeasonDDL1.SelectedValue.Equals("Select Season") Then
                Dim SportDDL2Value As String
                SportDDL2Value = SportDDL2.SelectedValue.ToString().Replace("&", "38")
                Call SetDownloadLinkSeason("Athletic", SportDDL2.SelectedValue, SeasonDDL1.SelectedValue, DownloadLabel2)
                If DownloadLabel2.Text.Equals("0") Then
                    DownloadLBtn2.HRef = ""
                Else
                    DownloadLBtn2.HRef = "http://sms.allsaints.cc/CYOLiabilityForm/tabid/134/Default.aspx?download=true&type=season&sport=" & SportDDL2Value & "&season=" & SeasonDDL1.SelectedValue & "&table=both"
                End If

                DownloadCYOlbl2.Text = "-1"
                Call SetDownloadLinkSeason("Athletic", SportDDL2.SelectedValue, SeasonDDL1.SelectedValue, DownloadCYOlbl2)
                If DownloadCYOlbl2.Text.Equals("0") Then
                    DownloadCYO2.HRef = ""
                Else
                    DownloadCYO2.HRef = "http://sms.allsaints.cc/CYOLiabilityForm/tabid/134/Default.aspx?download=all&type=season&sport=" & SportDDL2Value & "&season=" & SeasonDDL1.SelectedValue & "&table=both&formtype=cyo"
                End If

                DownloadAthleticlbl2.Text = DownloadLabel2.Text
                'Call SetDownloadLinkSeason("Athletic", SportDDL2.SelectedValue, SeasonDDL1.SelectedValue, DownloadAthleticlbl2)
                If DownloadAthleticlbl2.Text.Equals("0") Then
                    DownloadAthletic2.HRef = ""
                Else
                    DownloadAthletic2.HRef = "http://sms.allsaints.cc/CYOLiabilityForm/tabid/134/Default.aspx?download=all&type=season&sport=" & SportDDL2Value & "&season=" & SeasonDDL1.SelectedValue & "&table=both&formtype=athletic"
                End If

                Call SetEmailTextBox2("Athletic", SportDDL2.SelectedValue, SeasonDDL1.SelectedValue, EmailsTB2)
                EmailsTB2.ReadOnly = False
                EmailsLabel2.InnerText = ""
            Else
                DownloadLabel2.Text = "0"
                DownloadLBtn2.HRef = ""

                DownloadCYOlbl2.Text = "0"
                DownloadCYO2.HRef = ""

                DownloadAthleticlbl2.Text = "0"
                DownloadAthletic2.HRef = ""

                EmailsTB2.Text = ""
                EmailsTB2.ReadOnly = True
                EmailsLabel2.InnerText = "Please select season."
            End If
        Else
            DownloadLabel2.Text = "0"
            DownloadLBtn2.HRef = ""

            DownloadCYOlbl2.Text = "0"
            DownloadCYO2.HRef = ""

            DownloadAthleticlbl2.Text = "0"
            DownloadAthletic2.HRef = ""

            EmailsTB2.Text = ""
            EmailsTB2.ReadOnly = True
            EmailsLabel2.InnerText = "Please select sport."
        End If

    End Sub


    Private Sub SportDDL3_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SportDDL3.SelectedIndexChanged

        If GradeDDL2.SelectedIndex > 0 Then
            If Not SportDDL3.SelectedValue.Equals("Select Sport") Then

                Dim grade As String = ""
                Dim grade1 As String = ""
                If Not (GradeDDL2.Items(1).Selected = True) Then
                    For i As Integer = 0 To GradeDDL2.Items.Count - 1
                        If Not (GradeDDL2.Items(i).Text.Equals("Select Grade")) Then
                            If GradeDDL2.Items(i).Selected Then
                                If (grade.Equals("")) Then
                                    grade += "gradeddl = '" & GradeDDL2.Items(i).Text & "'"
                                Else
                                    grade += "or gradeddl = '" & GradeDDL2.Items(i).Text & "'"
                                End If

                                If grade1.Equals("") Then
                                    grade1 = GradeDDL2.Items(i).Text.Trim()
                                Else
                                    grade1 += "," & GradeDDL2.Items(i).Text.Trim()
                                End If
                            End If
                        End If
                    Next
                Else
                    For i As Integer = 2 To GradeDDL2.Items.Count - 1
                        If Not (GradeDDL2.Items(i).Text.Equals("Select Grade")) Then
                            If (grade.Equals("")) Then
                                grade += "gradeddl = '" & GradeDDL2.Items(i).Text & "'"
                            Else
                                grade += "or gradeddl = '" & GradeDDL2.Items(i).Text & "'"
                            End If

                            If grade1.Equals("") Then
                                grade1 = GradeDDL2.Items(i).Text.Trim()
                            Else
                                grade1 += "," & GradeDDL2.Items(i).Text.Trim()
                            End If
                        End If
                    Next
                End If
                Dim SportDDL3Value As String
                SportDDL3Value = SportDDL3.SelectedValue.ToString().Replace("&", "38")
                Call SetDownloadLinkGrade("Coaching", SportDDL3.SelectedValue, grade, DownloadLabel3)
                If DownloadLabel3.Text.Equals("0") Then
                    DownloadLBtn3.HRef = ""
                Else
                    DownloadLBtn3.HRef = "http://sms.allsaints.cc/CoachForm/tabid/133/Default.aspx?download=true&type=grade&sport=" & SportDDL3Value & "&grade=" & HttpUtility.UrlEncode(grade1) & "&table=both"
                End If
                Call SetEmailTextBox("Coaching", SportDDL3.SelectedValue, GradeDDL2, EmailsTB3)
                EmailsTB3.ReadOnly = False
                EmailsLabel3.InnerText = ""
            Else
                DownloadLabel3.Text = "0"
                DownloadLBtn3.HRef = ""
                EmailsTB3.Text = ""
                EmailsTB3.ReadOnly = False
                EmailsLabel3.InnerText = "Please select sport."
            End If
        Else
            DownloadLabel3.Text = "0"
            DownloadLBtn3.HRef = ""
            EmailsTB3.Text = ""
            EmailsTB3.ReadOnly = False
            EmailsLabel3.InnerText = "Please select grade."
        End If

    End Sub

    'Private Sub GradeDDL2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GradeDDL2.SelectedIndexChanged

    '   If Not SportDDL3.SelectedValue.Equals("Select Sport") Then
    '          If Not GradeDDL2.SelectedValue.Equals("Select Grade") Then
    '
    '               Call SetDownloadLinkGrade("Coaching", SportDDL3.SelectedValue, GradeDDL2.SelectedValue, DownloadLabel3)
    '             If DownloadLabel3.Text.Equals("0") Then
    '                DownloadLBtn3.HRef = ""
    '           Else
    '              DownloadLBtn3.HRef = "http://sms.allsaints.cc/CoachForm/tabid/133/Default.aspx?download=true&type=grade&sport=" & SportDDL3.SelectedValue & "&grade=" & GradeDDL2.SelectedValue
    '         End If
    '
    '               Call SetEmailTextBox("Coaching", SportDDL3.SelectedValue, GradeDDL2.SelectedValue, EmailsTB3)
    '              EmailsTB3.ReadOnly = False
    '             EmailsLabel3.InnerText = ""
    '        Else
    '           DownloadLabel3.Text = "0"
    '          DownloadLBtn3.HRef = ""
    '         EmailsTB3.Text = ""
    '        EmailsTB3.ReadOnly = True
    '       EmailsLabel3.InnerText = "Please select grade."
    '  End If
    '        Else
    '           DownloadLabel3.Text = "0"
    '          DownloadLBtn3.HRef = ""
    '         EmailsTB3.Text = ""
    '        EmailsTB3.ReadOnly = True
    '       EmailsLabel3.InnerText = "Please select sport."
    '  End If

    'End Sub

    Private Sub SportDDL4_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SportDDL4.SelectedIndexChanged

        If Not SeasonDDL2.SelectedValue.Equals("Select Season") Then
            If Not SportDDL4.SelectedValue.Equals("Select Sport") Then
                Dim SportDDL4Value As String
                SportDDL4Value = SportDDL4.SelectedValue.ToString().Replace("&", "38")
                Call SetDownloadLinkSeason("Evaluate", SportDDL4.SelectedValue, SeasonDDL2.SelectedValue, DownloadLabel4)
                If DownloadLabel4.Text.Equals("0") Then
                    DownloadLBtn4.HRef = ""
                Else
                    DownloadLBtn4.HRef = "http://sms.allsaints.cc/SportsEvaluationForm/tabid/137/Default.aspx?download=true&type=season&sport=" & SportDDL4Value & "&season=" & SeasonDDL2.SelectedValue & "&table=both"
                End If
                Call SetEmailTextBox2("Evaluate", SportDDL4.SelectedValue, SeasonDDL2.SelectedValue, EmailsTB4)
                EmailsTB4.ReadOnly = False
                EmailsLabel4.InnerText = ""
            Else
                DownloadLabel4.Text = "0"
                DownloadLBtn4.HRef = ""
                EmailsTB4.Text = ""
                EmailsTB4.ReadOnly = True
                EmailsLabel4.InnerText = "Please select sport."
            End If
        Else
            DownloadLabel4.Text = "0"
            DownloadLBtn4.HRef = ""
            EmailsTB4.Text = ""
            EmailsTB4.ReadOnly = True
            EmailsLabel4.InnerText = "Please select season."
        End If

    End Sub

    Private Sub SeasonDDL2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SeasonDDL2.SelectedIndexChanged

        If Not SportDDL4.SelectedValue.Equals("Select Sport") Then
            If Not SeasonDDL2.SelectedValue.Equals("Select Season") Then
                Dim SportDDL4Value As String
                SportDDL4Value = SportDDL4.SelectedValue.ToString().Replace("&", "38")
                Call SetDownloadLinkSeason("Evaluate", SportDDL4.SelectedValue, SeasonDDL2.SelectedValue, DownloadLabel4)
                If DownloadLabel4.Text.Equals("0") Then
                    DownloadLBtn4.HRef = ""
                Else
                    DownloadLBtn4.HRef = "http://sms.allsaints.cc/SportsEvaluationForm/tabid/137/Default.aspx?download=true&type=season&sport=" & SportDDL4Value & "&season=" & SeasonDDL2.SelectedValue & "&table=both"
                End If
                Call SetEmailTextBox2("Evaluate", SportDDL4.SelectedValue, SeasonDDL2.SelectedValue, EmailsTB4)
                EmailsTB4.ReadOnly = False
                EmailsLabel4.InnerText = ""

            Else
                DownloadLabel4.Text = "0"
                DownloadLBtn4.HRef = ""
                EmailsTB4.Text = ""
                EmailsTB4.ReadOnly = True
                EmailsLabel4.InnerText = "Please select season."
            End If
        Else
            DownloadLabel4.Text = "0"
            DownloadLBtn4.HRef = ""
            EmailsTB4.Text = ""
            EmailsTB4.ReadOnly = True
            EmailsLabel4.InnerText = "Please select sport."
        End If

    End Sub

    Private Sub UpdateBtn1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateBtn1.Click

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        If Not SportDDL_Grade1.SelectedValue = "Select Sport" Then
            Dim grade As String = ""
            If Not (GradeDDL1.Items(1).Selected = True) Then
                For i As Integer = 0 To GradeDDL1.Items.Count - 1
                    If Not (GradeDDL1.Items(i).Text.Equals("Select Grade")) Then
                        If (GradeDDL1.Items(i).Selected = True) Then

                            grade = GradeDDL1.Items(i).Text
                            Dim cmd As New SqlCommand
                            With cmd

                                Dim cmd2 As New SqlCommand
                                Dim value As Integer = 0

                                cmd2.CommandText = "select ID from as_FormEmails where formtype='Athletic' and sport='" & SportDDL_Grade1.SelectedValue & "' and grade='" & grade & "'"
                                cmd2.CommandType = CommandType.Text
                                If Conn.State = ConnectionState.Closed Then Conn.Open()
                                cmd2.Connection = Conn
                                value = CType(cmd2.ExecuteScalar(), Integer)

                                If Not (value = 0) Then
                                    If (EmailsTB1.Text.Equals("")) Then
                                        .CommandText = "delete from as_FormEmails where formtype='Athletic' and sport='" & SportDDL_Grade1.SelectedValue & "' and grade='" & grade & "'"
                                    Else
                                        .CommandText = "update as_FormEmails set Emails = '" & EmailsTB1.Text.Trim().Replace("'", "''") & "' where formtype='Athletic' and sport='" & SportDDL_Grade1.SelectedValue & "' and grade='" & grade & "'"
                                    End If
                                Else
                                    .CommandText = "insert into as_FormEmails values('Athletic','" & SportDDL_Grade1.SelectedValue & "','" & grade & "','-','" & EmailsTB1.Text.Trim().Replace("'", "''") & "')"
                                End If

                                .CommandType = CommandType.Text
                                If Conn.State = ConnectionState.Closed Then Conn.Open()
                                .Connection = Conn
                                .ExecuteNonQuery()

                            End With
                        End If
                    Else
                        EmailsLabel1.InnerText = "Please select grade first."
                    End If
                Next
            Else
                For i As Integer = 2 To GradeDDL1.Items.Count - 1
                    If Not (GradeDDL1.Items(i).Text.Equals("Select Grade")) Then
                        grade = GradeDDL1.Items(i).Text
                        Dim cmd As New SqlCommand
                        With cmd

                            Dim cmd2 As New SqlCommand
                            Dim value As Integer = 0

                            cmd2.CommandText = "select ID from as_FormEmails where formtype='Athletic' and sport='" & SportDDL_Grade1.SelectedValue & "' and grade='" & grade & "'"
                            cmd2.CommandType = CommandType.Text
                            If Conn.State = ConnectionState.Closed Then Conn.Open()
                            cmd2.Connection = Conn
                            value = CType(cmd2.ExecuteScalar(), Integer)

                            If Not (value = 0) Then
                                If (EmailsTB1.Text.Equals("")) Then
                                    .CommandText = "delete from as_FormEmails where formtype='Athletic' and sport='" & SportDDL_Grade1.SelectedValue & "' and grade='" & grade & "'"
                                Else
                                    .CommandText = "update as_FormEmails set Emails = '" & EmailsTB1.Text.Trim().Replace("'", "''") & "' where formtype='Athletic' and sport='" & SportDDL_Grade1.SelectedValue & "' and grade='" & grade & "'"
                                End If
                            Else
                                .CommandText = "insert into as_FormEmails values('Athletic','" & SportDDL_Grade1.SelectedValue & "','" & grade & "','-','" & EmailsTB1.Text.Trim().Replace("'", "''") & "')"
                            End If

                            .CommandType = CommandType.Text
                            If Conn.State = ConnectionState.Closed Then Conn.Open()
                            .Connection = Conn
                            .ExecuteNonQuery()

                        End With
                    Else
                        EmailsLabel1.InnerText = "Please select grade first."
                    End If
                Next
            End If
        Else
            EmailsLabel1.InnerText = "Please select sport."
        End If
        If Conn.State = ConnectionState.Open Then Conn.Close()

    End Sub

    Private Sub UpdateBtn2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateBtn2.Click

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        Dim cmd As New SqlCommand
        With cmd

            Dim cmd2 As New SqlCommand
            Dim value As Integer = 0

            cmd2.CommandText = "select ID from as_FormEmails where formtype='Athletic' and sport='" & SportDDL2.SelectedValue & "' and season='" & SeasonDDL1.SelectedValue & "'"
            cmd2.CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            cmd2.Connection = Conn
            value = CType(cmd2.ExecuteScalar(), Integer)

            If Not (value = 0) Then
                If (EmailsTB2.Text.Equals("")) Then
                    .CommandText = "delete from as_FormEmails where formtype='Athletic' and sport='" & SportDDL2.SelectedValue & "' and season='" & SeasonDDL1.SelectedValue & "'"
                Else
                    .CommandText = "update as_FormEmails set Emails = '" & EmailsTB2.Text.Trim().Replace("'", "''") & "' where formtype='Athletic' and sport='" & SportDDL2.SelectedValue & "' and season='" & SeasonDDL1.SelectedValue & "'"
                End If
            Else
                .CommandText = "insert into as_FormEmails values('Athletic','" & SportDDL2.SelectedValue & "','-','" & SeasonDDL1.SelectedValue & "','" & EmailsTB2.Text.Trim().Replace("'", "''") & "')"
            End If

            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            .ExecuteNonQuery()

        End With

        If Conn.State = ConnectionState.Open Then Conn.Close()

    End Sub

    Private Sub UpdateBtn3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateBtn3.Click

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        Dim grade As String = ""

        If Not SportDDL3.SelectedValue = "Select Sport" Then
            If Not (GradeDDL2.Items(1).Selected = True) Then
                For i As Integer = 0 To GradeDDL2.Items.Count - 1
                    If Not (GradeDDL2.Items(i).Text.Equals("Select Grade")) Then
                        If (GradeDDL2.Items(i).Selected = True) Then

                            grade = GradeDDL2.Items(i).Text
                            Dim cmd As New SqlCommand
                            With cmd

                                Dim cmd2 As New SqlCommand
                                Dim value As Integer = 0

                                cmd2.CommandText = "select ID from as_FormEmails where formtype='Coaching' and sport='" & SportDDL3.SelectedValue & "' and grade='" & grade & "'"
                                cmd2.CommandType = CommandType.Text
                                If Conn.State = ConnectionState.Closed Then Conn.Open()
                                cmd2.Connection = Conn
                                value = CType(cmd2.ExecuteScalar(), Integer)

                                If Not (value = 0) Then
                                    If (EmailsTB3.Text.Equals("")) Then
                                        .CommandText = "delete from as_FormEmails where formtype='Coaching' and sport='" & SportDDL3.SelectedValue & "' and grade='" & grade & "'"
                                    Else
                                        .CommandText = "update as_FormEmails set Emails = '" & EmailsTB3.Text.Trim().Replace("'", "''") & "' where formtype='Coaching' and sport='" & SportDDL3.SelectedValue & "' and grade='" & grade & "'"
                                    End If
                                Else
                                    .CommandText = "insert into as_FormEmails values('Coaching','" & SportDDL3.SelectedValue & "','" & grade & "','-','" & EmailsTB3.Text.Trim().Replace("'", "''") & "')"
                                End If

                                .CommandType = CommandType.Text
                                If Conn.State = ConnectionState.Closed Then Conn.Open()
                                .Connection = Conn
                                .ExecuteNonQuery()

                            End With
                        End If
                    Else
                        EmailsLabel3.InnerText = "Please select grade first."
                    End If
                Next
            Else
                For i As Integer = 2 To GradeDDL2.Items.Count - 1
                    If Not (GradeDDL2.Items(i).Text.Equals("Select Grade")) Then

                        grade = GradeDDL2.Items(i).Text
                        Dim cmd As New SqlCommand
                        With cmd

                            Dim cmd2 As New SqlCommand
                            Dim value As Integer = 0

                            cmd2.CommandText = "select ID from as_FormEmails where formtype='Coaching' and sport='" & SportDDL3.SelectedValue & "' and grade='" & grade & "'"
                            cmd2.CommandType = CommandType.Text
                            If Conn.State = ConnectionState.Closed Then Conn.Open()
                            cmd2.Connection = Conn
                            value = CType(cmd2.ExecuteScalar(), Integer)

                            If Not (value = 0) Then
                                If (EmailsTB3.Text.Equals("")) Then
                                    .CommandText = "delete from as_FormEmails where formtype='Coaching' and sport='" & SportDDL3.SelectedValue & "' and grade='" & grade & "'"
                                Else
                                    .CommandText = "update as_FormEmails set Emails = '" & EmailsTB3.Text.Trim().Replace("'", "''") & "' where formtype='Coaching' and sport='" & SportDDL3.SelectedValue & "' and grade='" & grade & "'"
                                End If
                            Else
                                .CommandText = "insert into as_FormEmails values('Coaching','" & SportDDL3.SelectedValue & "','" & grade & "','-','" & EmailsTB3.Text.Trim().Replace("'", "''") & "')"
                            End If

                            .CommandType = CommandType.Text
                            If Conn.State = ConnectionState.Closed Then Conn.Open()
                            .Connection = Conn
                            .ExecuteNonQuery()

                        End With
                    Else
                        EmailsLabel3.InnerText = "Please select grade first."
                    End If
                Next
            End If
        Else
            EmailsLabel3.InnerText = "Please select sport."
        End If
        If Conn.State = ConnectionState.Open Then Conn.Close()

    End Sub

    Private Sub UpdateBtn4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateBtn4.Click

        If Conn.State = ConnectionState.Closed Then
            Conn.ConnectionString = ConfigurationSettings.AppSettings("sConnection")
        End If

        Dim cmd As New SqlCommand
        With cmd

            Dim cmd2 As New SqlCommand
            Dim value As Integer = 0

            cmd2.CommandText = "select ID from as_FormEmails where formtype='Evaluate' and sport='" & SportDDL4.SelectedValue & "' and season='" & SeasonDDL2.SelectedValue & "'"
            cmd2.CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            cmd2.Connection = Conn
            value = CType(cmd2.ExecuteScalar(), Integer)

            If Not (value = 0) Then
                If (EmailsTB4.Text.Equals("")) Then
                    .CommandText = "delete from as_FormEmails where formtype='Evaluate' and sport='" & SportDDL4.SelectedValue & "' and season='" & SeasonDDL2.SelectedValue & "'"
                Else
                    .CommandText = "update as_FormEmails set Emails = '" & EmailsTB4.Text.Trim().Replace("'", "''") & "' where formtype='Evaluate' and sport='" & SportDDL4.SelectedValue & "' and season='" & SeasonDDL2.SelectedValue & "'"
                End If
            Else
                .CommandText = "insert into as_FormEmails values('Evaluate','" & SportDDL4.SelectedValue & "','-','" & SeasonDDL2.SelectedValue & "','" & EmailsTB4.Text.Trim().Replace("'", "''") & "')"
            End If

            .CommandType = CommandType.Text
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            .Connection = Conn
            .ExecuteNonQuery()

        End With

        If Conn.State = ConnectionState.Open Then Conn.Close()

    End Sub
End Class


'<Sohaib Akram - Aug 16, 2011 - End>