dbEditAddSQL | dbEditDeleteSQL | dbEditUpdateSQL

Multiple source editing. At this time only tables form the same connections are supported.

Property Syntax

Obj.dbEditAddSQL="(|;+)AddSQL1;scope1;Reserved | AddSQL2;scope2;Reserved ... repeat
Obj.dbEditDeleteSQL="(|;+)DeleteSQL1;scope1;Reserved | DeleteSQL2;scope2;Reserved ... repeat
Obj.dbEditUpdateSQL="(|;+)UpdateSQL1;scope1;;Reserved | UpdateSQL2;scope2;Reserved ...repeat

Parameters
 

Parameters

Description

(|;+) Default Group, field and special delimiter. Special delimiter is reserved for future use.
SQLs Valid SQL statements performing functions with two type of embedded tags (criteria and Input tags). See explanation below.
Scope "" (Blank), Single or Multiple. Blank and Single both means single. All other text implies multiple. Single means "Records Affected" of this particular SQL allowed = 1 and multiple means Lrecs >1. If records affected is >1 in a single scope then the query is considered to fail. If one SQL fails then the entire multiple SQL editing session will be rolled back. Records affected = 0 is considered as success !
Reserved Reserved for future additional properties.

 

Example

 Multiple Edit SQL statement - Criteria tags and Input tags

The edit SQL statement is a regular SQL statement with two type of embedded tags - Criteria tags and Input tags. Look at the following sample SQL statement with the tags in place -

X.dbEditUpdateSQL="(|;)UPDATE Names SET LastName='#LASTNAME#', FirstName='#FIRSTNAME#' WHERE ID=[[ID]] | UPDATE Address SET Address='#Address#' WHERE ID=[[ID]]"

X.dbEditAddSQL="(|;)INSERT INTO Names (ID, LastName, FirstName) VALUES (#ID#, '#LastName#', '#FirstName#') | INSERT INTO Address (ID, Address) VALUES (#ID#, '#Address#')"

X.dbEditDeleteSQL="(|;)DELETE FROM Names WHERE ID = [[ID]] | DELETE FROM Address WHERE ID = [[ID]]"

These are normal SQL statements except that some of the values are replaced by tags. The ones wrapped by the (#) are the criteria tags. A criteria tag is actually a regular MagicCell type macro tag. This tag will be replaced by the values of the current record. One requirement is that the #Fieldname# MUST be a fieldname and cannot be a field index. Also note that the appropriate wrapper of this macro tag MUST be in place. During the edit SQL construction stage, this value will be replaced by the current record field values. Another type of tags are the ones wrapped by the [[...]]. These are the Input tags. Input tags will be replaced by the values on the Edit Input Form once the submit button is hit.

Pay attention to the two editing events - 

  1. When user hit the editing buttons to enter the edit screen.
    Before entering the editing screens, the criteria tags will be all replaced by current field values.
  2. After user filled in the input boxes in the editing screens and hit Submit.
    Right after the Submit button, the Input tags will be replaced by the Input form values.

The above two events will construct the proper SQL statements ready for execution. While the statement might contain multiple statements delimited by the Group delimiter, ASP-db will loop through and execute each statement accordingly. If you understand the above two events, you'll see that the relationship between the current table, current record, input screens and input fields and value of input fields are really not as tight as it used to be. The goal is to extract values from the two states to construct a valid SQL statement for execution.

Example - Multi-table edit - ASP file

<%
Set X= Server.CreateObject("ASP.DB")
X.dbQuickProps="(;,)3;MultipleEdit.mdb; Names; Grid;; std; http://localhost/aspdb2000/vdata/images/;;-1;;;;FALSE"
X.dbEditParams = "UpdateIcon=True, DeleteIcon=True"
X.dbNavigationItem = "Top,Prev,Next,Bottom,GridRow,Reload,Update,Add,filter"
X.dbButtonImages=",add.gif,update.gif,delete.gif,reset.gif,Cancel.gif"
X.dbGridIncMax=100
X.dbSQL="SELECT Address.ID, Address.Address, Names.FirstName, Names.LastName FROM Address INNER JOIN [Names] ON Address.ID = Names.ID"
X.dbEditUpdateSQL="(|;)UPDATE Names SET LastName='#LASTNAME#', FirstName='#FIRSTNAME#' WHERE ID=[[ID]] | UPDATE Address SET Address='#Address#' WHERE ID=[[ID]]"
X.dbEditUpdateTemplate="Update2Tables.htm"
X.dbEditAddSQL="(|;)INSERT INTO Names (ID, LastName, FirstName) VALUES (#ID#, '#LastName#', '#FirstName#') | INSERT INTO Address (ID, Address) VALUES (#ID#, '#Address#')"
X.dbEditAddTemplate="Add2Tables.htm"
X.dbEditDeleteSQL="(|;)DELETE FROM Names WHERE ID = [[ID]] | DELETE FROM Address WHERE ID = [[ID]]"
X.dbEditDeleteTemplate="Delete2Tables.htm"

X.ASPdb
%>


Example - Multi-table edit - Add template file = Add2Tables.htm

Add records to 2 tables<BR>

[[FormTag]]
ID: <INPUT TYPE="Text" NAME="ID" SIZE=5 VALUE=[[ID]]><BR>
FIRST NAME: <INPUT TYPE="Text" NAME="FIRSTNAME" VALUE=[[FIRSTNAME]]><BR>
LAST NAME: <INPUT TYPE="Text" NAME="LASTNAME" VALUE=[[LASTNAME]]><BR>
ADDRESS: <INPUT TYPE="Text" NAME="ADDRESS" SIZE=50 VALUE=[[ADDRESS]]><P>
[[ReturnButton]] [[AddSubmitButton]] [[ResetButton]] 
[[/FORMTag]]


Output


Notes

Look for the example files in the sub-directory datafile\Multi.