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
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
|
Imports System.Data.SqlClient
Public Class Form1
Public CePC As String = My.Computer.Name
Public LaConnexion As String
Public Connexion As New SqlConnection(LaConnexion)
Public DSGrid As New DataSet
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim PC1 As String = "LE NOM DE MON PC"
If CePC = PC1 Then
LaConnexion = "BLABLABLA MA CONNECTION AU SERVEUR;"
Else
LaConnexion = " BLABLABLA MA CONNECTION AU SERVEUR;"
End If
Connexion.ConnectionString = LaConnexion
Try
Connexion.Open()
MAJDataGrid()
MajControls()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Function GetNewItem(ByVal LaRq As String) As String
'Fonction de récupération du dernier ID ajouté dans les tables
Dim CmdLastId As New SqlCommand(LaRq, Connexion)
Dim LastId As String = CStr(CmdLastId.ExecuteScalar())
Return LastId
End Function
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
'Tableau de conservation des choix dans les combos
Dim TabValCombo(11) As String
'Test as la combo pour déterminer si il faut ajouter des données dans la table
'et dans les 2 cas conservation des choix utilisateur
If ComboBox7.SelectedValue Is Nothing Then
Dim RqInsert As String = "Insert into T_Unites_Pds( U_PDS_UNITE ) values ('" & ComboBox7.Text & "')"
Dim CmdInsert As New SqlCommand(RqInsert, Connexion)
CmdInsert.ExecuteNonQuery()
Dim LaRq As String = "Select Ident_Current('T_Unites_Pds')"
TabValCombo(0) = "Combobox7"
TabValCombo(1) = GetNewItem(LaRq)
Else
TabValCombo(0) = "Combobox7"
TabValCombo(1) = CStr(ComboBox7.SelectedValue)
End If
'Test la combo pour déterminer si il faut ajouter des données dans la table
'et dans les 2 cas conservation des choix utilisateur
If ComboBox9.SelectedValue Is Nothing Then
Dim RqInsert As String = "Insert into T_Categories( CATEGORIE_TYPE ) values ('" & ComboBox9.Text & "')"
Dim CmdInsert As New SqlCommand(RqInsert, Connexion)
CmdInsert.ExecuteNonQuery()
Dim LaRq As String = "Select Ident_Current('T_Categories')"
TabValCombo(2) = "Combobox9"
TabValCombo(3) = GetNewItem(LaRq)
Else
TabValCombo(2) = "Combobox9"
TabValCombo(3) = CStr(ComboBox9.SelectedValue)
End If
'Test la combo pour déterminer si il faut ajouter des données dans la table
'et dans les 2 cas conservation des choix utilisateur
If ComboBox3.SelectedValue Is Nothing Or ComboBox4.SelectedValue Is Nothing Or ComboBox5.SelectedValue Is Nothing Then
Dim RqInsert As String = "Insert into T_Conditionnements(COND_U_PDS_ID , COND_CMD_PCS_MINI , COND_QT_U , COND_TYPE) " _
& "values (" & TabValCombo(1) & "," & ComboBox4.Text & "," & ComboBox5.Text & "," & "'" & ComboBox3.Text & "')"
Dim CmdInsert As New SqlCommand(RqInsert, Connexion)
CmdInsert.ExecuteNonQuery()
Dim LaRq As String = "Select Ident_Current('T_Conditionnements')"
TabValCombo(4) = "Combobox3"
TabValCombo(5) = GetNewItem(LaRq)
Else
TabValCombo(4) = "Combobox3"
TabValCombo(5) = CStr(ComboBox3.SelectedValue)
End If
'Test la combo pour déterminer si il faut ajouter des données dans la table
'et dans les 2 cas conservation des choix utilisateur
If ComboBox2.SelectedValue Is Nothing Then
Dim RqInsert As String = "Insert into T_Fournisseurs(FOUR_NOM) values ('" & ComboBox2.Text & "')"
Dim CmdInsert As New SqlCommand(RqInsert, Connexion)
CmdInsert.ExecuteNonQuery()
Dim LaRq As String = "Select Ident_Current('T_Fournisseurs')"
TabValCombo(6) = "Combobox2"
TabValCombo(7) = GetNewItem(LaRq)
Else
TabValCombo(6) = "Combobox2"
TabValCombo(7) = CStr(ComboBox2.SelectedValue)
End If
TabValCombo(8) = "Combobox6"
TabValCombo(9) = CStr(ComboBox6.SelectedValue)
TabValCombo(10) = "Combobox1"
TabValCombo(11) = CStr(ComboBox1.SelectedValue)
'Mise à jour des combos pour intégrer les nouvelles données
MajControls()
'reséléction des items séléctionnés avant la mise ajour des combos
For i As Integer = 0 To UBound(TabValCombo) Step 2
Dim LaComBo As ComboBox = CType(Controls(TabValCombo(i)), ComboBox) LaComBo.SelectedValue = CInt(TabValCombo(i + 1))
Next i
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim RqExistProd As String = "Select PROD_FOUR_ID , PROD_STOC_ID, PROD_COND_ID, PROD_CATEGORIE_ID , PROD_NOM, PROD_REFPRODFOUR " _
& "from T_Produits " _
& "WHERE PROD_FOUR_ID = " & CInt(ComboBox2.SelectedValue) & " And PROD_STOC_ID = " & CInt(ComboBox6.SelectedValue) & " And PROD_COND_ID = " & CInt(ComboBox3.SelectedValue) & "" _
& " And PROD_NOM = " & " '" & ComboBox1.Text & "' AND PROD_REFPRODFOUR = '" & ComboBox8.Text & "' AND PROD_CATEGORIE_ID = " & CInt(ComboBox9.SelectedValue) & ""
Dim CmdExistProd As New SqlCommand(RqExistProd, Connexion)
Dim ReaderProd As SqlDataReader = CmdExistProd.ExecuteReader()
If ReaderProd.Read() Then
MsgBox("Cette ligne existe déjà")
Else
ReaderProd.Close()
Dim RqInsertT_Prod As String = "Insert into T_Produits(PROD_FOUR_ID , PROD_STOC_ID , PROD_COND_ID , PROD_CATEGORIE_ID , PROD_NOM , PROD_REFPRODFOUR )" _
& "values (" & CInt(ComboBox2.SelectedValue) & "," & CInt(ComboBox6.SelectedValue) & "," & CInt(ComboBox3.SelectedValue) & "," & CInt(ComboBox9.SelectedValue) & "," & "'" & ComboBox1.Text & "','" & ComboBox8.Text & "')"
Dim CmdInsertProd As New SqlCommand(RqInsertT_Prod, Connexion)
MsgBox("Il y a eu " & CmdInsertProd.ExecuteNonQuery() & " lignes mises à jour")
End If
ReaderProd.Close()
MAJDataGrid()
End Sub
'MAJ DATAGRID
Sub MAJDataGrid()
'Mise a jour du datagrig de la feuille
DSGrid.Clear()
Dim RqGrid As String = "SELECT ID_PROD , PROD_NOM , PROD_FOUR_ID , PROD_STOC_ID , PROD_COND_ID , PROD_REFPRODFOUR , ID_FOUR , FOUR_NOM , " _
& "ID_COND , COND_U_PDS_ID , COND_TYPE , COND_CMD_PCS_MINI , COND_QT_U , ID_STOC , STOC_TYPE , ID_U_PDS , U_PDS_UNITE , ID_CATEGORIE , CATEGORIE_TYPE " _
& "from T_Produits , T_Fournisseurs , T_Conditionnements , T_Stockages , T_Unites_Pds , T_Categories " _
& "WHERE ID_FOUR = PROD_FOUR_ID and PROD_STOC_ID = ID_STOC and PROD_COND_ID = ID_COND and COND_U_PDS_ID = ID_U_PDS AND PROD_CATEGORIE_ID = ID_CATEGORIE"
Dim CmdGrid As New SqlCommand(RqGrid, Connexion)
Dim AdpGrid As New SqlDataAdapter(CmdGrid)
AdpGrid.Fill(DSGrid, "TbPRODGrid")
DG_DataGrid.DataSource = DSGrid.Tables("TbPRODGrid")
For i = 0 To DG_DataGrid.ColumnCount - 1
DG_DataGrid.Columns(i).Width = 75
Next i
End Sub
'MAJ DES COMBOS
Sub MajControls()
Try
Dim RqFour As String = "SELECT * FROM T_Fournisseurs"
Dim CmdFour As New SqlCommand(RqFour, Connexion)
Dim AdpFour As New SqlDataAdapter(CmdFour)
Dim DSFour As New DataSet
Dim RqCond As String = "SELECT * FROM T_Conditionnements"
Dim CmdCond As New SqlCommand(RqCond, Connexion)
Dim AdpCond As New SqlDataAdapter(CmdCond)
Dim DSCond As New DataSet
Dim RqStoc As String = "SELECT * FROM T_Stockages"
Dim CmdStoc As New SqlCommand(RqStoc, Connexion)
Dim AdpStoc As New SqlDataAdapter(CmdStoc)
Dim DSStoc As New DataSet
Dim RqU_Pds As String = "SELECT * FROM T_Unites_Pds"
Dim CmdU_Pds As New SqlCommand(RqU_Pds, Connexion)
Dim AdpU_Pds As New SqlDataAdapter(CmdU_Pds)
Dim DSU_Pds As New DataSet
Dim RqCat As String = "SELECT * FROM T_Categories order by CATEGORIE_TYPE"
Dim CmdCat As New SqlCommand(RqCat, Connexion)
Dim AdpCat As New SqlDataAdapter(CmdCat)
Dim DSCat As New DataSet
AdpCat.Fill(DSCat, "TbCat")
AdpFour.Fill(DSFour, "TbFour")
AdpCond.Fill(DSCond, "TbCond")
AdpStoc.Fill(DSStoc, "TbStoc")
AdpU_Pds.Fill(DSU_Pds, "TbU_Pds")
ComboBox1.DisplayMember = "PROD_NOM"
ComboBox1.ValueMember = "ID_PROD"
ComboBox1.DataSource = DSGrid.Tables("TbPRODGrid")
ComboBox2.DisplayMember = "FOUR_NOM"
ComboBox2.ValueMember = "ID_FOUR"
ComboBox2.DataSource = DSFour.Tables("TbFour")
ComboBox3.DisplayMember = "COND_TYPE"
ComboBox3.ValueMember = "ID_COND"
ComboBox3.DataSource = DSCond.Tables("TbCond")
ComboBox4.DisplayMember = "COND_CMD_PCS_MINI"
ComboBox4.ValueMember = "ID_COND"
ComboBox4.DataSource = DSCond.Tables("TbCond")
ComboBox5.DisplayMember = "COND_QT_U"
ComboBox5.ValueMember = "ID_COND"
ComboBox5.DataSource = DSCond.Tables("TbCond")
ComboBox6.DisplayMember = "STOC_TYPE"
ComboBox6.ValueMember = "ID_STOC"
ComboBox6.DataSource = DSStoc.Tables("TbStoc")
ComboBox7.DisplayMember = "U_PDS_UNITE"
ComboBox7.ValueMember = "ID_U_PDS"
ComboBox7.DataSource = DSU_Pds.Tables("TbU_Pds")
ComboBox8.DisplayMember = "PROD_REFPRODFOUR"
ComboBox8.ValueMember = "ID_PROD"
ComboBox8.DataSource = DSGrid.Tables("TbPRODGrid")
ComboBox9.DisplayMember = "CATEGORIE_TYPE"
ComboBox9.ValueMember = "ID_CATEGORIE"
ComboBox9.DataSource = DSCat.Tables("TbCat")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
'FERMETURE FORM
Private Sub Form1_Closed(sender As Object, e As EventArgs) Handles Me.Closed
'Libere le dataset
DSGrid.Dispose()
Connexion.Close()
End Sub
End Class |
S'initier aux objets
Répondre avec citation
Partager