Use ASP commands to modify database in Dreamweaver

In Dreamweaver, open the ASP page that will run the command.

Open the Server Behaviors panel (Window > Server Behaviors), click the Plus (+) button, and select Command.

Enter a name for the command, select a connection to the database that contains the records you want to edit, and select the editing operation that you want the command to perform—Insert, Update, or Delete.

Dreamweaver starts the SQL statement, based on the type of operation you select. For example, if you select Insert, the dialog box looks like the following example:

Complete the SQL statement.

For information on writing SQL statements that modify databases, consult a Transact‑SQL manual.

Use the Variables area to define any SQL variables. Provide the name and run-time value. Specifying the type and size of each variable prevents injection attacks.

The following example shows an Insert statement that contains three SQL variables. The values of these variables are provided by URL parameters passed to the page, as defined in the Run‑Time Value column of the Variables area.

To get the Size value, use the Databases panel in Dreamweaver. Find your database in the Databases panel and expand it. Next, find the table you’re working with and expand it. The table lists the sizes for your fields. For example, it might say ADDRESS (WChar 50). In this example, 50 is the size. You can also find the size in your database application.

To determine the Type value, see the following table:

Type in database	Type in Dreamweaver	Size
Numeric (MS Access, MS SQL Server, MySQL)	Double	-1
Boolean, Yes/No (MS Access, MS SQL Server, MySQL)	Double	-1
Date/Time (MS Access, MS SQL Server, MySQL)	DBTimeStamp	-1
All other types of text fields, including the MySQL text data types char, varchar and longtext	LongVarChar	check database table
Text (MS Access) or nvarchar, nchar (MS SQL Server)	VarWChar	check database table
Memo (MS Access), ntext (MS SQL Server), or fields that support large amounts of text	LongVarWChar	1073741823

For more information on the type and size of SQL variables, see www.adobe.com/go/4e6b330a.

Close the dialog box.

Dreamweaver inserts ASP code in your page that, when run on the server, creates a command that inserts, updates, or deletes records in the database.

By default, the code sets the Prepared property of the Command object to true, which makes the application server reuse a single compiled version of the object every time the command is run. To change this setting, switch to Code view and change the Prepared property to false.

Create a page with an HTML form so users can enter record data. In the HTML form, include three text fields (txtCity, txtAddress, and txtPhone) and a submit button. The form uses the GET method and submits the text field values to the page that contains your command.

In Dreamweaver, open the page that will run the stored procedure.

In the Bindings panel (Window > Bindings), click the Plus (+) button, and then select Stored Procedure.

In the Data Source pop‑up menu, select a connection to the database containing the stored procedure.

Enter the ColdFusion Data Source user name and password.

Select a stored procedure from the Procedures pop‑up menu.

Dreamweaver automatically fills in any parameters.

Select a parameter, and click Edit if you have to make changes.

The Edit Stored Procedure Variable dialog box appears. The name of the variable you are editing appears in the Name box.

Make changes as necessary:

• Select a Direction from the pop‑up menu. A stored procedure might have input values, output values, or both input and output values.

• Select a SQL type from the pop‑up menu. Enter a return variable, a run-time value, and a test value.

If the stored procedure takes a parameter, click the Plus (+) button to add a page parameter.

Click the Plus (+) button again to add another page parameter, if necessary.

Select a page parameter, and click the Minus (-) button to delete the parameter if necessary or click Edit to make changes to the parameter.

Select the Returns Recordset Named option, and then enter a name for the recordset; if the stored procedure returns a recordset, click the Test button to see the recordset that the stored procedure returns.

Dreamweaver runs the stored procedure and displays the recordset, if any.

Select the Returns Status Code Named option, enter a name for the status code, if the stored procedure returns a status code return value. Click OK.

After you close the box, Dreamweaver inserts ColdFusion code in your page that calls a stored procedure in the database, when the code runs on the server. The stored procedure in turn performs a database operation, such as inserting a record.

If the stored procedure takes parameters, you can create a page that gathers the parameter values and submits them to the page with the stored procedure. For example, you may create a page that uses URL parameters or an HTML form to gather parameter values from users.

In Dreamweaver, open the page that will run the stored procedure.

In the Bindings panel (Window > Bindings), click the Plus (+) button, and then select Command (Stored Procedure).

The Command dialog box appears.

Enter a name for the command, select a connection to the database containing the stored procedure, and then select Stored Procedure from the Type pop‑up menu.

Select your stored procedure by expanding the Stored Procedures branch in the Database Items box, selecting the stored procedure from the list, and clicking the Procedure button.

Enter any required parameters in the Variables table.

You don’t need to enter any parameters for any RETURN_VALUE variable.

Click OK.

After you close the dialog box, ASP code is inserted in your page. When the code runs on the server, the code creates a command object that runs a stored procedure in the database. The stored procedure in turn performs a database operation, such as inserting a record.

By default, the code sets the Prepared property of the Command object to true, which makes the application server reuse a single compiled version of the object every time the stored procedure is run. If you know the command will be executed more than a few times, having a single compiled version of the object can improve the efficiency of database operations. However, if the command will only be executed one or two times, using one might actually slow down your web application because the system has to pause to compile the command. To change the setting, switch to Code view and change the Prepared property to false.

If the stored procedure takes parameters, you might create a page that gathers the parameter values and submits them to the page with the stored procedure. For example, you may create a page that uses URL parameters or an HTML form to gather parameter values from users.