Share via

Create parameterized TableAdapter queries

  • Article

Note

This article applies to Visual Studio 2015. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

A parameterized query returns data that meets the conditions of a WHERE clause within the query. For example, you can parameterize a customer list to display only customers in a certain city by adding WHERE City = @City to the end of the SQL statement that returns a list of customers.

You create parameterized TableAdapter queries in the Dataset Designer. You can also create them in a Windows application with the Parameterize Data Source command on the Data menu. The Parameterize Data Source command creates controls on your form where you can input the parameter values and run the query.

Note

When constructing a parameterized query, use the parameter notation that's specific to the database you're coding against. For example, Access and OleDb data sources use the question mark '?' to denote parameters, so the WHERE clause would look like this: WHERE City = ?.

Note

The dialog boxes and menu commands you see might differ from those described in Help, depending on your active settings or the edition you're using. To change your settings, go to the Tools menu and select Import and Export Settings. For more information, see Customizing Development Settings in Visual Studio.

Create a parameterized TableAdapter query

  • Create a new TableAdapter, adding a WHERE clause with the desired parameters to the SQL statement. For more information, see Create and configure TableAdapters.

    -or-

  • Add a query to an existing TableAdapter, adding a WHERE clause with the desired parameters to the SQL statement.

Create a parameterized query while designing a data-bound form

  1. Select a control on your form that is already bound to a dataset. For more information, see Bind Windows Forms controls to data in Visual Studio.

  2. On the Data menu, selectAdd Query.

  3. Complete the Search Criteria Builder dialog box, adding a WHERE clause with the desired parameters to the SQL statement.

Add a query to an existing data-bound form

  1. Open the form in the Windows Forms Designer.

  2. On the Data menu, select Add Query or Data Smart Tags.

    Note

    If Add Query is not available on the Data menu, select a control on the form that displays the data source you want to add the parameterization to. For example, if the form displays data in a DataGridView control, select it. If the form displays data in individual controls, select any data-bound control.

  3. In the Select data source table area, select the tablethat you want to add parameterization to.

  4. Type a name in the New query name box if you are creating a new query.

    -or-

    Select a query in the Existing query name box.

  5. In the Query Text box, type a query that takes parameters.

  6. Select OK.

    A control to input the parameter and a Load button are added to the form in a ToolStrip control.

    TableAdapter parameters can be assigned null values when you want to query for records that have no current value. For example, consider the following query that has a ShippedDate parameter in its WHERE clause:

    SELECT CustomerID, OrderDate, ShippedDate
FROM Orders
WHERE (ShippedDate = @ShippedDate) OR
(ShippedDate IS NULL)

If this were a query on a TableAdapter, you could query for all orders that have not been shipped with the following code:

ordersTableAdapter.FillByShippedDate(northwindDataSet.Orders, null);

OrdersTableAdapter.FillByShippedDate(NorthwindDataSet.Orders, Nothing)

Enable a query to accept null values

  1. In the Dataset Designer, select the TableAdapter query that needs to accept null parameter values.

  2. In the Properties window, select Parameters. Then press the ellipsis (…) button to open the Parameters Collection Editor.

  3. Select the parameter that allows null values and set the AllowDbNull property to true.

See also