0

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
Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Hi Tim, I put this statement between the lines Dim Da As New OleDbAdapter and Da.Fill(Dt) (after taking out the original SQL line), and I get an error saying Variable 'Da' hides a variable in an enclosing block in Using Da... Any ideas?
@Joe: you don't need to declare the adapter anymore since it's created in the Usingstatement. It is closed and disposed at the the end of it. All you want to do with it belongs into the Using. Remember to use the Using-statement for anything that implements IDisposable(with few exceptions like DataTable/DataSet) which don't hold unmanaged resources.
'Must declare the scalar variable "@PersonID".' is the new error after changing it this way?
@Joe: have you seen that i've modified your query?
Yes, the one I copied and pasted directly from your answer (Just done it again now), still returns the same error
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.