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 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334
| Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Dim nomServeur, nomBdd, nomUser, passUser, strConn As String
Dim Conn As New SqlClient.SqlConnection
Dim myCommand As New SqlClient.SqlCommand
Dim myAdapter As New SqlClient.SqlDataAdapter
Dim myDataset As New DataSet
Dim myDatasetcombo As New DataSet
Dim myDataTable As DataTable
Private Sub filtreAppretB()
If Me.ComboBox1.SelectedItem Is Nothing Then Exit Sub
ComboBox2.Visible = True
Dim valeurAppret As String
Dim MaTable As String
Dim IdTable As String
Dim Affichage As String
valeurAppret = Me.ComboBox1.SelectedValue.ToString()
If valeurAppret = 0 Then
MsgBox("Veuillez faire un choix")
Else
myDatasetcombo.Clear()
myCommand.Connection = Conn
myAdapter.SelectCommand() = myCommand
If valeurAppret = 1 Then
MaTable = "Fournisseurs"
IdTable = "id_fournisseur"
Affichage = "Nom"
ComboBox2.ValueMember = ""
myCommand.CommandText = "Select * from Fournisseurs order by nom"
myAdapter.Fill(myDatasetcombo, "Fournisseurs")
myDataTable = myDatasetcombo.Tables("Fournisseurs")
ComboBox2.DisplayMember = "Nom"
ComboBox2.ValueMember = "id_fournisseur"
ComboBox2.Items.Clear()
ComboBox2.DataSource = myDatasetcombo.Tables("Fournisseurs")
ElseIf valeurAppret = 2 Then
MaTable = "Matieres"
IdTable = "id_matiere"
Affichage = "Code_matiere_fr"
ComboBox2.ValueMember = ""
myCommand.CommandText = "Select * from Matieres order by CONVERT(nvarchar,Description_fr)"
myAdapter.Fill(myDatasetcombo, "Matieres")
myDataTable = myDatasetcombo.Tables("Matieres")
ComboBox2.DisplayMember = "Description_fr"
ComboBox2.ValueMember = "id_matiere"
ComboBox2.Items.Clear()
ComboBox2.DataSource = myDatasetcombo.Tables("Matieres")
End If
ComboBox2.Refresh()
FermerConnexion()
sqlAppret()
End If
End Sub
Private Sub sqlAppret()
Dim valeurAppret As String
Dim MaTable As String
Dim IdTable As String
Dim Affichage As String
Dim choix As String
If Me.ComboBox1.SelectedItem Is Nothing Then Exit Sub
If Me.ComboBox2.SelectedItem Is Nothing Then Exit Sub
valeurAppret = Me.ComboBox1.SelectedValue.ToString()
choix = Me.ComboBox2.SelectedValue.ToString()
If valeurAppret = 1 Then
MaTable = "Fournisseurs"
IdTable = "id_fournisseur"
Affichage = "Nom"
myCommand.CommandText = "SELECT Apprets.id_appret, Apprets.designation_fr, Fournisseurs.Nom, Apprets.ref_fournisseur, Unites.code_unite_fr, Matieres.Description_fr, STR(Apprets.stock_EI,8,2),STR(apprets.stock_HEI,8,2) FROM Apprets INNER JOIN Fournisseurs ON Apprets.code_fournisseur = Fournisseurs.id_fournisseur INNER JOIN Unites ON Apprets.code_unite = Unites.id_unite INNER JOIN Matieres ON Apprets.matiere = Matieres.id_matiere where code_fournisseur=" & choix & " order by id_appret"
ElseIf valeurAppret = 2 Then
MaTable = "Matieres"
IdTable = "id_matiere"
Affichage = "Code_matiere_fr"
myCommand.CommandText = "SELECT Apprets.id_appret, Apprets.designation_fr, Fournisseurs.Nom, Apprets.ref_fournisseur, Unites.code_unite_fr, Matieres.Description_fr, STR(Apprets.stock_EI,8,2),STR(apprets.stock_HEI,8,2) FROM Apprets INNER JOIN Fournisseurs ON Apprets.code_fournisseur = Fournisseurs.id_fournisseur INNER JOIN Unites ON Apprets.code_unite = Unites.id_unite INNER JOIN Matieres ON Apprets.matiere = Matieres.id_matiere where matiere=" & choix & " order by id_appret"
End If
MsgBox(myCommand.CommandText)
Try
OuvrirConnexion()
myDataset.Clear()
myCommand.Connection = Conn
myAdapter.SelectCommand() = myCommand 'L'objet adapter prend en compte mon objet command
myAdapter.Fill(myDataset, MaTable) 'Ici, il faut remettre le nom de la table comme deuxime paramtre
myDataTable = myDataset.Tables(MaTable) 'Ici, je dfini mon objet DataTable, il faut remettre le nom de la table ici aussi
Datagrid1.DataSource = myDataTable 'Ma source de donne de mon datagrid est mon DataTable
'On ferme tout
myDataTable.Dispose()
myAdapter.Dispose()
myCommand.Dispose()
FermerConnexion()
Catch ex As Exception
MessageBox.Show("Impossible d'tablir la connection la base." & ex.Message)
End Try
End Sub
Private Sub apprets()
Try
OuvrirConnexion()
myCommand.CommandText = "SELECT Apprets.id_appret, Apprets.designation_fr, Fournisseurs.Nom, Apprets.ref_fournisseur, Unites.code_unite_fr, Matieres.Description_fr, STR(Apprets.stock_EI,8,2),STR(apprets.stock_HEI,8,2) FROM Apprets INNER JOIN Fournisseurs ON Apprets.code_fournisseur = Fournisseurs.id_fournisseur INNER JOIN Unites ON Apprets.code_unite = Unites.id_unite INNER JOIN Matieres ON Apprets.matiere = Matieres.id_matiere" 'Je dfini ma requte SQL
'2- myCommand.CommandText = "SELECT * FROM CLIENT c, COMMANDE co WHERE co.CLI_CMD = c.ID_CLI AND c.NOM_CLI = 'KILL'"
myCommand.Connection = Conn
myAdapter.SelectCommand() = myCommand 'L'objet adapter prend en compte mon objet command
myAdapter.Fill(myDataset, "Apprets") 'Ici, il faut remettre le nom de la table comme deuxime paramtres
'Mme pour une requte imbrique, il suffit de passer une des tables de la requte en paramtres, cela ne change donc rien
myDataTable = myDataset.Tables("Apprets") 'Ici, je dfini mon objet DataTable, il faut remettre le nom de la table ici aussi
Datagrid1.DataSource = myDataTable 'Ma source de donne de mon datagrid est mon DataTable
'On ferme tout
myDataTable.Dispose()
myAdapter.Dispose()
myCommand.Dispose()
FermerConnexion()
'filtreAppretA()
Catch ex As Exception
MessageBox.Show("Impossible d'tablir la connection la base." & ex.Message)
End Try
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
apprets()
End Sub
Private Sub DataGridView1_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Datagrid1.CellDoubleClick
TabControl1.SelectedIndex = 0
MsgBox(Datagrid1.CurrentRow.Cells(0).Value)
End Sub
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
Datagrid1.DataSource = Nothing
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
myDataset.Clear()
myCommand.CommandText = "SELECT id_appret,designation_fr FROM Apprets where designation_fr LIKE '%" + TextBox1.Text + "%'" 'Je dfini ma requte SQL
'2- myCommand.CommandText = "SELECT * FROM CLIENT c, COMMANDE co WHERE co.CLI_CMD = c.ID_CLI AND c.NOM_CLI = 'KILL'"
myCommand.Connection = Conn
myAdapter.SelectCommand() = myCommand 'L'objet adapter prend en compte mon objet command
myAdapter.Fill(myDataset, "Apprets") 'Ici, il faut remettre le nom de la table comme deuxime paramtres
'Mme pour une requte imbrique, il suffit de passer une des tables de la requte en paramtres, cela ne change donc rien
myDataTable = myDataset.Tables("Apprets") 'Ici, je dfini mon objet DataTable, il faut remettre le nom de la table ici aussi
Datagrid1.DataSource = myDataTable
'On ferme tout
myDataTable.Dispose()
myAdapter.Dispose()
myCommand.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Public Sub New()
' Cet appel est requis par le Concepteur Windows Form.
InitializeComponent()
Dim dt As New DataTable
dt.Columns.Add("C1")
dt.Columns.Add("C2")
dt.Rows.Add("0", "Faite un choix")
dt.Rows.Add("1", "Fournisseurs")
dt.Rows.Add("2", "Matires")
Me.ComboBox1.DataSource = dt
Me.ComboBox1.ValueMember = "C1"
Me.ComboBox1.DisplayMember = "C2"
Me.ComboBox1.DropDownStyle = ComboBoxStyle.DropDown 'ComboBoxStyle.DropDownList
AddHandler ComboBox1.SelectedIndexChanged, AddressOf ComboBox1_SelectedIndexChanged
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
myDataset.Clear()
myDatasetcombo.Clear()
If Me.ComboBox1.SelectedItem Is Nothing Then Exit Sub
ComboBox2.Refresh()
Dim dv As DataRowView = CType(Me.ComboBox1.SelectedItem, DataRowView)
filtreAppretB()
End Sub
Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
Dim dv As DataRowView = CType(Me.ComboBox2.SelectedItem, DataRowView)
sqlAppret()
End Sub
End Class |
Partager