Parameterized Access/SQL statements?!

[tramimaus]

Hi!
Can somebody help me out please?
I need to modify the code, so that it uses the text command version of the parameterized sql/ access statement. Does anybody know how to do that?
Thank you!

Dim dtNW As New DataTable
Dim ConnEmp As OleDb.OleDbConnection
Dim strCommand As String = "Select LastName, FirstName, City from Employees where EmployeeID = " & txtID.Text
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\DataStore\Nwind.mdb;Mode=Share Deny None;"
ConnEmp = New OleDb.OleDbConnection(strConn)
Dim cmdEmp As New OleDb.OleDbCommand(strCommand, ConnEmp)
Dim daEmp As New OleDb.OleDbDataAdapter(cmdEmp)
Try
daEmp.Fill(dtNW)
txtLName.Text = dtNW.Rows(0).Item("LastName")
txtFName.Text = dtNW.Rows(0).Item("FirstName")
txtCity.Text = dtNW.Rows(0).Item("City").ToString ' City may be null
Catch exc As Exception
' There should not be msgbox in a Class
MsgBox("Error in retrieving Data " & exc.Message)
txtLName.Clear()
txtFName.Clear()
txtCity.Clear()
End Try

 

[chiph]

The only thing that parameterized queries would help you with in this select statement is where you're specifying the EmployeeID. That's only one possible parameter, so not a lot of benefit there.

And since that's (presumably) a numeric value, you would do something like this to protect yourself from SQL Injection (you're currently passing the contents of the textbox in unchanged, which is bad):

Dim sSQL As String
Dim lEmpID As Long

If IsNumeric(txtID.Text) Then
   lEmpID = CLng(txtID.Text)

   sSQL = ""
   sSQL = sSQL & " SELECT LastName, FirstName, City"
   sSQL = sSQL & " FROM Employees"
   sSQL = sSQL & " WHERE EmployeeID = " & CStr(lEmpID)
End If

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first