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=GetFileFromPat h(GetTempl atePath()) >
<cfif IsDefined("FORM.MM_InsertR ecord") 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> </p>
<form name="form1" method="POST" action="<cfoutput>#Current Page#</cfo utput>">
<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?</stro ng></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#">#Pr emier.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#">#Pr emier.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#">#Pr emier.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#">#Pr emier.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> </p>
</div>
</body>
</html>
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=GetFileFromPat
<cfif IsDefined("FORM.MM_InsertR
<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")
'#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> </p>
<form name="form1" method="POST" action="<cfoutput>#Current
<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>
</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><div align="center">
<select name="by1" id="by1">
<cfoutput query="Premier">
<option value="#Premier.Prem#">#Pr
</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><div align="center">
<select name="by2" id="by2">
<cfoutput query="Premier">
<option value="#Premier.Prem#">#Pr
</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><div align="center">
<select name="by3" id="by3">
<cfoutput query="Premier">
<option value="#Premier.Prem#">#Pr
</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><div align="center">
<select name="by4" id="by4">
<cfoutput query="Premier">
<option value="#Premier.Prem#">#Pr
</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> </p>
</div>
</body>
</html>
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
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
ASKER
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=GetFileFromPat h(GetTempl atePath()) >
<cfif IsDefined("FORM.MM_InsertR ecord") 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> </p>
<form name="form1" method="POST" action="<cfoutput>#Current Page#</cfo utput>">
<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?</stro ng></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#">#Pr emier.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> </p>
</div>
</body>
</html>
Many thanks,
Stuart
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=GetFileFromPat
<cfif IsDefined("FORM.MM_InsertR
<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"
<cfset ThisItem = Evaluate("FORM.details#i#"
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.by#i#")>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.when#i#")>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<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> </p>
<form name="form1" method="POST" action="<cfoutput>#Current
<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>
</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><div align="center">
<select name="by#i#" id="by#i#">
<cfloop query="Premier">
<option value="#Premier.Prem#">#Pr
</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> </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
<cfquery datasource="UKContacts">
INSERT INTO FollowUpTable (Item, Details, ByWhom, ByWhen, ActionCompleted) VALUES (
<cfset ThisItem = Evaluate("FORM.Item#i#")>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.details#i#"
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.by#i#")>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.when#i#")>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<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
ASKER
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=GetFileFromPat h(GetTempl atePath()) >
<cfif IsDefined("FORM.MM_InsertR ecord") 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.VisitReport ID#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> </p>
<form name="form1" method="POST" action="<cfoutput>#Current Page#</cfo utput>">
<div align="center">
<table width="60%" border="0">
<tr>
<th width="11%" scope="col">Item</th>
<th width="32%" scope="col"> </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?</stro ng></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#">#Pr emier.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> </p>
</div>
</body>
</html>
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=GetFileFromPat
<cfif IsDefined("FORM.MM_InsertR
<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"
<cfset ThisItem = Evaluate("FORM.details#i#"
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.VisitReport
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.by#i#")>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.when#i#")>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<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> </p>
<form name="form1" method="POST" action="<cfoutput>#Current
<div align="center">
<table width="60%" border="0">
<tr>
<th width="11%" scope="col">Item</th>
<th width="32%" scope="col"> </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>
</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#"
<td><textarea name="details#i#" id="details#i#"></textarea
<td><div align="center">
<select name="by#i#" id="by#i#">
<cfloop query="Premier">
<option value="#Premier.Prem#">#Pr
</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> </p>
</div>
</body>
</html>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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 :-)
<!--- 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"
<cfset ThisItem = Evaluate("FORM.details#i#"
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.by#i#")>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.when#i#")>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<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 :-)
<cfset CurrentPage=GetFileFromPat
<cfif IsDefined("FORM.MM_InsertR
<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"
<cfset ThisItem = Evaluate("FORM.details#i#"
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.by#i#")>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfset ThisItem = Evaluate("FORM.when#i#")>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<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> </p>
<form name="form1" method="POST" action="<cfoutput>#Current
<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>
</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><div align="center">
<select name="by#i#" id="by#i#">
<cfloop query="Premier">
<option value="#Premier.Prem#">#Pr
</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> </p>
</div>
</body>
</html>
HTH,
-Dain