Using the Query Designer

The Query Designer—a SQL Builder add-in for Visual InterDev—is a member of the Microsoft Visual Database Tools product family and is a key companion to the Data Environment in both Visual InterDev and Visual Basic. The Query Designer provides visual design tools to create SQL statements for performing queries, updates, and other tasks on any ODBC-compliant database. The Query Designer lets you perform the following tasks:

• Create queries to retrieve, update, insert, and delete data from any ODBC-compliant database
• Select tables, views, and columns for a query
• Specify how to order query results
• Build Where clauses that specify what values to search for
• Preview query results in the Results pane
• Join tables to create multitable queries
• Edit databases by inserting, updating, or deleting rows
• Create special-purpose queries, such as parameter queries in which search values are provided when the query is executed
• Enter SQL statements directly or edit the SQL statements created by the Query Designer
• Create back-end-specific SQL statements to take advantage of a particular database's features
• Execute Microsoft SQL Server stored procedures

The Query Designer Interface

The Query Designer interface is built around four panes, or windows, as described in Table 5-1.

You can control which panes are currently displayed by using the buttons on the Query toolbar, shown in Figure 5-17. The buttons are toggles that turn a pane's display on or off. Figure 5-17 shows the Query Designer with the SQL statement for the GetCustomers data command object.

The Query Designer supports drag-and-drop and cut-and-paste operations to the Clipboard. For example, the interactive interface lets you drag a column from the Diagram pane and drop it onto the Grid pane.

Table 5-1. The graphical panes of the Query Designer interface.

Query Designer Pane	Description
Diagram	Displays the input sources (tables or views) that you are querying
Grid	Contains a spreadsheet-like grid in which you specify query options, such as which data columns to display, how to order the results, which rows to select, and how to group rows
SQL	Displays the SQL statement for the current query and lets you create your own SQL statements
Results	Shows a grid with the results of the most recently executed query

Figure 5-17. The Query Designer interface is based upon four windows (from top to bottom): Diagram, Grid, SQL, and Results.

Creating Queries with the Query Designer

You can create a query and/or display the Query Designer interface in several ways. You can open the Query Designer for an existing table or you can view it in Data View.

To open the Query Designer for a table or a view, take the following steps:

1. Display the tables or views in the Data View window.
2. Double-click a table or a view to open the Query Designer for that object.

These two steps open the Results pane for the selected table or view and display the data from the query. The Results pane lets you edit and delete the data returned by the query. You cannot access the Query Designer's other features because they are not useful for a table or a view. You can, however, use the Query toolbar to open the other panes.

If you open the Diagram or SQL pane, you can create an entirely new query. After you change the query definition, the query is no longer based on the table with which you opened the Query Designer. You can save the query to a file by clicking the Standard toolbar's Save button. The resulting .dtq file is an ASCII description of the query that you can add to a database project.

To open the Query Designer for the Recordset DTC, follow these steps:

1. Display the properties for the Recordset DTC by right-clicking the DTC and choosing Properties from the context menu.
2. Click the General tab of the Recordset Properties dialog box.
3. Select the SQL Statement option, and then click the SQL Builder button.

To open the Query Designer for a data command object, do the following:

• Right-click the data command in Project Explorer, and select SQL Builder from the context menu.

You can also open the Query Designer for a data command object by clicking the SQL Builder button on the data command Properties dialog box.

The query you create for a Recordset DTC or a data command object automatically updates the SQL for the object when you save or close the query.

To add input sources (tables and views) to the Query Designer, follow these steps:

1. Display the Data View window of the Project Workspace.
2. Drag the table or view, and drop it onto the Query Designer Diagram pane.

You can also drag a database diagram from the Database Designer and drop it onto the Query Designer. This step inserts all the tables in the diagram into the Query Designer. This is a quick way to get several tables into the Query Designer when you already have a database diagram containing the tables you need.

Once the Query Designer is open, you can quickly create a query. Just add the tables from which you want to retrieve the data, and then follow these steps:

1. Select the columns to use in the query by dragging them to the Grid pane or by selecting the check box next to the column name.
2. Enter any criteria for the query, such as filters or expressions.
3. Test the query by clicking the Run Query button on the Query toolbar.