GESTION ECOLE ISGI MARRAKECH BY OUKOUJANE EL MUSTAPHA

Utiliser le nom d’espace suivant
Imports System.Data.SqlClient


  Dans l’Evenement session_start du fichier global.asax

Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
        Dim cn As New SqlClient.SqlConnection
        Session("con") = cn

      cn.ConnectionString = "server=(local);integrated security=sspi;initial catalog=ecole"

        cn.Open()
    End Sub


Dans l’Evenement Evenement session_end du fichier global.asax

Sub Session_End(ByVal sender As Object, ByVal e As EventArgs)

        CType(Session("con"), SqlConnection).Close()

    End Sub


Dans l’Evenement  load de la Page de démarrage

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

            Try


                Dim dr As SqlDataReader
                Dim cmd As New SqlCommand
                cmd.CommandText = "select * from classes"
                cmd.Connection = CType(Session("con"), SqlConnection)

                dr = cmd.ExecuteReader
                While dr.Read
                    DropDownList1.Items.Add(dr(0) & "  : " & dr(1))
                    DropDownList1.DataBind()

                End While
                dr.Close()
            Catch ex As Exception
                Label2.Text = ("erreur :" & ex.Message)
            End Try
        End If
    End Sub

Dans l’Evenement linkbutton1_click

Private Sub LinkButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
        Try
            Dim nr As String
            nr = DropDownList1.SelectedItem.Value
            Dim ch() As String = nr.Split(":")


            Dim cmd As New SqlCommand
            Dim dr As SqlDataReader
            cmd.CommandText = "select * from eleves where id_classe=" & "'" & ch(0) & "'"
            cmd.Connection = CType(Session("con"), SqlConnection)
            dr = cmd.ExecuteReader
            ListBox1.Items.Clear()
            While dr.Read
                ListBox1.Items.Add(dr(1))
                ListBox1.DataBind()
            End While
            dr.Close()

            Label3.Text = ListBox1.Items.Count & " :" & " Etudiants dans la classe    " & ch(1)
        Catch ex As Exception
            Label2.Text = ("erreur: " & ex.Message)
        End Try
    End Sub

Dans l’Evenement  load :

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            Dim cmdc As New SqlCommand
            Dim cmdmat As New SqlCommand
            cmdc.CommandText = "select * from classes "
            cmdmat.CommandText = "select * from matieres "
            cmdc.Connection = CType(Session("con"), SqlConnection)
            cmdmat.Connection = CType(Session("con"), SqlConnection)
            Dim drc As SqlDataReader
            drc = cmdc.ExecuteReader
            While drc.Read
                listeclasses.Items.Add(drc(0) & " : " & drc(1))
            End While
            drc.Close()
            Dim drmat As SqlDataReader
            drmat = cmdmat.ExecuteReader
            While drmat.Read
                listematieres.Items.Add(drmat(0) & " : " & drmat(1))
            End While

            drmat.Close()
        End If
    End Sub


Evénement l LinkButton1_Click:

