Link to home
Start Free TrialLog in
Avatar of sr_millar
sr_millar

asked on

Multiple Database Entries on one Form

Hi Everyone,

I have what I am sure will be an easy question but is proving very frustrating for me!  I am creating a database so employees can enter details about customer visits.  Each customer visit may generate Follow Up Actions (details, by Whom and a due date), so I have created a form (code below) that will allow users to enter upto 4 FollowUp items.  The problem I am having is it will only allow one record to be added at a time and also errors.

How do I modify my code below to allow the form to submit upto the 4 seperate items, as seperate records in the FollowupTable?  I think I may need to use CFLoop but have never used it before and dont understand the syntax.

This is an Access MDB by the way.

Many thanks.

Stuart


<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
  <cfquery datasource="UKContacts">
  INSERT INTO FollowUpTable (Item, Details, ByWhom, ByWhen, ActionCompleted) VALUES (
  <cfif IsDefined("FORM.item1") AND #FORM.item1# NEQ "">
    #FORM.item1#
      <cfelse>
      NULL
  </cfif>
  ,
  <cfif IsDefined("FORM.details1") AND #FORM.details1# NEQ "">
    '#FORM.details1#'
      <cfelse>
      NULL
  </cfif>
  ,
  <cfif IsDefined("FORM.by1") AND #FORM.by1# NEQ "">
    '#FORM.by1#'
      <cfelse>
      NULL
  </cfif>
  ,
  <cfif IsDefined("FORM.when1") AND #FORM.when1# NEQ "">
    '#FORM.when1#'
      <cfelse>
      NULL
  </cfif>
  ,
  <cfif IsDefined("FORM.checkbox1")>
    1
      <cfelse>
      0
  </cfif>
  )
  </cfquery>
</cfif>
<cfquery name="Premier" datasource="UKContacts">
SELECT "Premier Contact" as Prem
FROM "Premier Contact Table"
ORDER BY "Premier Contact" ASC
</cfquery>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<div align="center">
  <p>&nbsp;  </p>
  <form name="form1" method="POST" action="<cfoutput>#CurrentPage#</cfoutput>">
    <div align="center">
      <table width="60%"  border="0">
        <tr>
          <th width="11%" scope="col">Item</th>
          <th width="32%" scope="col">Details</th>
          <th width="23%" scope="col">By Whom </th>
          <th width="7%" scope="col">By When </th>
          <th width="27%" scope="col">Comple<strong>ted?</strong></th>
        </tr>
        <tr>
          <td><div align="center">
            <input name="item1" type="text" id="item1" value="1" size="4">
          </div></td>
          <td><textarea name="details1" id="details1"></textarea></td>
          <td><div align="center">
            <select name="by1" id="by1">
              <cfoutput query="Premier">
                <option value="#Premier.Prem#">#Premier.Prem#</option>
              </cfoutput>            </select>
          </div></td>
          <td><input name="when1" type="text" id="when1"></td>
          <td><div align="center">
            <input name="checkbox1" type="checkbox" id="checkbox1" value="checkbox">
          </div></td>
        </tr>
        <tr>
          <td><div align="center">
            <input name="item2" type="text" id="item2" value="2" size="4">
          </div></td>
          <td><textarea name="details2" id="details2"></textarea></td>
          <td><div align="center">
            <select name="by2" id="by2">
              <cfoutput query="Premier">
                <option value="#Premier.Prem#">#Premier.Prem#</option>
              </cfoutput>            </select>
          </div></td>
          <td><input name="hen2" type="text" id="hen2"></td>
          <td><div align="center">
            <input name="checkbox2" type="checkbox" id="checkbox2" value="checkbox">
          </div></td>
        </tr>
        <tr>
          <td><div align="center">
            <input name="item3" type="text" id="item3" value="3" size="4">
          </div></td>
          <td><textarea name="details3" id="details3"></textarea></td>
          <td><div align="center">
            <select name="by3" id="by3">
              <cfoutput query="Premier">
                <option value="#Premier.Prem#">#Premier.Prem#</option>
              </cfoutput>            </select>
          </div></td>
          <td><input name="when3" type="text" id="when3"></td>
          <td><div align="center">
            <input name="checkbox3" type="checkbox" id="checkbox3" value="checkbox">
          </div></td>
        </tr>
        <tr>
          <td><div align="center">
            <input name="item4" type="text" id="item4" value="4" size="4">
          </div></td>
          <td><textarea name="details4" id="details4"></textarea></td>
          <td><div align="center">
            <select name="by4" id="by4">
              <cfoutput query="Premier">
                <option value="#Premier.Prem#">#Premier.Prem#</option>
              </cfoutput>            </select>
          </div></td>
          <td><input name="when4" type="text" id="when4"></td>
          <td><div align="center">
            <input name="checkbox4" type="checkbox" id="checkbox4" value="checkbox">
          </div></td>
        </tr>
          </table>  
    </div>
    <p align="center">
      <input type="submit" name="Submit" value="Submit">
    </p>
    <input type="hidden" name="MM_InsertRecord" value="form1">
  </form>
  <p>&nbsp;</p>
