Exercice VB: Tp Gestion Stagiaires Simplifié

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Imports System.Data.SqlClient.SqlDataReader
Imports System.Data.SqlClient
Module Module1
    Public cn As New SqlConnection("data source=Exercicesgratuit\SQLEXPRESS;initial catalog=Exercicesgratuit;integrated security=true")
    Public cmd As New SqlCommand
    Public da As SqlDataAdapter
    Public ds As New DataSet
    Public dt As New DataTable
    Public bind As New BindingSource
    Public dtr As DataRelation
    Public dr As DataRow
    Public cm As CurrencyManager
    Public dr1 As SqlDataReader
    Sub inni()
        da = New SqlDataAdapter("select * from sta", cn)
        da.Fill(ds, "sta")
        dt = ds.Tables("sta")
        dt.PrimaryKey = New DataColumn() {dt.Columns.Item(0)}
    End Sub
End Module

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181

Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Data
Imports System
Public Class Form1
    Dim pos As Integer
    Dim d As DataRow
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        End
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        inni()
        ds.Clear()
        cn.Open()
        da = New SqlDataAdapter("select * from sta", cn)
        da.TableMappings.Add("sta", "sta")
        da.Fill(ds, "sta")
        bind.DataSource = ds.Tables("sta")
        DataGridView1.DataSource = bind
        cn.Close()
    End Sub

    Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click
        txtname.Text = ""
        txtnom.Text = ""
        txtpre.Text = ""
        txtad.Text = ""
        txtname.Focus()
    End Sub
    Sub vider()
        txtname.Text = ""
        txtnom.Text = ""
        txtpre.Text = ""
        txtad.Text = ""
        txtname.Focus()
    End Sub
    Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
        If MsgBox("Voulez vous vraiment quitter l'application", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
            End
        End If
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Try
            da.InsertCommand = New SqlCommand("insert into sta (num,nom,prenom,adress) values(@a,@b,@c,@d)", cn)
            da.InsertCommand.Parameters.Add("@a", SqlDbType.Int, 10, "num")
            da.InsertCommand.Parameters.Add("@b", SqlDbType.VarChar, 50, "nom")
            da.InsertCommand.Parameters.Add("@c", SqlDbType.VarChar, 50, "prenom")
            da.InsertCommand.Parameters.Add("@d", SqlDbType.Text, 50, "adress")
            d = dt.NewRow
            d("num") = txtname.Text
            d("nom") = txtnom.Text
            d("prenom") = txtpre.Text
            d("adress") = txtpre.Text
            Try
                dt.PrimaryKey = New DataColumn() {dt.Columns.Item(0)}
                dt.Rows.Add(d)
            Catch ex As Exception
                MsgBox("Se Num existe déjà", MsgBoxStyle.Information)
                Exit Sub
            End Try
            da.Update(ds, "sta")
            MsgBox("Ajouter avec Succée")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Try
            da.UpdateCommand = New SqlCommand("update sta set nom=@b,prenom=@c,adress=@d where num=@a", cn)
            da.UpdateCommand.Parameters.AddWithValue("@a", txtname.Text)
            da.UpdateCommand.Parameters.AddWithValue("@b", txtnom.Text)
            da.UpdateCommand.Parameters.AddWithValue("@c", txtpre.Text)
            da.UpdateCommand.Parameters.AddWithValue("@d", txtad.Text)
            Try
                cn.Open()
                da.UpdateCommand.ExecuteNonQuery()
                da.Update(ds, "sta")
                MsgBox("Modification avec succée", MsgBoxStyle.Information)
                vider()
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                cn.Close()
            End Try
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        If MsgBox("Voulez vous vraiment supprimer cette Personne de la liste", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
            Try
                Dim dra As DataRow()
                da.DeleteCommand = New SqlCommand("Delete from sta where num=@a", cn)
                da.DeleteCommand.Parameters.Add("@a", SqlDbType.Int, 10, "num")
                dt = ds.Tables("sta")
                dra = dt.Select("num=" & txtname.Text)
                Try
                    dt.PrimaryKey = New DataColumn() {dt.Columns.Item(0)}
                    dra(0).Delete()
                Catch ex As Exception
                    MsgBox("Cette personne n'existe pas", MsgBoxStyle.Information)
                    Exit Sub
                End Try
                da.Update(ds, "sta")
                MsgBox("Suppression avec succéé", MsgBoxStyle.Information)
                vider()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If

    End Sub

    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        Try
            Dim str As String = InputBox("Entrer le numéro du Persone à chercher")
            dr = dt.Rows.Find(str)
            If dr Is Nothing Then
                MsgBox("Se personne n'existe pas")
            Else
                With dr
                    txtname.Text = .Item(0)
                    txtnom.Text = .Item(1)
                    txtpre.Text = .Item(2)
                    txtad.Text = .Item(3)
                End With
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Sub afficher(ByVal i As Integer)
        With dt.Rows(i)
            txtname.Text = .Item(0)
            txtnom.Text = .Item(1)
            txtpre.Text = .Item(2)
            txtad.Text = .Item(3)
        End With
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If dt.Rows.Count > 0 Then
            pos = 0
            afficher(pos)
            TextBox5.Text = pos + 1
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If pos > 0 Then
            pos -= 1
            afficher(pos)
            TextBox5.Text = pos + 1
        End If
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        If pos < dt.Rows.Count - 1 Then
            pos += 1
            afficher(pos)
            TextBox5.Text = pos + 1
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        If dt.Rows.Count > 0 Then
            pos = dt.Rows.Count - 1
            afficher(pos)
            TextBox5.Text = pos + 1
        End If
    End Sub

    Private Sub Button12_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button12.Click
        Me.Hide()
        Form2.Show()
    End Sub
End Class