(1) download from multiple tables in a database to a page including repeat regions, update info, & upload to dbase, (2) add completely new records, AND (3) delete old records

Posted on 2006-04-10
Last Modified: 2010-04-03
I am
(1) dynamically filling a form on a webpage (including the use of repeat regions) from multiple tables in a database, make changes to that information, then upload the updated material back into the database tables,
(2) be able to add a completely new record to the database tables, AND
(3) delete old records from the database tables.

I am using Dreamweaver 8 with a Coldfusion server and an extension from WebAssist called DataAssist.

I have used the WebAssist extension to create admin pages that allow me to update a single table in a database.  However, all the information about a single product is spread across 5 tables in our database.  The extension only allows modification to 1 table at a time -- we want to combine it all into one set of 5 admin pages: search, results, update, add, and delete.

The site we are creating is for a company that sells Bible study materials in the forms of courses.  The tables are:
courses (contains a uniue courseID and any general info about the course -- e.g., title, number of lessons, instructor, etc),
categories (contains a unique categoryID and information about all of the 24 available categories),
courseCategories (desiginates which categoryID's a specific courseID fits into),
volumes (contains a unique volumeID and specific information for each volume (product) available in a specific courseID including its formatID, title, price, etc., and
formats (contains a unique formatID & title for all 9 available formats, i.e. 01, DVD; 02,Audio; etc.)

The challenge is that each courseID can have more than one categoryID attached, and have more than one volume (and each volume will have a single format that may be different from each other volume in a course).

I have been able to modify the search & results pages to allow access to all courses.
I have modified the update & details pages to display all information from the dbase on a specifc course AND modified the update page to generate text boxes & drop down menues that are prepopulated with the dbase info.  I do this by using repeat regions for the categories and volumes sections.

Now I am at a loss on how to (1) move this information back to the database, (2) create an entirely new record that will pull any autogenerated numbering from the database being used, and (3) how to remove an entire record (course), and all of its attendant information from every applicable table.

I am using thiscode to do my repeat for the categories (it is 1 row out of a 3 columnn table).

<cfset variables.catSelect = rsCourseCategories.categoryID>                          <!---this cfset is to make a variable for my cfloop filtered by courseID--->
<cfloop query="rsCourseCategories">                                                           <!---recordset to get the categories from table courseCategories--->
    <cfif catSelect NEQ rsCourseCategories.categoryID>                                   <!---adjusts the cfset variable to the next categoryID for the courseID--->
        <cfset variables.catSelect = rsCourseCategories.categoryID>
        <th class="WADADataTableHeader">Categories:</th>
        <td align="left" class="WADADataTableCell"><cfoutput></cfoutput></td>           <!---outputs the name for the categoryID--->
        <td class="WADADataTableCell">
            <select name="categoryID" id="categoryID">                                                                                           <!---selectable menu--->
                <cfoutput query="rsCategoriesSelection">                                                                                            <!---dynamic menu labels/values--->
                        <option value="#rsCategoriesSelection.categoryID#" <cfif (isDefined("catSelect") AND rsCategoriesSelection.categoryID EQ catSelect)>selected="selected"</cfif>></option>                                                                   <!---dynamic bind for menu selection--->

I am using this code to do my repeat for the volumes (it is a merged row containing a repeated 4 row, 8 column table).

    <th colspan="3" class="WADADataTableHeader">
    <cfset variables.intLastFormatID = rsVolumes.FormatID>                         <!---this cfset is to make a variable for my cfloop filtered by courseID--->
    <cfloop query="rsVolumes">                                                                  <!---recordset to get the categories from table courseCategories--->
        <cfif intLastFormatID NEQ rsVolumes.FormatID>
           <cfset variables.intLastFormatID = rsVolumes.FormatID>                 <!---adjusts the cfset variable to the next categoryID for the courseID--->
        <table width="100%" cellspacing="3">
            <tr align="left">                                                                            <!---table headers--->
                <td width="8%">VOLUME ID</td>
                <td width="13%">Format</td>
                <td width="11%">Display order </td>
                <td width="7%">Pieces</td>
                <td width="9%">Price</td>
                <td width="31%">Title</td>
                <td width="16%">Author</td>
                <td width="5%">Pages</td>
            <tr align="left">                                                                        <!---dynamic text from dbase--->
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.volumeID#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.label#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.displayOrder#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.pieces#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.price#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.title#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput></cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.pages#</cfoutput></span></td>
            <tr align="left">                                                                       <!---textboxes & menus containing dynamic info--->
                <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.volumeID#</cfoutput></span></td>
                <td><span class="WADADataTableCell">
                    <select name="formanottID" id="formanottID">
                        <option value="" <cfif (isDefined("intLastFormatID") AND "" EQ intLastFormatID)>selected="selected"</cfif>>(select)</option>
                        <cfoutput query="rsFormatsSelection">
                            <option value="#rsFormatsSelection.formatID#" <cfif (isDefined("intLastFormatID") AND rsFormatsSelection.formatID EQ intLastFormatID)>selected="selected"</cfif>>#rsFormatsSelection.label#</option>
                <td><span class="WADADataTableCell">
                    <input name="text" type="text" value="<cfoutput>#rsVolumes.displayOrder#</cfoutput>" size="5"></span></td>
                <td><span class="WADADataTableCell">
                    <input name="pieces" type="text" id="pieces" value="<cfoutput>#rsVolumes.pieces#</cfoutput>" size="3"></span></td>
                <td><span class="WADADataTableCell">
                    <input name="price" type="text" id="price" value="<cfoutput>#rsVolumes.price#</cfoutput>" size="7"></span></td>
                <td><span class="WADADataTableCell">
                    <input name="title" type="text" id="title" value="<cfoutput>#rsVolumes.title#</cfoutput>" size="50"></span></td>
                <td><span class="WADADataTableCell">
                    <input name="author" type="text" id="author" value="<cfoutput></cfoutput>" size="20"></span></td>
                <td><span class="WADADataTableCell">
                    <input name="pages" type="text" id="pages" value="<cfoutput>#rsVolumes.pages#</cfoutput>" size="4"></span></td>
            <tr align="left">
                <td colspan="8"><hr></td>                                                <!---spacer line--->

How do I (1) update the record in the database, (2) delete the record from the database, (3) add a new record to the database
Question by:Upekrithen
    LVL 1

    Accepted Solution

    well that's a lot of info. To add, update, and delete form multiple tables you will need to have multiple cfqueries, one for each table. But most of the db code will be done through sql. To have one form where the user can decide what action to perform, read below...

    What I do is create an update button (type=submit), delete button (type=submit), and add button (type=submit), and if you really want to create a done button or cancel button, but that is besides the point.

    Update button:
    <input type="submit" name="updatebutton" value="update info">

    Delete Button:
    <input type="submit" name="deletebutton" value="delete info">

    Add button:
    <input type="submit" name="addbutton" value="add info">

    Now since all these buttons are of type submit, and you can only have one page that the form will redirect the user, you need to have some if statements on the action page. e.g. <form name="form1" method="post" action="modifyDB.cfm">

    This is the form that will perform the update, deletion, or present the user with a form to add a new row into the tables. You also want to have a clause that ensures a bit of security, so that someone who just typed in a random url won't make any db changes.

    <cfif isdefined("form.updatebutton")>
        Place update query here

    <cfelseif isdefined("form.deletebutton")>
        Place delete code here

    <cfelseif isdefined("form.addbutton")>
        Place another form here for users to fill out

        No action performed. Return user to main page...


    For the update query you can break down the updates into multiple queries if neccesary, one for each table. Then update the tables with their respective information from the form.
    Same with delete section.

    Um... Let me know if I'm on the right track...


    Author Comment

    Figured it out. Thanks for your input - have some points.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: HTML5 for Beginning Beginners

    Are you looking for an efficient introduction to HTML5? Have you tried other HTML5 courses and found them too complex? Do you need just the basics of HTML5 to use with Wordpress or other content management system? If you said yes to any of these questions, this course is for you.

    For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
    Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now