How to: Create Parameterized Queries (Devices)

  • Article

The following steps assume that you have a SQL Server Compact 3.5 database available in the Data Sources window. For more information, see How to: Create a Database (Devices) or How to: Add a Database to a Device Project.

When you want users to be able to enter different values for a parameter, use a question mark ("?") as the parameter when you design your query. If you create your query by using the smart tag on the Windows Forms Designer, as shown in the following set of steps, a user interface is automatically generated in the Windows Form. If you create your query from the TableAdapter in the Dataset Designer, as shown in the last set of steps, no user interface is automatically generated.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

To set up for specifying a parameter using the Windows Forms Designer

  1. Drag a table in Datagrid or Details format from the Data Sources window onto the form in the designer.

    You can select the format by clicking the arrow at the right of the table name.

  2. Click the smart tag on the dragged component, and then click Add Query.

    The keyboard shortcut to open the Tasks dialog box is Shift+Alt+F10.

  3. In the Search Criteria Builder dialog box, select New query name.

    Use the default name or create a name of your choice.

  4. You can now specify your parameters by changing the SQL statement in the Query Text box or by clicking Query Builder.

To specify a parameter using the Query Text box

  1. Add a WHERE clause to the end of the SELECT statement.

  2. Click OK to close the Search Criteria Builder dialog box.

    A query-bound button is displayed on the form in the designer.

To specify a parameter using Query Builder

  1. In the Query Builder dialog box, either:

    • Add a WHERE clause in the SQL Statement pane.

      -or-

    • Type your parameter under Filter in the appropriate Column listing.

      This approach writes the WHERE clause for you in the SQL Statement pane.

  2. Click OK to close the Query Builder dialog box.

  3. Click OK to close the Search Criteria Builder dialog box.

    A query-bound button is displayed on the form in the designer.

To specify a parameter using the Dataset Designer

  1. In Solution Explorer, right-click the .xsd file, and then click Open.

  2. In the Dataset Designer, right-click the TableAdapter, point to Add, and then click Query.

  3. In the TableAdapter Query Configuration Wizard, select Use SQL Statements, and then click Next.

  4. On the Choose a Query Type page, select Select which returns a single value, and then click Next.

  5. On the Specify a SQL SELECT statement page, click Query Builder.

    If you want, you can add the WHERE clause here.

  6. Use Query Builder as described earlier in this topic.

    Note

    No user interface elements are automatically generated when you create your queries by using the TableAdapter Query Configuration Wizard.

See Also

Tasks

How to: Add a Parameterized Query to a Form in a Windows Application

Walkthrough: A Parameterized Query Application

Concepts

Query and View Designer Tools

Using SQL Server Compact 3.5 Databases (Devices)

Reference

Search Criteria Builder Dialog Box