Using SQL Parameters

Here's another example that uses data-bound controls, the ParameterizedQueries example on the CD-ROM, which also will give us more insight into working with SQL in Visual Basic data adapters. In this case, I'll use two data adapters and two datasets to let the user select the authors they want to view by state. You can see how it works in Figure 21.11. When the program loads, the states of the authors in the authors table of the pubs database are loaded into a dataset bound to the drop-down list you see in Figure 21.11. The user can then select a state from the list, and click the "Load data" button to load all the authors from that state into a second dataset, whose data is displayed in the text boxes in the figure.

Figure 21.11: The ParameterizedQueries example.

Loading the state data from the authors table is easy in the first data adapter, OleDbDataAdapter1—I just give that data adapter this SQL in the Data Adapter Configuration Wizard (see "Accessing Data with Data Adaptors and DataSets" in the previous chapter):

SELECT DISTINCT state FROM authors

Note that I've added the keyword DISTINCT here so that only unique states are placed in the corresponding dataset—no state will be listed more than once.

After the user selects a state, how do we tell the second data adapter, OleDbDataAdapter2 (which fills the dataset, DataSet12, for the rest of the controls) how to use that selection to choose its data? We can do that with a SQL parameter, which is much like a variable in other programming languages. Here's how that works; 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, using a SQL parameter. You can either enter this SQL for OleDbDataAdapter2 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, as you see in Figure 21.12.

Figure 21.12: Adding a SQL parameter to a data adapter.

How do we place a value into the SQL parameter corresponding to the state field at run time? That's easy enough—you just refer to that parameter as OleDbDataAdapter2.SelectCommand.Parameters("state"), and I'll set it to the state the user has selected in the combo box, then use the data adapter to fill the form's main dataset, like this:

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—now we're using SQL parameters with bound controls to let the user select the author's state. In fact, what we've created here is like a master/detail example, letting the user filter the records they want to see, but in this case, we're only using one data table. We'll see a true master/detail example soon—see "Using Master/Detail Relationships and Data Relation Objects" in the Immediate Solutions. Now that we're creating data-entry forms, I'll also take a look at validating data in controls with built-in events like Validating and Validated in this chapter—see "Performing Data Validation in Controls" in the Immediate Solutions.

And now it's time to start working with the Immediate Solutions.