Fixing some of the more obvious problems in your code:
- Use parameterized queries to fix the SQL Injection[^] vulnerability.
- Don't store connection and command objects as class-level fields; use local variables instead.
- Wrap objects that implement
IDisposable
in a Using
block.
Private Function CreateConnection() As OleDbConnection
Dim result As New OleDbConnection("** YOUR CONNECTION STRING HERE **")
result.Open()
Return result
End Function
Private Sub btnReport_SLA_Click(sender As Object, e As EventArgs) Handles btnReport_SLA.Click
Try
Using connection As OleDbConnection = CreateConnection()
Using cmdDelete As OleDbCommand = connection.CreateCommand()
cmdDelete.CommandText = "DELETE * FROM SLA"
cmdDelete.ExecuteNonQuery()
End Using
Using cmdInsert As OleDbCommand = connection.CreateCommand()
If DateAdded.Checked Then
cmdInsert.CommandText = "INSERT INTO SLA SELECT ID, Dial, ServiceName, SupplierName, DateAdded, SupplierFeedbackDate FROM Customers Where SupplierName = ? AND SupplierFeedbackDate <> '.... / .... / .....' AND DateAdded between ? And ?"
cmdInsert.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
cmdInsert.Parameters.AddWithValue("DateAddedFrom", cbo_DateSent.Value)
cmdInsert.Parameters.AddWithValue("DateAddedTo", cbo_DateSentTo.Value)
Else
cmdInsert.CommandText = "INSERT INTO SLA SELECT ID, Dial, ServiceName, SupplierName, DateAdded, SupplierFeedbackDate FROM Customers Where SupplierName = ? AND SupplierFeedbackDate <> '.... / .... / .....'"
cmdInsert.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
End If
cmdInsert.ExecuteNonQuery()
End Using
Using cmdUpdate As OleDbCommand = connection.CreateCommand()
cmdUpdate.CommandText = "Update SLA SET ClosureDuration = SupplierFeedbackDate - DateAdded"
cmdUpdate.ExecuteNonQuery()
End Using
If String.IsNullOrEmpty(cbo_Supplier.Text) Then
MessageBox.Show("No supplier selected")
Return
End If
Using cmdSelect As OleDbCommand = connection.CreateCommand()
If DateAdded.Checked Then
cmdSelect.CommandText = "Select * From Customers Where SupplierName = ? AND DateAdded between ? And ? AND SupplierFeedbackDate <> '.... / .... / .....' "
cmdSelect.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
cmdSelect.Parameters.AddWithValue("DateAddedFrom", cbo_DateSent.Value)
cmdSelect.Parameters.AddWithValue("DateAddedTo", cbo_DateSentTo.Value)
Else
cmdSelect.CommandText = "Select * From Customers Where SupplierName = ? AND SupplierFeedbackDate <> '.... / .... / .....' "
cmdSelect.Parameters.AddWithValue("SupplierName", cbo_Supplier.Text)
End If
Using da As New OleDbDataAdapter(cmdSelect)
Dim ds As New DataSet()
da.Fill(ds, "Customers")
DataGridViewReports.DataSource = ds
DataGridViewReports.DataMember = "Customers"
lbl_RowCount.Text = DataGridViewReports.RowCount
End Using
End Using
Using cmdAverage As OleDbCommand = connection.CreateCommand()
cmdAverage.CommandText = "SELECT AVG(ClosureDuration) FROM SLA"
Dim AVGResult As Integer = Convert.ToInt32(cmdAverage.ExecuteScalar())
Dim SFound As String = If(AVGResult > 1, "s", "")
If DateAdded.Checked Then
MessageBox.Show(String.Format("Response duration from {1} : {2} day{3}{0}{0}For all finished requests sent between {4} and {5}", _
vbNewLine, cbo_Supplier.Text, AVGResult, SFound, cbo_DateSent.Value, cbo_DateSentTo.Value));
Else
MessageBox.Show(String.Format("Response duration from {1} : {2} day{3}{0}{0}For all finished requests.", _
vbNewLine, cbo_Supplier.Text, AVGResult, SFound));
End If
End Using
Using cmdDelete As OleDbCommand = connection.CreateCommand()
cmdDelete.CommandText = "DELETE * FROM SLA"
cmdDelete.ExecuteNonQuery()
End Using
End Using
Catch ex As OleDbException
System.Diagnostics.Trace.TraceError(ex.ToString())
MessageBox.Show(ex.Message)
End Try
End Sub