Joe Joe - 1 year ago 97 Question

Paramterising an SQL SELECT statement

I've been working with paramaterising my SQL statements lately, and have managed to do so for my

queries. However, I am struggling to do so with
... Is anybody able to help me? I feel it's because I'm using
, rather than

Public Shared Function getPerson(ByVal personID As Integer, m_cn As OleDbConnection)

Dim Dt As New DataTable

Dim Da As New OleDbDataAdapter
Da = New OleDbDataAdapter("SELECT * FROM tblPerson WHERE personID = " & personID, m_cn)


Return Dt

End Function

Answer Source

The OleDbDataAdapter.SelectCommand has the parameters for the SQL statement (or stored procedure) used to select records:

Using da = New OleDbDataAdapter("SELECT * FROM tblPerson WHERE personID = @PersonID", m_cn)
    da.SelectCommand.Parameters.Add("@PersonID", SqlDbType.Int).Value = personID
End Using

I suggest to not reuse the connection(or make it even static/shared) since that can cause various issues. Instead create, open and use it wherever you need it, so in this method, best by using the Using statement to ensure that it gets closed even in case of an error.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download