CODEWITHSUNDEEP
sqlvb.netparametersparameterized
user6579928
user6579928

Reputation:

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

Upvotes: 0

Views: 82

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460038

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", OleDbType.Integer).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.

Upvotes: 1

Related Questions