Changing Multiple Tables with a Single Set of Pages

One normally does an insert with a "form page" where the user enters data and an "action page" which writes the form input to the database.  One normally does a modify with three pages: a display page which displays multiple records with some mechanism for the user to select a single record, a form page which displays the current values from the database for the chosen record, and an "action page" which does the modification.

If one wants to be able to write to multiple tables rather than to just a single table, it makes it easier to maintain the code (i.e. make future changes), if this can be done with a single set of pages (2 for insert, 3 for modify) rather than using a separate set for each table.  This means that the table name will have to be known to all pages so that the action page in the insert case knows which table to use and the form and action pages in the modify case know which table to use.  The following discussion assumes that one will pass this information via query strings, however the essential points apply regardless of how this information is shared.  

Here is what one needs to do to fix this for the modify case -- the insert case is similar, but easier.

1) In the first page, you have formed one link per table to pass the primary key value of the selected record to the second page. One solution is to expand this link to include the tablename. You can just hardcode it as follows: 

<a href="modifyform.asp?id=<% =events("id")%>&tablename=events">Modify<a>

In a URL, each name=value pair is separated by a & and no spaces are allowed.

2) The second page then needs to use this tablename (that you just been passed in #1) in the SQL statement. There are several possible ways to do this. One way is to write 3 if statements to set the right value for the SQL  string which you will then pass to the Execute method, e.g.

if (Request.QueryString("tablename")== "events") 
str = "SELECT * from events WHERE id=" + id);
if (Request.QueryString("tablename")== "onlinetraining") 
str = "SELECT * from onlinetraining WHERE id=" + id);
if (Request.QueryString("tablename")== "resources") 
str = "SELECT * from resources WHERE id=" + id);
rsItem = ConnCW.Execute(str);

Another way is to write only one str statement and use the tablename in this, e.g.

str = "SELECT * from " + Request.QueryString("tablename") + " WHERE id=" + id;
rsItem = ConnCW.Execute(str);

3) The second page then needs to pass this tablename to the third page. You are submitting the information in a form on the second page.  If you display the tablename in one of the form elements, the user could change it, which you don't want to have happen. Therefore you want to transmit the tablename to the next page as a hidden form field, the same as you are doing for the primary key value. Thus you would write:

<input type="hidden" name="tablename" value="<% =Request.QueryString(tablename)%>">

4) The third page then needs to read the tablename and then use it in the UPDATE statement. You can either write 3 if statements as in #2 or write one if statement that incorporates the table name, as we did in #2.  Here "title" and "description" are the name of the fields in the tables.

The if statements:

if (Request.QueryString("tablename")=="events") 
str = "UPDATE events SET title='" + Request.QueryString("title") + "', description ='";
str+= Request.QueryString("description") + "' WHERE id=" + Request.QueryString("id");

if etc.

The one statement approach:

str = "UPDATE " + Request.QueryString("tablename") + " SET title='";
str += Request.QueryString("title") + "', description = '" + Request.QueryString("description");
str += "' WHERE id=" + Request.QueryString("id");

All of the above assumes that the field names are the same in all 3 tables. If they aren't, the "one statement" approach has to handle this as well and it may then be simpler to write the 3 if statements.

Revised: October 18, 2002 Comments to William Pegram, wpegram@nvcc.edu