Joe Joe - 4 months ago 27
Vb.net Question

Paramterising an SQL SELECT statement

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

INSERT
,
UPDATE
and
DELETE
queries. However, I am struggling to do so with
SELECT
... Is anybody able to help me? I feel it's because I'm using
OleDbDataAdapter
, rather than
OleDbCommand
?

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)

Da.Fill(Dt)

Return Dt

End Function

Answer

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
    Da.Fill(Dt)
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.