Using Parameterized SQL Queries

The ParameterizedQueries example on the CD-ROM uses an SQL parameter in a data adapter, which is much like a variable in other programming languages. Here's the SQL—in this case, I'm using a parameter, indicated by the question mark (?), for the state field in a WHERE clause in the SQL for the second data adapter:

SELECT au_id, au_lname, state FROM authors WHERE (state = ?)

In this case, I'm indicating that I want to set the value of the state field at run time. You can either enter this kind of SQL directly into the Data Adapter Configuration Wizard, or use the Query Builder in the wizard, setting the Criteria column to a ? (which Visual Basic immediately changes to the more proper SQL "=?") for the state field.

The question is: how do we place a value into the SQL parameter corresponding to the state field at run time? That turns out to be easy enough—you just refer to that parameter as OleDbDataAdapter1.SelectCommand.Parameters ("state"). In the ParameterizedQueries example, I'll set this parameter to the state the user has selected in a combo box:

Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
    OleDbDataAdapter2.SelectCommand.Parameters("state").Value = _
        ComboBox1.Text
    DataSet12.Clear()
    OleDbDataAdapter2.Fill(DataSet12)
    ShowPosition()
End Sub

And that's all it takes to use SQL parameters in Visual Basic.