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
|
Private Sub btnImportXml_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportXml.Click
' Displays an OpenFileDialog so the user can select a Cursor.
Dim openFileDialog1 As New OpenFileDialog()
openFileDialog1.InitialDirectory = Application.StartupPath & "\files\"
openFileDialog1.Filter = "xml Files|*.xml"
openFileDialog1.Title = "Sélectionnez un fichier xml"
If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Dim filePath As String = openFileDialog1.FileName
AuthorsDataSet.ReadXml(filePath)
DGV.DataSource = AuthorsDataSet
DGV.DataMember = "EtablissementsPublic"
DGVXMLStyle()
lblCompteXML.Text = DGV.RowCount - 1 & " | " & DGV.ColumnCount - 1
End If
Dim Com As New OleDbCommand
Dim objetCommandDel As OleDbCommand = New OleDbCommand("Delete * from EtablissementsPublic", Cn)
If Cn.State = ConnectionState.Open Then
Cn.Close()
End If
Cn.Open()
objetCommandDel.ExecuteNonQuery()
Cn.Close()
Try
If Cn.State = ConnectionState.Open Then
Cn.Close()
End If
Cn.Open()
Dim strSQL As String
For i As Integer = 0 To DGV.RowCount - 1
strSQL = "INSERT INTO EtablissementsPublic (CD_ETAB, CD_GIPE, NOM_ETABL, NOM_ETABA, CD_OrgTutel, CD_REG, CD_PROV, CD_COM, CD_MIL, CD_CAUTO, AdresseL, AdresseA, CP, VilleL, VilleA, TEL, FAX, [e-mail], CD_NETAB, CD_ETABR, INDHcom, INDHquart, Nbre_Satellites, Directeur, [Directeur Adjoint], [Secrétaire Général], Censeur, [Directeur d'études], [Chef de Travaux], [Econome/Intendant], SurveillantGénInt, [Nombre de SurvGenExt], CIN_Dir, PPR_Dir, NOM_PrenDirL, NOM_PrenDirA, [Préscolaire Moderne], [Primaire Général], [Primaire Originel], [Collégial Général], [Collégial Originel], [Qualifiant Général], [Qualifiant Originel], [Qualifiant Technique], CPGE, BTS, CFCCE, CPCA, CFCAFCA, FormInspecteurs, FormCOPE, FormBS, FP, ConsGestEtab, ConsPédag, ConsEnseig, ConsClas, AssParents, AssSportive, CoopScolaire, Partenariat, ClubSanté, CentreAppui, AssEcolReussite, ProgrammeTissir, ClubEnv, CD_Financ, DC_ETAB, DO_ETAB, DF_ETAB, CD_causesFerm, DistSatEcoM, DistUnitéCol, DistColLyc, Const_Dur, Const_Prefab, Const_Pise, Const_Autre, Eau_Source, Elec_Source, Ass_Source, Clo_Dur, Clo_partie, Clo_grillage, Clo_bois, Clo_autre, Clo_sans, Superf_etab, Superf_Cour, Superf_EspVert, Superf_Exten, Cantine, Cantine_Capacité, Cantine_DO, Cantine_DF, Restauration, Couvert_place, Internat, Internat_Capacité, Internat_DO, Internat_DF, Exist_Internet, upsize_ts, nomL, FP_Bac, ENF, cd_Ass) VALUES (@CD_ETAB, @CD_GIPE, @NOM_ETABL, @NOM_ETABA, @CD_OrgTutel, @CD_REG, @CD_PROV, @CD_COM, @CD_MIL, @CD_CAUTO, @AdresseL, @AdresseA, @CP, @VilleL, @VilleA, @TEL, @FAX, @e_mail, @CD_NETAB, @CD_ETABR, @INDHcom, @INDHquart, @Nbre_Satellites, @Directeur, @Directeur_Adjoint, @Secrétaire_Général, @Censeur, @Directeur_d_études, @Chef_de_Travaux, @Econome_Intendant, @SurveillantGénInt, @Nombre_de_SurvGenExt, @CIN_Dir, @PPR_Dir, @NOM_PrenDirL, @NOM_PrenDirA, @Préscolaire_Moderne, @Primaire_Général, @Primaire_Originel, @Collégial_Général, @Collégial_Originel, @Qualifiant_Général, @Qualifiant_Originel, @Qualifiant_Technique, @CPGE, @BTS, @CFCCE, @CPCA, @CFCAFCA, @FormInspecteurs, @FormCOPE, @FormBS, @FP, @ConsGestEtab, @ConsPédag, @ConsEnseig, @ConsClas, @AssParents, @AssSportive, @CoopScolaire, @Partenariat, @ClubSanté, @CentreAppui, @AssEcolReussite, @ProgrammeTissir, @ClubEnv, @CD_Financ, @DC_ETAB, @DO_ETAB, @DF_ETAB, @CD_causesFerm, @DistSatEcoM, @DistUnitéCol, @DistColLyc, @Const_Dur, @Const_Prefab, @Const_Pise, @Const_Autre, @Eau_Source, @Elec_Source, @Ass_Source, @Clo_Dur, @Clo_partie, @Clo_grillage, @Clo_bois, @Clo_autre, @Clo_sans, @Superf_etab, @Superf_Cour, @Superf_EspVert, @Superf_Exten, @Cantine, @Cantine_Capacité, @Cantine_DO, @Cantine_DF, @Restauration, @Couvert_place, @Internat, @Internat_Capacité, @Internat_DO, @Internat_DF, @Exist_Internet, @upsize_ts, @nomL, @FP_Bac, @ENF, @cd_Ass)"
Com.Connection = Cn
Com.CommandType = CommandType.Text
Com.CommandText = strSQL
'---------------------------------------------------------------------------------------------------------------------------
With Com.Parameters
.AddWithValue("@CD_ETAB", If(IsDBNull(DGV.Item(0, i).Value), DBNull.Value, CStr(DGV.Item(0, i).Value)))
.AddWithValue("@CD_GIPE", If(IsDBNull(DGV.Item(1, i).Value), DBNull.Value, CStr(DGV.Item(1, i).Value)))
.AddWithValue("@NOM_ETABL", If(IsDBNull(DGV.Item(2, i).Value), DBNull.Value, CStr(DGV.Item(2, i).Value)))
.AddWithValue("@NOM_ETABA", If(IsDBNull(DGV.Item(3, i).Value), DBNull.Value, CStr(DGV.Item(3, i).Value)))
.AddWithValue("@CD_OrgTutel", If(IsDBNull(DGV.Item(4, i).Value), DBNull.Value, CStr(DGV.Item(4, i).Value)))
.AddWithValue("@CD_REG", If(IsDBNull(DGV.Item(5, i).Value), DBNull.Value, CStr(DGV.Item(5, i).Value)))
.AddWithValue("@CD_PROV", If(IsDBNull(DGV.Item(6, i).Value), DBNull.Value, CStr(DGV.Item(6, i).Value)))
.AddWithValue("@CD_COM", If(IsDBNull(DGV.Item(7, i).Value), DBNull.Value, CStr(DGV.Item(7, i).Value)))
.AddWithValue("@CD_MIL", If(IsDBNull(DGV.Item(8, i).Value), DBNull.Value, CStr(DGV.Item(8, i).Value)))
.AddWithValue("@CD_CAUTO", If(IsDBNull(DGV.Item(9, i).Value), DBNull.Value, CStr(DGV.Item(9, i).Value)))
.AddWithValue("@AdresseL", If(IsDBNull(DGV.Item(10, i).Value), DBNull.Value, CStr(DGV.Item(10, i).Value)))
.AddWithValue("@AdresseA", If(IsDBNull(DGV.Item(11, i).Value), DBNull.Value, CStr(DGV.Item(11, i).Value)))
.AddWithValue("@CP", If(IsDBNull(DGV.Item(12, i).Value), DBNull.Value, CStr(DGV.Item(12, i).Value)))
.AddWithValue("@VilleL", If(IsDBNull(DGV.Item(13, i).Value), DBNull.Value, CStr(DGV.Item(13, i).Value)))
.AddWithValue("@VilleA", If(IsDBNull(DGV.Item(14, i).Value), DBNull.Value, CStr(DGV.Item(14, i).Value)))
.AddWithValue("@TEL", If(IsDBNull(DGV.Item(15, i).Value), DBNull.Value, CStr(DGV.Item(15, i).Value)))
.AddWithValue("@FAX", If(IsDBNull(DGV.Item(16, i).Value), DBNull.Value, CStr(DGV.Item(16, i).Value)))
.AddWithValue("@e_mail", If(IsDBNull(DGV.Item(17, i).Value), DBNull.Value, CStr(DGV.Item(17, i).Value)))
.AddWithValue("@CD_NETAB", If(IsDBNull(DGV.Item(18, i).Value), DBNull.Value, CStr(DGV.Item(18, i).Value)))
.AddWithValue("@CD_ETABR", If(IsDBNull(DGV.Item(19, i).Value), DBNull.Value, CStr(DGV.Item(19, i).Value)))
.AddWithValue("@INDHcom", If(IsDBNull(DGV.Item(20, i).Value), DBNull.Value, CBool(DGV.Item(20, i).Value)))
.AddWithValue("@INDHquart", If(IsDBNull(DGV.Item(21, i).Value), DBNull.Value, CBool(DGV.Item(21, i).Value)))
.AddWithValue("@Nbre_Satellites", If(IsDBNull(DGV.Item(22, i).Value), DBNull.Value, CInt(DGV.Item(22, i).Value)))
.AddWithValue("@Directeur", If(IsDBNull(DGV.Item(23, i).Value), DBNull.Value, CBool(DGV.Item(23, i).Value)))
.AddWithValue("@Directeur Adjoint", If(IsDBNull(DGV.Item(24, i).Value), DBNull.Value, CBool(DGV.Item(24, i).Value)))
.AddWithValue("@Secrétaire Général", If(IsDBNull(DGV.Item(25, i).Value), DBNull.Value, CBool(DGV.Item(25, i).Value)))
.AddWithValue("@Censeur", If(IsDBNull(DGV.Item(26, i).Value), DBNull.Value, CBool(DGV.Item(26, i).Value)))
.AddWithValue("@Directeur_d_études", If(IsDBNull(DGV.Item(27, i).Value), DBNull.Value, CBool(DGV.Item(27, i).Value)))
.AddWithValue("@Chef_de_Travaux", If(IsDBNull(DGV.Item(28, i).Value), DBNull.Value, CBool(DGV.Item(28, i).Value)))
.AddWithValue("@Econome_Intendant", If(IsDBNull(DGV.Item(29, i).Value), DBNull.Value, CBool(DGV.Item(29, i).Value)))
.AddWithValue("@SurveillantGénInt", If(IsDBNull(DGV.Item(30, i).Value), DBNull.Value, CBool(DGV.Item(30, i).Value)))
.AddWithValue("@Nombre_de_SurvGenExt", If(IsDBNull(DGV.Item(31, i).Value), DBNull.Value, CInt(DGV.Item(31, i).Value)))
.AddWithValue("@CIN_Dir", If(IsDBNull(DGV.Item(32, i).Value), DBNull.Value, CStr(DGV.Item(32, i).Value)))
.AddWithValue("@PPR_Dir", If(IsDBNull(DGV.Item(33, i).Value), DBNull.Value, CLng(DGV.Item(33, i).Value)))
.AddWithValue("@NOM_PrenDirL", If(IsDBNull(DGV.Item(34, i).Value), DBNull.Value, CStr(DGV.Item(34, i).Value)))
.AddWithValue("@NOM_PrenDirA", If(IsDBNull(DGV.Item(35, i).Value), DBNull.Value, CStr(DGV.Item(35, i).Value)))
.AddWithValue("@Préscolaire_Moderne", If(IsDBNull(DGV.Item(36, i).Value), DBNull.Value, CBool(DGV.Item(36, i).Value)))
.AddWithValue("@Primaire_Général", If(IsDBNull(DGV.Item(37, i).Value), DBNull.Value, CBool(DGV.Item(37, i).Value)))
.AddWithValue("@Primaire_Originel", If(IsDBNull(DGV.Item(38, i).Value), DBNull.Value, CBool(DGV.Item(38, i).Value)))
.AddWithValue("@Collégial_Général", If(IsDBNull(DGV.Item(39, i).Value), DBNull.Value, CBool(DGV.Item(39, i).Value)))
.AddWithValue("@Collégial_Originel", If(IsDBNull(DGV.Item(40, i).Value), DBNull.Value, CBool(DGV.Item(40, i).Value)))
.AddWithValue("@Qualifiant_Général", If(IsDBNull(DGV.Item(41, i).Value), DBNull.Value, CBool(DGV.Item(41, i).Value)))
.AddWithValue("@Qualifiant_Originel", If(IsDBNull(DGV.Item(42, i).Value), DBNull.Value, CBool(DGV.Item(42, i).Value)))
.AddWithValue("@Qualifiant_Technique", If(IsDBNull(DGV.Item(43, i).Value), DBNull.Value, CBool(DGV.Item(43, i).Value)))
.AddWithValue("@CPGE", If(IsDBNull(DGV.Item(44, i).Value), DBNull.Value, CBool(DGV.Item(44, i).Value)))
.AddWithValue("@BTS", If(IsDBNull(DGV.Item(45, i).Value), DBNull.Value, CBool(DGV.Item(45, i).Value)))
.AddWithValue("@CFCCE", If(IsDBNull(DGV.Item(46, i).Value), DBNull.Value, CBool(DGV.Item(46, i).Value)))
.AddWithValue("@CPCA", If(IsDBNull(DGV.Item(47, i).Value), DBNull.Value, CBool(DGV.Item(47, i).Value)))
.AddWithValue("@CFCAFCA", If(IsDBNull(DGV.Item(48, i).Value), DBNull.Value, CBool(DGV.Item(48, i).Value)))
.AddWithValue("@FormInspecteurs", If(IsDBNull(DGV.Item(49, i).Value), DBNull.Value, CBool(DGV.Item(49, i).Value)))
.AddWithValue("@FormCOPE", If(IsDBNull(DGV.Item(50, i).Value), DBNull.Value, CBool(DGV.Item(50, i).Value)))
.AddWithValue("@FormBS", If(IsDBNull(DGV.Item(51, i).Value), DBNull.Value, CBool(DGV.Item(51, i).Value)))
.AddWithValue("@FP", If(IsDBNull(DGV.Item(52, i).Value), DBNull.Value, CBool(DGV.Item(52, i).Value)))
.AddWithValue("@ConsGestEtab", If(IsDBNull(DGV.Item(53, i).Value), DBNull.Value, CBool(DGV.Item(53, i).Value)))
.AddWithValue("@ConsPédag", If(IsDBNull(DGV.Item(54, i).Value), DBNull.Value, CBool(DGV.Item(54, i).Value)))
.AddWithValue("@ConsEnseig", If(IsDBNull(DGV.Item(55, i).Value), DBNull.Value, CBool(DGV.Item(55, i).Value)))
.AddWithValue("@ConsClas", If(IsDBNull(DGV.Item(56, i).Value), DBNull.Value, CBool(DGV.Item(56, i).Value)))
.AddWithValue("@AssParents", If(IsDBNull(DGV.Item(57, i).Value), DBNull.Value, CBool(DGV.Item(57, i).Value)))
.AddWithValue("@AssSportive", If(IsDBNull(DGV.Item(58, i).Value), DBNull.Value, CBool(DGV.Item(58, i).Value)))
.AddWithValue("@CoopScolaire", If(IsDBNull(DGV.Item(59, i).Value), DBNull.Value, CBool(DGV.Item(59, i).Value)))
.AddWithValue("@Partenariat", If(IsDBNull(DGV.Item(60, i).Value), DBNull.Value, CBool(DGV.Item(60, i).Value)))
.AddWithValue("@ClubSanté", If(IsDBNull(DGV.Item(61, i).Value), DBNull.Value, CBool(DGV.Item(61, i).Value)))
.AddWithValue("@CentreAppui", If(IsDBNull(DGV.Item(62, i).Value), DBNull.Value, CBool(DGV.Item(62, i).Value)))
.AddWithValue("@AssEcolReussite", If(IsDBNull(DGV.Item(63, i).Value), DBNull.Value, CBool(DGV.Item(63, i).Value)))
.AddWithValue("@ProgrammeTissir", If(IsDBNull(DGV.Item(64, i).Value), DBNull.Value, CBool(DGV.Item(64, i).Value)))
.AddWithValue("@ClubEnv", If(IsDBNull(DGV.Item(65, i).Value), DBNull.Value, CBool(DGV.Item(65, i).Value)))
.AddWithValue("@CD_Financ", If(IsDBNull(DGV.Item(66, i).Value), DBNull.Value, CStr(DGV.Item(66, i).Value)))
.AddWithValue("@DC_ETAB", If(IsDBNull(DGV.Item(67, i).Value), DBNull.Value, CDate(DGV.Item(67, i).Value)))
.AddWithValue("@DO_ETAB", If(IsDBNull(DGV.Item(67, i).Value), DBNull.Value, CDate(DGV.Item(68, i).Value)))
.AddWithValue("@DF_ETAB", If(IsDBNull(DGV.Item(69, i).Value), DBNull.Value, CDate(DGV.Item(69, i).Value)))
.AddWithValue("@CD_causesFerm", If(IsDBNull(DGV.Item(70, i).Value), DBNull.Value, CStr(DGV.Item(70, i).Value)))
.AddWithValue("@DistSatEcoM", If(IsDBNull(DGV.Item(71, i).Value), DBNull.Value, CDbl(DGV.Item(71, i).Value)))
.AddWithValue("@DistUnitéCol", If(IsDBNull(DGV.Item(72, i).Value), DBNull.Value, CDbl(DGV.Item(72, i).Value)))
.AddWithValue("@DistColLyc", If(IsDBNull(DGV.Item(73, i).Value), DBNull.Value, CDbl(DGV.Item(73, i).Value)))
.AddWithValue("@Const_Dur", If(IsDBNull(DGV.Item(74, i).Value), DBNull.Value, CBool(DGV.Item(74, i).Value)))
.AddWithValue("@Const_Prefab", If(IsDBNull(DGV.Item(75, i).Value), DBNull.Value, CBool(DGV.Item(75, i).Value)))
.AddWithValue("@Const_Pise", If(IsDBNull(DGV.Item(76, i).Value), DBNull.Value, CBool(DGV.Item(76, i).Value)))
.AddWithValue("@Const_Autre", If(IsDBNull(DGV.Item(77, i).Value), DBNull.Value, CBool(DGV.Item(77, i).Value)))
.AddWithValue("@Eau_Source", If(IsDBNull(DGV.Item(78, i).Value), DBNull.Value, CInt(DGV.Item(78, i).Value)))
.AddWithValue("@Elec_Source", If(IsDBNull(DGV.Item(79, i).Value), DBNull.Value, CInt(DGV.Item(79, i).Value)))
.AddWithValue("@Ass_Source", If(IsDBNull(DGV.Item(80, i).Value), DBNull.Value, CInt(DGV.Item(80, i).Value)))
.AddWithValue("@Clo_Dur", If(IsDBNull(DGV.Item(81, i).Value), DBNull.Value, CBool(DGV.Item(81, i).Value)))
.AddWithValue("@Clo_partie", If(IsDBNull(DGV.Item(82, i).Value), DBNull.Value, CBool(DGV.Item(82, i).Value)))
.AddWithValue("@Clo_grillage", If(IsDBNull(DGV.Item(83, i).Value), DBNull.Value, CBool(DGV.Item(83, i).Value)))
.AddWithValue("@Clo_bois", If(IsDBNull(DGV.Item(84, i).Value), DBNull.Value, CBool(DGV.Item(84, i).Value)))
.AddWithValue("@Clo_autre", If(IsDBNull(DGV.Item(85, i).Value), DBNull.Value, CBool(DGV.Item(85, i).Value)))
.AddWithValue("@Clo_sans", If(IsDBNull(DGV.Item(86, i).Value), DBNull.Value, CBool(DGV.Item(86, i).Value)))
.AddWithValue("@Superf_etab", If(IsDBNull(DGV.Item(87, i).Value), DBNull.Value, CDbl(DGV.Item(87, i).Value)))
.AddWithValue("@Superf_Cour", If(IsDBNull(DGV.Item(88, i).Value), DBNull.Value, CDbl(DGV.Item(88, i).Value)))
.AddWithValue("@Superf_EspVert", If(IsDBNull(DGV.Item(89, i).Value), DBNull.Value, CDbl(DGV.Item(89, i).Value)))
.AddWithValue("@Superf_Exten", If(IsDBNull(DGV.Item(90, i).Value), DBNull.Value, CDbl(DGV.Item(90, i).Value)))
.AddWithValue("@Cantine", If(IsDBNull(DGV.Item(91, i).Value), DBNull.Value, CBool(DGV.Item(91, i).Value)))
.AddWithValue("@Cantine_Capacité", If(IsDBNull(DGV.Item(92, i).Value), DBNull.Value, CLng(DGV.Item(92, i).Value)))
.AddWithValue("@Cantine_DO", If(IsDBNull(DGV.Item(93, i).Value), DBNull.Value, CDate(DGV.Item(93, i).Value)))
.AddWithValue("@Cantine_DF", If(IsDBNull(DGV.Item(94, i).Value), DBNull.Value, CDate(DGV.Item(94, i).Value)))
.AddWithValue("@Restauration", If(IsDBNull(DGV.Item(95, i).Value), DBNull.Value, CBool(DGV.Item(95, i).Value)))
.AddWithValue("@Couvert_place", If(IsDBNull(DGV.Item(96, i).Value), DBNull.Value, CLng(DGV.Item(96, i).Value)))
.AddWithValue("@Internat", If(IsDBNull(DGV.Item(97, i).Value), DBNull.Value, CBool(DGV.Item(97, i).Value)))
.AddWithValue("@Internat_Capacité", If(IsDBNull(DGV.Item(98, i).Value), DBNull.Value, CLng(DGV.Item(98, i).Value)))
.AddWithValue("@Internat_DO", If(IsDBNull(DGV.Item(99, i).Value), DBNull.Value, CDate(DGV.Item(99, i).Value)))
.AddWithValue("@Internat_DF", If(IsDBNull(DGV.Item(100, i).Value), DBNull.Value, CDate(DGV.Item(100, i).Value)))
.AddWithValue("@Exist_Internet", If(IsDBNull(DGV.Item(101, i).Value), DBNull.Value, CBool(DGV.Item(101, i).Value)))
.AddWithValue("@upsize_ts", If(IsDBNull(DGV.Item(102, i).Value), DBNull.Value, DGV.Item(102, i).Value))
.AddWithValue("@nomL", If(IsDBNull(DGV.Item(103, i).Value), DBNull.Value, CStr(DGV.Item(103, i).Value)))
.AddWithValue("@FP_Bac", If(IsDBNull(DGV.Item(104, i).Value), DBNull.Value, CBool(DGV.Item(104, i).Value)))
.AddWithValue("@ENF", If(IsDBNull(DGV.Item(105, i).Value), DBNull.Value, CBool(DGV.Item(105, i).Value)))
.AddWithValue("@cd_Ass", If(IsDBNull(DGV.Item(106, i).Value), DBNull.Value, CLng(DGV.Item(106, i).Value)))
End With
Com.ExecuteNonQuery()
Com.Dispose()
Next
Cn.Close()
MessageBox.Show("Registered Successfully!", "Register", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As OleDb.OleDbException
MsgBox(ex.Message & vbNewLine & "Code Err : " & ex.ErrorCode, MsgBoxStyle.Critical, "Oledb Error")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
End Try
End Sub |