</div>
</body>
</html>
Avatar of Dain_Anderson
Dain_Anderson

Give this a shot:

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<CFLOOP FROM="1" TO="4" INDEX="i">
      <cfquery datasource="UKContacts">
            INSERT INTO FollowUpTable (Item, Details, ByWhom, ByWhen, ActionCompleted) VALUES (
            <cfset ThisItem = Evaluate("FORM.Item#i#")>
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,
            
            <cfset ThisItem = Evaluate("FORM.details#i#")>
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,
            
            <cfset ThisItem = Evaluate("FORM.by#i#")>
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,

            <cfset ThisItem = Evaluate("FORM.when#i#")>
            <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,

            <cfset ThisItem = "FORM.checkbox#i#">
            <cfif IsDefined("#ThisItem#")>
                  <cfqueryparam cfsqltype="CF_SQL_CHAR" value="#Evaluate(ThisItem)#">
            </cfif>
            )
      </cfquery>
</CFLOOP>
</cfif>
<cfquery name="Premier" datasource="UKContacts">
      SELECT "Premier Contact" as Prem
      FROM "Premier Contact Table"
      ORDER BY "Premier Contact" ASC
</cfquery>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<div align="center">
  <p>&nbsp;  </p>
  <form name="form1" method="POST" action="<cfoutput>#CurrentPage#</cfoutput>">
    <div align="center">
      <table width="60%"  border="0">
        <tr>
          <th width="11%" scope="col">Item</th>
          <th width="32%" scope="col">Details</th>
          <th width="23%" scope="col">By Whom </th>
          <th width="7%" scope="col">By When </th>
          <th width="27%" scope="col">Comple<strong>ted?</strong></th>
        </tr>
      <CFLOOP FROM="1" TO="4" INDEX="i">
        <tr>
          <td><div align="center">
            <input name="item#i#" type="text" id="item#i#" value="#i#" size="4">
          </div></td>
          <td><textarea name="details#i#" id="details#i#"></textarea></td>
          <td><div align="center">
            <select name="by#i#" id="by#i#">
              <cfloop query="Premier">
                <option value="#Premier.Prem#">#Premier.Prem#</option>
              </cfloop>            
                  </select>
          </div></td>
          <td><input name="when#i#" type="text" id="when#i#"></td>
          <td><div align="center">
            <input name="checkbox#i#" type="checkbox" id="checkbox#i#" value="checkbox">
          </div></td>
        </tr>
      </CFLOOP>
      </table>  
    </div>
    <p align="center">
      <input type="submit" name="Submit" value="Submit">
    </p>
    <input type="hidden" name="MM_InsertRecord" value="form1">
  </form>
  <p>&nbsp;</p>
</div>
</body>
</html>

HTH,

-Dain
Avatar of sr_millar

ASKER

Dain,

Sorry for the delay in getting back to you and thanks for replying.

when I view the page all the text boxes called Item show #i# and Premier Contact shows #premier.prem# in the boxes

do I need to wrap them in<cfoutput> tags?  I will try that with my limited knowledge!

Many thanks again,

Stuart
You can actually wrap your entire HTML block in a single CFOUTPUT block. Sorry, I forgot to notice that! :-)

-Dain
Dain,

Ok, we are almost there.  My new code is posted below, however I am getting an ODBC error when hitting submit:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (4:6) to (4:38).