Private Sub LinkButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
        Dim moy As Double = 0
        Dim min As Double = 20
        Dim max As Double = 0
        Dim ecart As Double = 0
        Dim sw, sz As Double
        Dim etudmax, etudmin As String
        'on recupere l'element sélectionne dans le combos 
        'concernant les classes mais sous forme 1:tsdi3
        Dim idcl As String = listeclasses.SelectedItem.Value
        'de meme pour les matieres
        Dim idmat As String = listematieres.SelectedItem.Value
        'il faut decouper la chaine et la mettre dans un tableau
        'a l'aide de la methode split
        Dim tabcl() As String = idcl.Split(":")
        Dim tabmat() As String = idmat.Split(":")
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "meseleves"
‘meselves est une procedure stockee sur sql server comme suivant:
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’procédure stockée’’’’’’’’’’’’
CREATE proc meseleves(@idclasse int,@idmatiere int) as
select eleves.prenom,eleves.nom,notes.note from notes 
inner join ( eleves inner join classes on classes.id=eleves.id_classe)
on eleves.id=notes.id_eleve
where notes.id_matiere=@idmatiere and classes.id=@idclasse 
GO
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
        Dim p1 As New SqlParameter("@idclasse", SqlDbType.Int)
        Dim p2 As New SqlParameter("@idmatiere", SqlDbType.Int)
        p1.Value = tabcl(0)
        p2.Value = tabmat(0)
        cmd.Parameters.Add(p1)
        cmd.Parameters.Add(p2)
        cmd.Connection = CType(Session("con"), SqlConnection)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader
        ListBox1.Items.Clear()
        While dr.Read

            ListBox1.Items.Add(dr(0) & "  " & dr(1) & " : " & dr(2))
            ecart = ecart + dr(2) * dr(2)
            If dr(2) > max Then
                max = dr(2)
                etudmax = dr(1) & "  " & dr(0)


            End If
           
            If dr(2) <= min Then
                min = dr(2)
                etudmin = dr(1) & "  " & dr(0)

            End If
            moy = moy + dr(2)


        End While

       







            sw = moy * moy
            sz = ListBox1.Items.Count * ListBox1.Items.Count
            Label7.Text = moy / ListBox1.Items.Count
            Label8.Text = ecart / ListBox1.Items.Count - sw / sz
            Label9.Text = max & "  obtenue par :" & etudmax
            Label10.Text = min & "  obtenue par :" & etudmin



        dr.Close()

    End Sub
Dans événement  load de la page courante mettre :

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            Dim cmdc As New SqlCommand
            Dim cmdmat As New SqlCommand
            cmdc.CommandText = "select * from classes "
            cmdc.Connection = CType(Session("con"), SqlConnection)
            Dim drc As SqlDataReader
            drc = cmdc.ExecuteReader
            While drc.Read
                listeclasses.Items.Add(drc(0) & " : " & drc(1))
                
            End While
            drc.Close()
            
        End If
    End Sub
Dans l’événement linkboutton (afficher les élèves):

Private Sub LinkButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton2.Click
        listeeleves.Items.Clear()
        Dim d As String = listeclasses.SelectedItem.Value
        Dim sss() As String = d.split(":")
        Dim cmd As New SqlCommand
        Dim cmdmat As New SqlCommand
        cmd.CommandText = "select * from eleves where id_classe=" & sss(0)
        cmd.Connection = CType(Session("con"), SqlConnection)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            listeeleves.Items.Add(dr(0) & " : " & dr(1))
        End While
        dr.Close()
    End Sub

Dans l’événement linkboutton (afficher les notes):

Private Sub LinkButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
        Dim moy As Double = 0
        Dim sw, sz As Double
        Dim etudmax, etudmin As String
        'On recupere l'element selectionne dans le combos 
        'Concernant les classes mais sous forme 1:tsdi3
        Dim idcl As String = listeclasses.SelectedItem.Value
        'de meme pour les matieres
        Dim idelv As String = listeeleves.SelectedItem.Value
        'Il faut decouper la chaine et la mettre dans un tableau
        'a l'aide de la methode split
        Dim tabcl() As String = idcl.Split(":")
        Dim tabelv() As String = idelv.Split(":")
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "mesnotes"
           ’’’’’’’’’’’ps :mesnotes’’’’’’’’’’’’’’’’’’’’’
CREATE proc mesnotes(@idclasse int,@ideleve int) as
Select matieres.nom,notes.note from matieres  inner join (notes inner join eleves on eleves.id=notes.id_eleve)
on matieres.id=notes.id_matiere
where eleves.id=@ideleve and eleves.id_classe=@idclasse 
GO
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
        Dim p1 As New SqlParameter("@idclasse", SqlDbType.Int)
        Dim p2 As New SqlParameter("@ideleve", SqlDbType.Int)
        p1.Value = tabcl(0)
        p2.Value = tabelv(0)
        cmd.Parameters.Add(p1)
        cmd.Parameters.Add(p2)
        cmd.Connection = CType(Session("con"), SqlConnection)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader
        ListBox1.Items.Clear()
        While dr.Read
          ListBox1.Items.Add(dr(0) & " : " & dr(1))
             moy = moy + dr(1)
 End While
Label7.Text = moy / ListBox1.Items.Count
        dr.Close()

    End Sub

Télécharger le zip