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>
LVL 7
sr_millarAsked:
Who is Participating?
 
Dain_AndersonConnect With a Mentor Commented:
Ok, try this (I forgot to add the conditional clause around the first part of the query:

<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>
0
 
Dain_AndersonCommented:
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
0
 
sr_millarAuthor Commented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

-Dain
0
 
sr_millarAuthor Commented:
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
0
 
Dain_AndersonCommented:
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
0
 
sr_millarAuthor Commented:
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>
 

0
 
sr_millarAuthor Commented:
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
0
 
Dain_AndersonCommented:
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 :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.