My new code, including the <cfoutput> wrap is:

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<CFLOOP FROM="1" TO="4" INDEX="i">
     <cfquery datasource="UKContacts">
          INSERT INTO FollowUpTable (Item, Details, ByWhom, ByWhen, ActionCompleted) VALUES (
          <cfset ThisItem = Evaluate("FORM.Item#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,
         
          <cfset ThisItem = Evaluate("FORM.details#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,
         
          <cfset ThisItem = Evaluate("FORM.by#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,

          <cfset ThisItem = Evaluate("FORM.when#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,

          <cfset ThisItem = "FORM.checkbox#i#">
          <cfif IsDefined("#ThisItem#")>
               <cfqueryparam cfsqltype="CF_SQL_CHAR" value="#Evaluate(ThisItem)#">
          </cfif>
          )
     </cfquery>
</CFLOOP>
</cfif>
<cfquery name="Premier" datasource="UKContacts">
     SELECT "Premier Contact" as Prem
     FROM "Premier Contact Table"
     ORDER BY "Premier Contact" ASC
</cfquery>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<div align="center">
  <p>&nbsp;  </p>
  <form name="form1" method="POST" action="<cfoutput>#CurrentPage#</cfoutput>">
    <div align="center">
      <table width="60%"  border="0">
        <tr>
          <th width="11%" scope="col">Item</th>
          <th width="32%" scope="col">Details</th>
          <th width="23%" scope="col">By Whom </th>
          <th width="7%" scope="col">By When </th>
          <th width="27%" scope="col">Comple<strong>ted?</strong></th>
        </tr>
     <CFLOOP FROM="1" TO="4" INDEX="i">
       <cfoutput>
        <tr>
          <td><div align="center">
            <input name="item#i#" type="text" id="item#i#" value="#i#" size="4">
          </div></td>
          <td><textarea name="details#i#" id="details#i#"></textarea></td>
          <td><div align="center">
            <select name="by#i#" id="by#i#">
              <cfloop query="Premier">
                <option value="#Premier.Prem#">#Premier.Prem#</option>
              </cfloop>            
               </select>
          </div></td>
          <td><input name="when#i#" type="text" id="when#i#"></td>
          <td><div align="center">
            <input name="checkbox#i#" type="checkbox" id="checkbox#i#" value="checkbox">
          </div></td>
        </tr>
            </cfoutput>
     </CFLOOP>
     </table>  
    </div>
    <p align="center">
      <input type="submit" name="Submit" value="Submit">
    </p>
    <input type="hidden" name="MM_InsertRecord" value="form1">
  </form>
  <p>&nbsp;</p>
</div>
</body>
</html>


Many thanks,

Stuart
Ok, I think it's because of the comma in the last item in the INSERT statement, which may or may not be defined. I've moved the comma:

     <cfquery datasource="UKContacts">
          INSERT INTO FollowUpTable (Item, Details, ByWhom, ByWhen, ActionCompleted) VALUES (
          <cfset ThisItem = Evaluate("FORM.Item#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,
         
          <cfset ThisItem = Evaluate("FORM.details#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,
         
          <cfset ThisItem = Evaluate("FORM.by#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,

          <cfset ThisItem = Evaluate("FORM.when#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">

          <cfset ThisItem = "FORM.checkbox#i#">
          <cfif IsDefined("#ThisItem#")>
               ,<cfqueryparam cfsqltype="CF_SQL_CHAR" value="#Evaluate(ThisItem)#">
          </cfif>
          )
     </cfquery>

You'll notice the comma is before the last cfqueryparam statement.

Give that a try,

-Dain
Dain,

I added the comma, and am getting a different error now.  It is as follows:

Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 21S01 (Insert value list does not match column list)
[Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (4:1) to (4:33).


I realised I need to add a field called VisitReportID.  This is stamped as a field on each Followup Record to link it back to the original visitreport.  As you will see I get the value from a query.  I have added it to the code as best I can.  I have looked at this for about an hour solid and cant find the problem!  






<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<CFLOOP FROM="1" TO="4" INDEX="i">
<cfquery datasource="UKContacts">
          INSERT INTO FollowUpTable (Item, Details, ByWhom, ByWhen, ActionCompleted, VisitReportID) VALUES (
          <cfset ThisItem = Evaluate("FORM.Item#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,
         
          <cfset ThisItem = Evaluate("FORM.details#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,

      <cfset ThisItem = Evaluate("FORM.VisitReportID#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,
         
          <cfset ThisItem = Evaluate("FORM.by#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,

          <cfset ThisItem = Evaluate("FORM.when#i#")>
          <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">

          <cfset ThisItem = "FORM.checkbox#i#">
          <cfif IsDefined("#ThisItem#")>
               ,<cfqueryparam cfsqltype="CF_SQL_CHAR" value="#Evaluate(ThisItem)#">
          </cfif>
          )
     </cfquery>


</CFLOOP>
</cfif>
<cfquery name="Premier" datasource="UKContacts">
     SELECT "Premier Contact" as Prem
     FROM "PremierContactTable"
</cfquery>
<cfquery name="qQueryName" datasource="UKContacts">
  SELECT Max(VisitReportID) as NewID
  FROM VisitReportTable
</cfquery>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<div align="center">
  <p>&nbsp;  </p>
  <form name="form1" method="POST" action="<cfoutput>#CurrentPage#</cfoutput>">
    <div align="center">
      <table width="60%"  border="0">
        <tr>
          <th width="11%" scope="col">Item</th>
          <th width="32%" scope="col">&nbsp;</th>
          <th width="32%" scope="col">Details</th>
          <th width="23%" scope="col">By Whom </th>
          <th width="7%" scope="col">By When </th>
          <th width="27%" scope="col">Comple<strong>ted?</strong></th>
        </tr>
     <CFLOOP FROM="1" TO="4" INDEX="i">
       <cfoutput>
        <tr>
          <td><div align="center">
            <input name="item#i#" type="text" id="item#i#" value="#i#" size="4">
          </div></td>
          <td><input type="text" name="VisitReportID#i#" value="#qQueryName.NewID#" size="10"></td>
          <td><textarea name="details#i#" id="details#i#"></textarea></td>
          <td><div align="center">
            <select name="by#i#" id="by#i#">
              <cfloop query="Premier">
                <option value="#Premier.Prem#">#Premier.Prem#</option>
              </cfloop>            
               </select>
          </div></td>
          <td><input name="when#i#" type="text" id="when#i#"></td>
          <td><div align="center">
            <input name="checkbox#i#" type="checkbox" id="checkbox#i#" value="checkbox">
          </div></td>
        </tr>
            </cfoutput>
     </CFLOOP>
     </table>  
    </div>
    <p align="center">
      <input type="submit" name="Submit" value="Submit">
    </p>
    <input type="hidden" name="MM_InsertRecord" value="form1">
  <p><cfoutput>
  </cfoutput>    </p>

  </form>
  <p>&nbsp;</p>
</div>
</body>
</html>
 

ASKER CERTIFIED SOLUTION
Avatar of Dain_Anderson
Dain_Anderson

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Dain,

It is working!  One last question - if I choose to only add two follow up items it still add 4 records to the DB - the other two are blank.  Is there an easy way for it to add only the records where the details box is filled in?

If not dont worry about it.

Thanks for all the help!

Stuart
Sure thing, try this:

<!--- Only insert if Details is not blank: --->
<cfset ThisItem = Evaluate("FORM.details#i#")>
<cfif Len(Trim(ThisItem))>
      <cfquery datasource="UKContacts">
           INSERT INTO FollowUpTable
           (
                Item,
                Details,
                ByWhom,
                ByWhen
           <cfset ThisItem = "FORM.checkbox#i#">
           <cfif IsDefined("#ThisItem#")>
                ,ActionCompleted
           </cfif>          
           )
           VALUES
           (
                <cfset ThisItem = Evaluate("FORM.Item#i#")>
                <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,
                
                <cfset ThisItem = Evaluate("FORM.details#i#")>
                <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,
                
                <cfset ThisItem = Evaluate("FORM.by#i#")>
                <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">,
                
                <cfset ThisItem = Evaluate("FORM.when#i#")>
                <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ThisItem#" null="#YesNoFormat(NOT Len(ThisItem))#">
                
                <cfset ThisItem = "FORM.checkbox#i#">
                <cfif IsDefined("#ThisItem#")>
                     ,<cfqueryparam cfsqltype="CF_SQL_CHAR" value="#Evaluate(ThisItem)#">
                </cfif>
           )
      </cfquery>
</cfif>

HTH,

-Dain

And thank you for the points :-)