Using SQL Parameters in VB.NET

Using SQL Parameters

In VB.NET, we have Windows controls that may provide parameters for filtering data from the database. A common scenario might be searching for some data from the Windows form and then populating a DataGrid with the results. Sql Parameters help us to filter data. We will follow the usage of Sql Parameters in VB .NET with a sample application. To start, open a blank Microsoft Access database, name it as Sample. Create a table, Table1 in Sample database with the columns, Business Name, Name, Order ID and State. Enter some values in the table and close it. Make sure you enter atleast 3 different state names when you fill the table with values. The following sample application will use two data adapters and two datasets to let the user select a state from a ComboBox and display data relating to that state in a data grid. When the form loads, the states from the table load into the dataset bound to the combo box. The user can select a state from the combo box and click the load button on the form to load all the data relating to that state into a second dataset, whose data is displayed in a data gird.

Loading the state data from table1 is fairly simple with the first data adapter. Drag a OleDb Data Adapter from the data tab of the toolbox onto the form. The Data Adapter Configuration Wizard opens and you are required to configure it. You can have a look at the Data Adapter Configuration Wizard here. While configuring the data adapter, the Generate Sql Statements dialog box should be as follows: Select DISTINCT State from Table1 as shown in the image below.



Note that we used the DISTINCT keyword in the SQL Statement above. The distinct keyword is used to load a unique state in the dataset. No state will be displayed more than once. After you finish configuring the data adapter, you need to generate the dataset. To generate a dataset, DataSet11 select Data->Generate Dataset from the main menu.

After the user selects a state in the ComboBox and clicks the load button the data should load into the data grid. To load data into the data grid you need to use the second data adapter, OleDb DataAdapter2. From the data tab of the toolbox drag an OleDb DataAdapter, OleDb Data Adapter2 onto the form and configure it. While configuring the data adapter the Generate Sql Statements dialog box should be as follows:
Select Business Name, Name, Order ID from Table1 WHERE (state=?) as shown in the image below.



Note the above said line of code. Here, we are using the SQL Parameter indicated by the ? mark for the state field in a WHERE clause in the SQL for the second data adapter. The Sql Parameter used will display data related to the state we select in the combo box in the data grid. After you finish configuring the data adapter, you need to generate the dataset. To generate a dataset, DataSet21 select Data->Generate Dataset from the main menu.

Now, drag a ComboBox, a Button and a DataGrid control onto a new form. Select the ComboBox, open it's properties and set the DataSource property to DataSet11 and DisplayMember property to Table1.State. Select the DataGrid and in the properties window set the DataMember property to Table1 and DataSource property to DataSet21. Switch to code view and paste following code.

Private Sub Form2_Load(ByVal sender As System.Object,_
ByVal e As System.EventArgs) Handles MyBase.Load
DataSet11.Clear()
OleDbDataAdapter1.Fill(DataSet11)
End Sub

Private Sub Button1_Click(ByVal sender As System.Object,_
ByVal e As System.EventArgs) Handles Button1.Click
OleDbDataAdapter2.SelectCommand.Parameters("state").Value = ComboBox1.Text
'placing a value into the SQL parameter corresponding to the state field
DataSet21.Clear()
OleDbDataAdapter2.Fill(DataSet21)
End Sub

After you are done with the code run the form, select a state from the ComboBox and click the button. The data relating to that particluar state will be displayed in the datagrid as shown in the image below.