Start by fixing the
SQL Injection[
^] vulnerability in your code.
Use the parameters passed to the methods, rather than ignoring them and accessing the controls directly.
Create the
OleDbConnection object when you need it, and throw it away as soon as you're finished with it. There's no need to keep a connection object hanging around in a field.
Wrap your
OleDbConnection and
OleDbCommand objects in
Using blocks, to ensure that they are always cleaned up properly.
Something like this should work:
Private Function CreateConnection() As OleDbConnection
Return New OleDbConnection("YOUR CONNECTION STRING HERE")
End Function
Public Sub AddCommande(ByVal NumCommande As Integer, ByVal DateCommande As Date, ByVal NumCLient As Integer, ByVal Description_Commande As String)
Using connection As OleDbConnection = CreateConnection()
Using command As New OleDbCommand("insert into Commandes (NumCommande, DateCommande, NumClient, Description_Commande) VALUES (?, ?, ?, ?)", connection)
command.Parameters.AddWithValue("NumCommande", NumCommande)
command.Parameters.AddWithValue("DateCommande", DateCommande)
command.Parameters.AddWithValue("NumClient", NumClient)
command.Parameters.AddWithValue("Description_Commande", Description_Commande)
connection.Open()
command.ExecuteNonQuery()
End Using
End Using
End Sub
Public Sub AddDetailCommande(ByVal Reference As Integer, ByVal NumCommande As Integer, ByVal Prix_unitaire As Double, ByVal Quantite As Integer, ByVal PVenteHT As Double, ByVal taux As Integer, ByVal TVA As Integer, ByVal PrixVenteTTC As Double, ByVal Remise As Integer, ByVal MontantTotPrixVente As Double)
Using connection As OleDbConnection = CreateConnection()
Using command As New OleDbCommand("insert into Details_commandes(Reference, NumCommande, Prix_unitaire, Quantite, PVenteHT, Taux, TVA, PrixVenteTTC, Remise, MontantTotPrixVente) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", connection)
command.Parameters.AddWithValue("Reference", Reference)
command.Parameters.AddWithValue("NumCommande", NumCommande)
command.Parameters.AddWithValue("Prix_unitaire", Prix_unitaire)
command.Parameters.AddWithValue("Quantite", Quantite)
command.Parameters.AddWithValue("PVenteHT", PVenteHT)
command.Parameters.AddWithValue("Taux", taux)
command.Parameters.AddWithValue("TVA", TVA)
command.Parameters.AddWithValue("PrixVenteTTC", PrixVenteTTC)
command.Parameters.AddWithValue("Remise", Remise)
command.Parameters.AddWithValue("MontantTotPrixVente", MontantTotPrixVente)
connection.Open()
command.ExecuteNonQuery()
End Using
End Using
End Sub
Private Sub Sub IdAutoCommandeCltPrdt()
Using connection As OleDbConnection = CreateConnection()
Using command As New OleDbCommand("select Max(NumCommande) from Commandes", connection)
connection.Open()
Dim num As Object = command.ExecuteScalar()
If num Is Nothing OrElse Convert.IsDBNull(num) Then
txtNCdclt.Text = "1"
Else
txtNCdclt.Text = Convert.ToInt32(num) + 1
End If
End Using
End Using
End Sub
Private Sub btnAddCommande_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddCommande.Click
Dim NumCommande As Integer = Convert.ToInt32(txtNCdclt.Text)
AddCommande(NumCommande, dateCmdClt.Value, txtnumclient.Text, txtdescmdClt.Text)
Dim Reference As Integer
Dim Prix_unitaire As Double
Dim Quantite As Integer
Dim PVenteHT As Double
Dim taux As Integer
Dim TVA As Integer
Dim PrixVenteTTC As Double
Dim Remise As Integer
Dim MontantTotPrixVente As Double
For i = 0 To dgvProduits.Rows.Count - 1
Reference = Convert.ToInt32(dgvProduits.Rows(i).Cells(0).Value)
Prix_unitaire = Convert.ToDouble(dgvProduits.CurrentRow.Cells(2).Value)
Quantite = Convert.ToInt32(dgvProduits.Rows(i).Cells(3).Value)
PVenteHT = Convert.ToDouble(dgvProduits.Rows(i).Cells(4).Value)
taux = Convert.ToInt32(dgvProduits.Rows(i).Cells(5).Value)
TVA = Convert.ToInt32(dgvProduits.Rows(i).Cells(6).Value)
PrixVenteTTC = Convert.ToDouble(dgvProduits.Rows(i).Cells(7).Value)
Remise = Convert.ToInt32(dgvProduits.Rows(i).Cells(8).Value)
MontantTotPrixVente = Convert.ToDouble(dgvProduits.Rows(i).Cells(9).Value)
AddDetailCommande(Reference, NumCommande, Prix_unitaire, Quantite, PVenteHT, taux, TVA, PrixVenteTTC, Remise, MontantTotPrixVente)
Next
IdAutoCommandeCltPrdt()
MsgBox("Le commande a été ajouté avec succée !!!", vbInformation + vbOKOnly, "Information ")
End Sub
Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
SQL injection attack mechanics | Pluralsight [^]
Submit an article or tip