INSERT, UPDATE & DELETE records in multiple tables
Dreamweaver / ASP
You can use the Dreamweaver Ultradev or MX Command (Stored Procedure) server
behaviour to set up record inserts updates and deletions. The advantage of using
this method is that multiple insert, update and delete commands can be used on
the same page allowing values from a single form to be inserted, updated or deleted
in multiple database tables in the same instance. Additionally different combinations
of insert, update and delete commands may be executed from the same page.
This method can be used with Access and SQL Server databases. However if you
are using a SQL Server database, if possible, you should setup all INSERT, UPDATE,
DELETE commands as well as SELECT statements in Stored Procedures within the
database itself.
On the page in which you wish to perform the INSERT select Command
(Stored Procedure) from the Data Bindings window.
Give your INSERT command a name, select the connection and select Insert from
the Type list.
Dreamweaver can help you build the INSERT command. Click the + symbol
next to Tables in the Database Items box to display a list of tables in you database.
Click the + symbol next to the table into which
you wish to insert values. Now click on a column (fields) into which you want
to insert a value and then click the COLUMN button,
repeat this for each field you into which you want to insert a value, you should
see the results in the SQL box. Alternatively you can type the INSERT command
directly in the SQL box, either way you should end up with an insert command
something like:
INSERT INTO tableName(Field1, Field2, Field3) VALUES('Joe', 'Bloggs', 12345)
This will insert the literal values Job into Field1, Bloggs into Field2 and 12345
into Field3. Notice how text values are surrounded by single quotes, numeric
values without.
If you wish to insert variables or the value submitted from a form click the + button
above the variables box and add the variable in a way similar to setting up variables
for a recordset, providing a variable name and a run-time value, for example:
Variables
Name
Run-time Value
strSomeText
Request("TextFieldOneName")
strMoreText
Request("TextFieldTwoName")
intSomeNumber
Request("ListMenuName")
Which would produce an INSERT command like this:
INSERT INTO tableName(Field1, Field2, Field3) VALUES('strSomeText', 'strMoreText',
intSomeNumber)
Notice that variables containing text values are surrounded by single quotes,
in the same way as literal text values.
On the page in which you wish to perform the UPDATE select Command
(Stored Procedure) from the Data Bindings window.
Give your UPDATE command a name, select the connection and select UPDATE from
the Type list.
Similar to the INSERT command, Dreamweaver can help you build the UPDATE command.
Click
the + symbol
next to Tables in the Database
Items box to display a list of tables in you database.
Click the + symbol next to the table in which
you wish to update values. Now click on a column (fields) in which you wish to
update a value and then click the COLUMN button,
repeat this for each field you wish to update, you should see the results in
the SQL box. For the update command you also have to supply a WHERE clause, click
on the field in the database items box and click
the WHERE button. Alternatively you can type
the UPDATE command directly in the SQL box, either way you should end up with
an update command something
like:
UPDATE tableName SET Field1= 'Joe', Field2 = 'Bloggs' Field3 = 12345
WHERE Field4
= 7
This will update Field1 to Joe, Field2 to Bloggs and Field3 to 12345 in every
row of the table where Field 4 contains the value 7. Notice how text values are
surrounded
by single quotes, numeric values without.
If you wish to update fields with the values stored in variable or the value
submitted
from
a
form click the + button above the variables box
and add the variable in a way similar to setting up variables for a recordset,
providing a variable name and a run-time value,
for example:
Variables
Name
Run-time Value
strSomeText
Request("TextFieldOneName")
strMoreText
Request("TextFieldTwoName")
intSomeNumber
Request("ListMenuName")
intAnotherNumber
Request("RadioButtonName")
Which would produce an UPDATE command like this:
UPDATE tableName SET Field1= 'strSomeText', Field2 = 'strMoreText' Field3 = intSomeNumber
WHERE Field4
= intAnotherNumber
Notice that variables containing text values are surrounded by single quotes,
in the same way as literal text values.
On the page in which you wish to perform the UPDATE select Command
(Stored Procedure) from the Data Bindings window.
Give your DELETE command a name, select the connection and select DELETE from
the Type list.
Similar to the INSERT and UPDATE commands, Dreamweaver can help you build the
DELETE
command.
Click
the + symbol
next to Tables in the Database
Items box to display a list of tables in you database.
Click the + symbol next to the table in which
you wish to update values. Now click on a column (fields) in which you wish to
update a value and then click the COLUMN button,
repeat this for each field you wish to update, you should see the results in
the SQL box. For the delete command you also have to supply a WHERE clause, click
on the field in the database items box that
you want to use and click the WHERE button. Alternatively
you can type the DELETE command directly in the SQL box, either way you should
end up with a delete command something
like:
DELETE FROM tableName WHERE Field4
= 7
This will delete every
row in the table where Field 4 contains the value 7.
If you wish to delete fields by supplying the WHERE clause with a value stored
in
variable
or
a
value
submitted
from
a
form, click the + button above the variables
box and add the variable in a way similar to setting up variables for a recordset,
providing a variable name and a run-time value,
for example:
Variables
Name
Run-time Value
intRecordID
Request("RadioButtonName")
Which would produce an DELETE command like this:
DELETE FROM tableName WHERE Field4
= intRecordID
As with the INSERT and UPDATE commands, both literal and variable values that
contain text should be surrounded with single quotes, for example:
If you switch to code view, you will see that each of the commands generates
a
block of code similar to that shown below, in fact only the Command1.CommandText line
will vary according to whether you set up an insert, update or delete command.
This code will execute as soon as the page loads, which can be useful on occasion,
but we usually only want to perfom record modifications as the result of a
form being submitted. To do this simply put the command within a condition,
for example:
<% If Request("SubmitButtonName") <> "" Then
%>
<%
COMMAND CODE
%>
<% End If %>
You can also set up multiple commands on a single page in order to insert,
update or delete records in different tables in one instance, for example:
<% If Request("SubmitButtonName") <> "" Then
%>
<%
COMMAND CODE - INSERT INTO TABLE1
%>
<%
COMMAND CODE - INSERT INTO TABLE2
%>
<% End If %>
You can also use the commands in conjunction with the retrieve
inserted record identity code in order to insert the new identity into
a second table, which is useful if you need to maintain a relationship between
two or more tables, for example:
<% If Request("SubmitButtonName") <> "" Then
%>
<%
INSERT INTO TABLE1
RETRIEVE NEW RECORD IDENTITY
%>
<%
COMMAND CODE - INSERT INTO TABLE2(FIELD1, etc.) VALUES(NEW RECORD IDENTITY,
etc.)
%>
<% End If %>
