Paramterising an SQL SELECT statement

Question

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

Tim Schmelter · Accepted Answer · 2016-07-21T12:56:41.540

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.

edited Jul 21 '16 at 12:56

answered Jul 21 '16 at 10:27

Tim Schmelter

450,073
74
686
939

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? – Jul 21 '16 at 10:35
@Joe: you don't need to declare the adapter anymore since it's created in the `Using`statement. 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. – Tim Schmelter Jul 21 '16 at 10:36
'Must declare the scalar variable "@PersonID".' is the new error after changing it this way? – Jul 21 '16 at 10:42
@Joe: have you seen that i've modified your query? – Tim Schmelter Jul 21 '16 at 10:45
Yes, the one I copied and pasted directly from your answer (Just done it again now), still returns the same error – Jul 21 '16 at 10:48
@joe: you have also added this line: `da.SelectCommand.Parameters.Add("@PersonID", SqlDbType.Int).Value = personID`? Maybe you need to use the question mark syntax: http://stackoverflow.com/a/5870248/284240 because named parameters are not supported by your DB – Tim Schmelter Jul 21 '16 at 11:12
Hi Tim, yes I had added that line and it was just the question mark syntax that was causing it to fail, so thank you for your help! – Jul 21 '16 at 11:36
You have `SqlDbType` rather than `OleDbType` – Ňɏssa Pøngjǣrdenlarp Jul 21 '16 at 12:50
@Plutonix: fixed, but since he can't use named parameters there's still something to fix above – Tim Schmelter Jul 21 '16 at 12:57
Tim - doesn't your example run counter to your recommendation to not reuse a connection? m_cn seems to be a reused connection to me. – rheitzman Jul 21 '16 at 15:08
My suggestion is to create the connection here by using the using statement. I have just left out the code – Tim Schmelter Jul 21 '16 at 15:44

Paramterising an SQL SELECT statement

1 Answers1