The Query Designer

The Query Designer allows you to create SQL commands using a visual interface and to view and edit the results of your queries. We had an introduction to the Query Designer in Chapter 5. In this section, we'll look at creating insert, update, and delete queries. We'll also examine parameter queries. All of these queries can be constructed in the Query Designer and saved in your data command objects or Recordset DTCs for use within your Visual InterDev applications. To launch the Query Designer from your data command object or Recordset DTC, specify SQL Statement as the source of the data and then click the SQL Builder button.

Creating Update Queries

To create an update query, take the following steps:

1. Add the table you want to update to the Diagram pane.
2. Click the Change Type button, and select Update from the Query toolbar.
3. Add the columns you want to update to the Grid pane.
4. Add your update values.
5. In the Criteria column, set the criteria to use for selecting the rows to update.
6. Verify the SQL syntax by clicking the Verify SQL Syntax button on the Query toolbar.
7. Execute or save your query.

Figure 12-13 shows the Query Designer with an update query. The update statement in the figure changes the account_no field to 123401 for each row that is currently set to 123402.

Figure 12-13. The SQL pane shows the update query that the Query Designer generates from the information in the Grid pane.

Creating Insert Queries

There are two types of insert queries you can define: insert and insert values. The insert query uses a SELECT statement to gather the result set for insertion into the table. It can therefore be a multirow insert statement. The following code shows a sample insert query:

INSERT INTO "Account_Code"     (Account_Id, Account_Description) SELECT Account_Id, Account_Description FROM Account_Code_Tmp 

The insert values query does not use a SELECT statement and inserts specific values into a single new record. The following code shows a sample insert values query:

INSERT INTO Account_Code     (Account_Id, Account_Description) VALUES (5, 'Personal Loan') 

To create an insert query, take the following steps:

1. Add the table you want to insert in the Diagram pane.
2. Click the Change Type button, and select Insert or Insert Values from the Query toolbar.
3. If you have selected Insert, select the table name from the Insert Into Table dialog box and click OK. The table you selected in step 1 becomes the source table for the insert query.
4. Add the columns you want to insert in the Grid pane.
5. Add your update values.
6. Verify the SQL syntax by using the Verify SQL Syntax button on the Query toolbar.
7. Execute or save your query.

Figure 12-14 shows the Query Designer with an insert values query.

Figure 12-14. The SQL pane shows the insert values query that the Query Designer generates from the information in the Grid pane.

Creating Delete Queries

To create a delete query, take the following steps:

1. Add the table in which you want to delete records to the Diagram pane.
2. Click the Change Type button on the Query toolbar, and select Delete.
3. In the Criteria column, set the criteria to use for selecting the rows to delete.
4. Verify the SQL syntax by clicking the Verify SQL Syntax button on the Query toolbar.
5. Execute or save your query.

SQL delete queries do not use columns; they use only the name of the table to delete the row from, and they use a WHERE clause to determine which rows to delete. You can delete the row inserted by the earlier insert values query with this statement:

DELETE FROM Account_Code WHERE (Account_Id = 5) 

This delete query deletes all rows in which the Account_Id field is equal to 5.

Parameterized Queries

Parameterized queries will be the most typical queries that you create for your Web applications. These queries take input from the application during run time in the form of a variable or number of variables and build the SQL statement from these input parameters. Input parameters are typically used to supply values for the WHERE clause of a SELECT, UPDATE, or DELETE statement or are used as input parameters for stored procedures.

To build a parameterized query, you simply use the Query Designer. Use a question mark (?) to indicate parameters within your query. Figure 12-15 shows the Properties dialog box of a data command object named Customer, with a parameterized query entered as a SQL statement.

Figure 12-15. A parameterized query for a data command object named Customer.

Once you have entered the SQL statement with the question mark, you can further define the type of parameter you want to supply to the query by moving to the Parameters tab of the Properties dialog box, as shown in Figure 12-16.

Figure 12-16. The Parameters tab of a data command object's Properties dialog box. Here you can further define your parameters.

Within this tab you can define the data types for your parameters and specify whether they are input, output, or input/output parameters. For SQL statements these parameters can only be input parameters. For stored procedures, however, you can choose any of the options. In Figure 12-16, you'll also notice that there is a Value textbox. Here you can enter any of the following:

• Literals Character values in single quotes or numeric values without quotes.
• Variables Name of a variable defined in server code that will contain the value you want to pass.
• Object References An object reference and property value such as Textbox1.value. The object must be available in server script. Also, note that the expression will be evaluated as a JScript expression.
• Expressions The expression is evaluated as a JScript expression and can be any combination of literals, variables, object references, and function calls.

If you enter expressions into the Value text box, be sure to use correct capitalization—the expressions are evaluated as JScript expressions and are therefore case sensitive. Use a plus sign (+) for concatenation and single quotation marks for string literals. You can also leave the Value text box blank and supply it later via a Recordset scripting object (that uses the data command object as its data source) and the setParameter method.

In addition to data command objects, you can also create parameterized queries within Recordset scripting objects. The Recordset DTC also includes a Parameters tab in its Properties dialog box where you can enter a value for your parameter.

Note

---

To pass parameters into a Recordset scripting object, you want to be sure that the parameter is specified before the underlying recordset opens. You do this either by using the onenter event for the page or the onbeforeopen event for the Recordset scripting object or by marking the recordset not to open automatically and then programmatically setting the parameter and opening the recordset. Chapter 14 goes into more detail on the various Recordset scripting object methods, properties, and events.

Figure 12-17 shows a parameterized query within the Query Designer. Creating these types of queries is easy: simply add a question mark to the criteria column in the SQL grid pane for the specific column or columns you want to work with.

Figure 12-17. A parameterized query within the Query Designer.

When you execute a parameterized query from within the Query Designer, you'll see a dialog box like the one shown in Figure 12-18. This dialog box asks you to define the query parameters prior to executing the query. After you have entered the relevant parameters, the results of the query are displayed as usual in the results pane within the Query Designer.

Figure 12-18. The Define Query Parameters dialog box that appears when you execute a parameterized query from within the Query Designer.