jriver12
asked on
Help with seperating multiple selction as individual records.
I don't know if this is possible, but I know you guys will let me know.
I am trying to develop a way that once multiple selections have been made from a select box that I can seperate those selections as individual records in a table.
Now the select box will be populated through a query so if my user selects
a
b
c
d
from the select box then I would like them to be posted in my table like such:
rcd_Num cor_rec selection
1 1 a
2 1 b
3 1 c
4 1 d
so, what do you guys think? Can it be done.?
I am trying to develop a way that once multiple selections have been made from a select box that I can seperate those selections as individual records in a table.
Now the select box will be populated through a query so if my user selects
a
b
c
d
from the select box then I would like them to be posted in my table like such:
rcd_Num cor_rec selection
1 1 a
2 1 b
3 1 c
4 1 d
so, what do you guys think? Can it be done.?
I think I understand your problem and cheekycj had the right idea... When multiple selections are made from a drop-down, they are passed as a list, delimited by a comma. As long as you can be sure none of the values of the drop-down will contain this delimiter, you can use a cfloop with your insert query inside.
zook9549: you shouldn't post as an answer without being positive that your comment is the final resolution.
I have already provided code for doing what you stated.
CJ
I have already provided code for doing what you stated.
CJ
Just a quick note...
The post you provided is not what the answer that I believe is being looked for... As I stated, you had the correct idea by treating the passed form value as a ColdFusion List, however... The code you provided was for a select statement. I interpreted the above problem to be related to inserting the values into a database. This makes the code you provided invalid, but like I said, the concept of handling it as a list was there.
The post you provided is not what the answer that I believe is being looked for... As I stated, you had the correct idea by treating the passed form value as a ColdFusion List, however... The code you provided was for a select statement. I interpreted the above problem to be related to inserting the values into a database. This makes the code you provided invalid, but like I said, the concept of handling it as a list was there.
the approach is the same though.. change change the query.. You didn't provide a separate approach.
You are new to EE.. it is a common rule btw. participating experts to just post comments. This leaves the question open for maximum participation (a lot of experts do not look at locked questions.. figuring they have been answered) and also does not lock down the question. Plus, there is a button called "Accept Comment As Answer" which allows the question asker to accept any posted comment that he/she feels adequately provided a solution.
CJ
You are new to EE.. it is a common rule btw. participating experts to just post comments. This leaves the question open for maximum participation (a lot of experts do not look at locked questions.. figuring they have been answered) and also does not lock down the question. Plus, there is a button called "Accept Comment As Answer" which allows the question asker to accept any posted comment that he/she feels adequately provided a solution.
CJ
ASKER
I have not nor will I be able to check either code till tomorrow, I do appreciate your input but you should let me make the determination on what the accepted answer would be.
again thanks for the time and I will continue the thread tomorrow(server issues!)
sorry for the delay.
again thanks for the time and I will continue the thread tomorrow(server issues!)
sorry for the delay.
ASKER
cheekycj,
I'm a lil confused on the part of the form that will allow me to insert the selctions in the field into a seperate table.
Let me explain:
My form is going to update lets say table_1 with all my contacts buisiness infomation, now the field that I had mentioned has all the codes in my db(pulled from productcodetable.)now once my user makes their selection(s)in the menu I would like it to onsubmit run a query (i'm thinking it may need to be a transaction that will
1. post all info(-menu field) to table 1
then query the db for the identity field.
then in return insert that record number into the table_2 holding all selections from the menu field, but listing them in the table as individual records.
I see it as such:
(I have all forms setup and functionalwith the exception of this.)
form1:
Recordnum(hidden)
Co name
lname
fname
phone
***product codes**(going to different table)
on submit.
table 1 populates with.
recordnum coname lname fname phone etc
1 myco mine mine 111.111.1111
table 2
recordid recordnum productcode
1 1 baskets
2 1 weavers
3 1 Sham artist
now is this possible thru a transaction or is it something more complex. ?
I'm a lil confused on the part of the form that will allow me to insert the selctions in the field into a seperate table.
Let me explain:
My form is going to update lets say table_1 with all my contacts buisiness infomation, now the field that I had mentioned has all the codes in my db(pulled from productcodetable.)now once my user makes their selection(s)in the menu I would like it to onsubmit run a query (i'm thinking it may need to be a transaction that will
1. post all info(-menu field) to table 1
then query the db for the identity field.
then in return insert that record number into the table_2 holding all selections from the menu field, but listing them in the table as individual records.
I see it as such:
(I have all forms setup and functionalwith the exception of this.)
form1:
Recordnum(hidden)
Co name
lname
fname
phone
***product codes**(going to different table)
on submit.
table 1 populates with.
recordnum coname lname fname phone etc
1 myco mine mine 111.111.1111
table 2
recordid recordnum productcode
1 1 baskets
2 1 weavers
3 1 Sham artist
now is this possible thru a transaction or is it something more complex. ?
I'm not sure what recordnum in second table represents.
I assume the recordid is an autoincrement or you obtain it through a separate query.
However, since a multiple select box passes a comma delimited list, the elegant way to handle it is through an array, like:
<cfset oCode = listToArray(productCodes)>
<cfloop index="x" from="1" to=#ArrayLen(oCode)#>
<cfquery name="addCodes" datasource="mydsn">
insert into table2 (recordid, recordnum, productcode)
values (#recordid#, 1, #oCode[x]#)
</cfquery>
</cfloop>
I assume the recordid is an autoincrement or you obtain it through a separate query.
However, since a multiple select box passes a comma delimited list, the elegant way to handle it is through an array, like:
<cfset oCode = listToArray(productCodes)>
<cfloop index="x" from="1" to=#ArrayLen(oCode)#>
<cfquery name="addCodes" datasource="mydsn">
insert into table2 (recordid, recordnum, productcode)
values (#recordid#, 1, #oCode[x]#)
</cfquery>
</cfloop>
I don't know if running a sql query to do the update is what you want here.. a stored procedure may be better b/c it can return the recordnum for you.
But what you should do is wrap all your sql queries or stored procedure calls in a <CFTRANSACTION> tag so that either they are all inserted or none.
Here is the code w/out stored procedure:
<!--- I assume the check box field is called productCodes --->
<CFIF IsDefined("FORM.productCod
<!--- Transaction tag.. it will only insert the data if ALL queries (updates and inserts) are successful --->
<CFTRANSACTION>
<!--- First query that inserts the form info --->
<CFQUERY NAME="Table1InsertQuery" DATASOURCE="yourDB">
Insert into Table1
(coname, lname, fname, phone)
values ('#FORM.coname#', '#FORM.lname#', '#FORM.fname#', '#FORM.phone#')
</CFQUERY>
<!--- Query to get recordnum that was just inserted.. it has an order by desc to make the last insertion the first record --->
<CFQUERY NAME="getRecordNum" DATASOURCE="yourDB">
SELECT recordnum from Table1
where coname = '#FORM.coname#' AND
lname = '#FORM.lname#' AND
fname = '#FORM.fname#'
AND phone = '#FORM.phone#'
ORDER BY recordnum DESC
</CFQUERY>
<!--- Get the first row (should be the last inserted record even if there are duplicates --->
<CFSET VARIABLES.recordNum = getRecordNum.recordnum>
<!--- looping query that inserts the recordnumber and productcode --->
<CFLOOP LIST="#FORM.productCodes#"
<CFQUERY NAME="Table2InsertQuery" DATASOURCE="yourDB">
Insert into Table2 (recordnum, productcode)
values (#VARIABLES.recordNum#, '#productcode#')
</CFQUERY>
</CFLOOP>
</CFTRANSACTION>
</CFIF>
HTH,
CJ
But what you should do is wrap all your sql queries or stored procedure calls in a <CFTRANSACTION> tag so that either they are all inserted or none.
Here is the code w/out stored procedure:
<!--- I assume the check box field is called productCodes --->
<CFIF IsDefined("FORM.productCod
<!--- Transaction tag.. it will only insert the data if ALL queries (updates and inserts) are successful --->
<CFTRANSACTION>
<!--- First query that inserts the form info --->
<CFQUERY NAME="Table1InsertQuery" DATASOURCE="yourDB">
Insert into Table1
(coname, lname, fname, phone)
values ('#FORM.coname#', '#FORM.lname#', '#FORM.fname#', '#FORM.phone#')
</CFQUERY>
<!--- Query to get recordnum that was just inserted.. it has an order by desc to make the last insertion the first record --->
<CFQUERY NAME="getRecordNum" DATASOURCE="yourDB">
SELECT recordnum from Table1
where coname = '#FORM.coname#' AND
lname = '#FORM.lname#' AND
fname = '#FORM.fname#'
AND phone = '#FORM.phone#'
ORDER BY recordnum DESC
</CFQUERY>
<!--- Get the first row (should be the last inserted record even if there are duplicates --->
<CFSET VARIABLES.recordNum = getRecordNum.recordnum>
<!--- looping query that inserts the recordnumber and productcode --->
<CFLOOP LIST="#FORM.productCodes#"
<CFQUERY NAME="Table2InsertQuery" DATASOURCE="yourDB">
Insert into Table2 (recordnum, productcode)
values (#VARIABLES.recordNum#, '#productcode#')
</CFQUERY>
</CFLOOP>
</CFTRANSACTION>
</CFIF>
HTH,
CJ
ASKER
ok, so I feel clueless I have tried all the suggestions above and have gotten error after error which beleive you me makes me feel like I need to go back to the books and school and start over.
however here are the pages maybe you can make it work( I know you can)
Please keep in mind that I have everything else working.
now what I want to do is have the products code field insert into the prod codes table as a list.
thanks .
first page:
<html>
<CFIF IsDefined("FORM.Submit")>
<!--- First FORM was submitted so processing it.--->
<head>
<title>Edit Mailing Address</title>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<cfif isDefined("FORM.same_addre
<cfinsert datasource="exhibition" tablename="EXH_CONTACT_INF
<cfif FORM.same_address IS "1">
<CFQUERY NAME="getmax" DATASOURCE="exhibition">
SELECT MAX(ExhibitorNumber) AS Exhibitornumber
FROM dbo.EXH_CONTACT_INFO
ORDER BY ExhibitorNumber
</CFQUERY>
Your information has been posted.<BR>
If you would like to enter the booth information on this exhibitor click<a href="B_I/default.cfm? exhibitornumber=<cfoutput>
link text </a>
<cfelseif Same_Address Is "0">
<CFQUERY NAME="getmax" DATASOURCE="exhibition">
SELECT MAX(ExhibitorNumber) AS Exhibitornumber
FROM dbo.EXH_CONTACT_INFO
ORDER BY ExhibitorNumber
</CFQUERY>
<CFQUERY NAME="get_mailing" DATASOURCE="exhibition">
SELECT ExhibitorNumber, Co_Name, M_Lname, M_FName, M_address1, M_Address2, M_City, M_State_Province, M_Postal_Zip, M_Country
FROM EXH_CONTACT_INFO
WHERE exhibitornumber = '#getmax.exhibitornumber#'
</CFQUERY>
<cfoutput query="get_mailing" group="Co_Name">
<form name="form1" method="post" action="<cfoutput>#CGI.Scr
<input type="text" name="ExhibitorNumber" value="#getmax.ExhibitorNu
<input type="Text" name="Co_Name" value = "#get_mailing.Co_Name#">
<table cellspacing="2" cellpadding="2" width="100%" >
<tr>
<td>name</td>
<td>
<input type="text" name="M_Lname" value="#M_Lname#"><br>
<input type="text" name="M_Fname" value="#M_Fname#">
</td>
</TR>
<tr>
<td width="148">Mailing Address 1</td>
<td width="143">
<input type="text" name="M_Address1" value="#M_address1#">
</td>
<td colspan="2"> </td>
</tr>
<tr>
<td width="148">Mailing Address 2</td>
<td width="143">
<input type="text" name="M_Address2" value="#M_address2#">
</td>
<td colspan="2"> </td>
</tr>
<tr>
<td width="148">City, State Zip</td>
<td width="143">
<input type="text" name="M_City" value="#M_city#">,
</td>
<td width="130">
<input type="text" name="M_state_province" value="#M_state_province#"
</td>
<td width="141">
<input type="text" name="M_postal_zip" value="#M_postal_zip#">
</td>
</tr>
<tr>
<td width="148">
<input type="submit" name="Submit2" value="Update Record">
</td>
</tr>
</table>
</form>
</CFOUTPUT>
<cfelse>
ERROR: Invalid Field Data same_address<br>
</cfif>
<cfelse>
ERROR: Missing Essential Form Field same_address<br>
</cfif>
<CFELSEIF IsDefined("FORM.Submit2")>
<!--- Second FORM was submitted so processing it --->
<head><title>Information Updated</title>
<cfquery datasource="exhibition" name="updatemailing">
update EXH_CONTACT_INFO
set M_address1='#M_address1#',
where ExhibitorNumber = '#ExhibitorNumber#' and Co_Name = '#co_name#'
</cfquery>
</head>
Thank you your record has been updated.
<CFELSE>
<!--- First FORM not submitted so displaying form --->
<head><title>Exhibitor Maintenance Form</title></head>
<form name="input" method="post" action="<cfoutput>#CGI.Scr
<cfinclude template="d_insert.cfm">
</form></cfif>
</Body>
</HTML>
Page2 (include template)
<head>
<title>Exhibitor Maintenance Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<CFQUERY NAME="getproducts" DATASOURCE="exhibition">
SELECT Description
FROM dbo.products
</CFQUERY>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<!--- begin exhibitor entry form calls script from form.cfm --->
<form name="input" method="get"action="<cfout
<table width="100%">
<tr>
<input type="hidden" name="exhibitornumber">
<input type="hidden" name="updated" value="<cfoutput>#mid(NOW(
<!--- current show year field --->
<td width="192" height="35" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Current
Show Year</font></td>
<td width="30" height="35" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="ExpoYear" size="8" maxlength="8" value="2002">
</font></td>
<td width="101" height="35" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<!--- <div align="right"><font size="2" face="Geneva, Arial, Helvetica, san-serif">Exhibitor
Number</font></div> --->
</font></td>
<td width="126" height="35" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="hidden" name="ExhibitorNumber" size="4" maxlength="4">
</font></td>
<!--- Exhibitor code field --->
<td width="118" height="35" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Exhibitor
Code</font></td>
<td height="35" colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="Exhibitor_Code" maxlength="1">
<option>Select Code </option>
<option value="1">Exhibitor</optio
<option value="2">Prospect</option
<option value="3">Not Returning</option>
<option value="4">Application Sent</option>
</select>
</font></div>
</td>
</tr>
<tr>
<!--- comp or trade out field. Default value set to NA--->
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Comp
or Trade Out</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="Comp_TO" maxlength="3">
<option value="2" selected>Not Applicable</option>
<option value="0">Comp</option>
<option value="1">Trade Out</option>
</select>
</font></td>
<!--- Receive mail default value set to Yes --->
<td width="118" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Receive
Mail</font></td>
<td colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="Receive_mail" maxlength="1">
<option value="1" selected>Yes</option>
<option value="0">No</option>
</select>
</font></div>
</td>
</tr>
<tr>
<!--- years in expo default set to 2000 --->
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Years
in Exposition</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="Total_Years" size="2" maxlength="2">
</font></td>
<td width="118" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Last
Exhibited</font></td>
<td colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="LastYrExhibited">
<option value="2000" selected>2000</option>
<option value="2001">2001</option>
<option value="2002">2002</option>
<option value="2003">2003</option>
<option value="2004">2004</option>
</select>
</font></div>
</td>
</tr>
<tr>
<!--- Company Name --->
<td width="192" height="35" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Company
Name</font></td>
<td colspan="3" height="35" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="Co_Name" maxlength="50">
</font></td>
<!--- Contact Name --->
<td width="118" height="35" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">First
Name
<input type="hidden" name="M_FName">
</font></td>
<td width="130" height="35" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="ct_FName" onchange="this.form.M_FNam
</font></div>
<td width="87" rowspan="2"><font face="Geneva, Arial, Helvetica, san-serif" size="1">
</font>
<td width="160" rowspan="2"><font face="Geneva, Arial, Helvetica, san-serif" size="1">
</font>
</tr>
<tr>
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Product
Codes</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="productcodes" size="3" multiple>
<cfoutput query="getproducts">
<option value="#description#">#des
</cfoutput>
</select>
</font></td>
<td width="118" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Last
Name
<input type="hidden" name="M_lName">
</font></td>
<td width="130" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="CT_lName" onChange="this.form.M_lNam
</font>
</tr>
<tr>
<!--- Contact title --->
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Title</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="Title" maxlength="50">
</font></td>
<!--- Salutation --->
<td width="118" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Salutation</font>
<td colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="dear">
</font></div>
</td>
</tr>
<tr>
<!--- address line 1 --->
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Address</font></t
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="c_Address1" onchange="this.form.M_addr
<INPUT type="hidden" name="M_address1">
</font></td>
<!--- address line 2 --->
<td width="118" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Address2</font></
<td colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_address2" onchange="this.form.M_addr
<INPUT type="hidden" name="M_address2">
</font></div>
</td>
</tr>
<tr>
<!--- city field --->
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">City</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_CIty" onchange="this.form.M_city
<INPUT type="hidden" name="M_city">
</font></td>
<!--- State or Province field --->
<td valign="top" width="118" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">State
or Province</font></td>
<td valign="top" colspan="3" align="left">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_State_province" onchange="this.form.M_stat
<INPUT type="hidden" name="M_state_province">
</font></div>
</td>
</tr>
<tr>
<!--- Postal zip field --->
<td valign="top" width="192" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Postal
Zip</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_Postal_ZIP" onchange="this.form.M_post
<INPUT type="hidden" name="M_postal_zip">
</font></td>
<!--- country field --->
<td valign="top" width="118" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Country</font></t
<td valign="top" colspan="3" align="left">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_Country" onchange="this.form.M_coun
<INPUT type="hidden" name="M_country">
</font></div>
</td>
</tr>
<tr>
<!--- Email default set to not available--->
<td valign="top" height="24" width="192" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">E-mail</font></td
<td height="24" colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="E_Mail" value="Not Available" maxlength="60">
</font></td>
<!--- phone --->
<td valign="top" height="24" width="118" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Phone</font></td>
<td valign="top" height="24" colspan="3" align="left">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="c_Phone">
</font></div>
</td>
</tr>
<tr>
<!--- Fax --->
<td valign="top" rowspan="2" width="192" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Fax</font></td>
<td valign="top" rowspan="2" colspan="3" align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_Fax">
</font></td>
<td valign="top" colspan="4" height="23" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Is
the current address the same for billing? Yes:
<input type="radio" name="same_address" value="1" checked>
No:
<input type="radio" name="same_address" value="0">
</font></td>
</tr>
<tr>
<!--- Current address check default is set to yes --->
</tr>
<tr>
<!--- competitors --->
<td valign="top" width="192" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Competitors</font
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<textarea name="Competitors" cols="20" rows="5" wrap="virtual">Enter competiors.</textarea>
</font></td>
<!--- Comments --->
<td valign="top" width="118" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Comments</font></
<td colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<textarea name="Comments" cols="20" rows="5" wrap="virtual">Enter any Comments That You May Have.</textarea>
</font></div>
</td>
</tr>
<!--- submit to form.cfm --->
<tr align="center">
<td valign="top" colspan="8" align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="submit" name="Submit" value="Add Record">
</font></td>
</tr>
</table>
</form>
</body>
</html>
however here are the pages maybe you can make it work( I know you can)
Please keep in mind that I have everything else working.
now what I want to do is have the products code field insert into the prod codes table as a list.
thanks .
first page:
<html>
<CFIF IsDefined("FORM.Submit")>
<!--- First FORM was submitted so processing it.--->
<head>
<title>Edit Mailing Address</title>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<cfif isDefined("FORM.same_addre
<cfinsert datasource="exhibition" tablename="EXH_CONTACT_INF
<cfif FORM.same_address IS "1">
<CFQUERY NAME="getmax" DATASOURCE="exhibition">
SELECT MAX(ExhibitorNumber) AS Exhibitornumber
FROM dbo.EXH_CONTACT_INFO
ORDER BY ExhibitorNumber
</CFQUERY>
Your information has been posted.<BR>
If you would like to enter the booth information on this exhibitor click<a href="B_I/default.cfm? exhibitornumber=<cfoutput>
link text </a>
<cfelseif Same_Address Is "0">
<CFQUERY NAME="getmax" DATASOURCE="exhibition">
SELECT MAX(ExhibitorNumber) AS Exhibitornumber
FROM dbo.EXH_CONTACT_INFO
ORDER BY ExhibitorNumber
</CFQUERY>
<CFQUERY NAME="get_mailing" DATASOURCE="exhibition">
SELECT ExhibitorNumber, Co_Name, M_Lname, M_FName, M_address1, M_Address2, M_City, M_State_Province, M_Postal_Zip, M_Country
FROM EXH_CONTACT_INFO
WHERE exhibitornumber = '#getmax.exhibitornumber#'
</CFQUERY>
<cfoutput query="get_mailing" group="Co_Name">
<form name="form1" method="post" action="<cfoutput>#CGI.Scr
<input type="text" name="ExhibitorNumber" value="#getmax.ExhibitorNu
<input type="Text" name="Co_Name" value = "#get_mailing.Co_Name#">
<table cellspacing="2" cellpadding="2" width="100%" >
<tr>
<td>name</td>
<td>
<input type="text" name="M_Lname" value="#M_Lname#"><br>
<input type="text" name="M_Fname" value="#M_Fname#">
</td>
</TR>
<tr>
<td width="148">Mailing Address 1</td>
<td width="143">
<input type="text" name="M_Address1" value="#M_address1#">
</td>
<td colspan="2"> </td>
</tr>
<tr>
<td width="148">Mailing Address 2</td>
<td width="143">
<input type="text" name="M_Address2" value="#M_address2#">
</td>
<td colspan="2"> </td>
</tr>
<tr>
<td width="148">City, State Zip</td>
<td width="143">
<input type="text" name="M_City" value="#M_city#">,
</td>
<td width="130">
<input type="text" name="M_state_province" value="#M_state_province#"
</td>
<td width="141">
<input type="text" name="M_postal_zip" value="#M_postal_zip#">
</td>
</tr>
<tr>
<td width="148">
<input type="submit" name="Submit2" value="Update Record">
</td>
</tr>
</table>
</form>
</CFOUTPUT>
<cfelse>
ERROR: Invalid Field Data same_address<br>
</cfif>
<cfelse>
ERROR: Missing Essential Form Field same_address<br>
</cfif>
<CFELSEIF IsDefined("FORM.Submit2")>
<!--- Second FORM was submitted so processing it --->
<head><title>Information Updated</title>
<cfquery datasource="exhibition" name="updatemailing">
update EXH_CONTACT_INFO
set M_address1='#M_address1#',
where ExhibitorNumber = '#ExhibitorNumber#' and Co_Name = '#co_name#'
</cfquery>
</head>
Thank you your record has been updated.
<CFELSE>
<!--- First FORM not submitted so displaying form --->
<head><title>Exhibitor Maintenance Form</title></head>
<form name="input" method="post" action="<cfoutput>#CGI.Scr
<cfinclude template="d_insert.cfm">
</form></cfif>
</Body>
</HTML>
Page2 (include template)
<head>
<title>Exhibitor Maintenance Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<CFQUERY NAME="getproducts" DATASOURCE="exhibition">
SELECT Description
FROM dbo.products
</CFQUERY>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<!--- begin exhibitor entry form calls script from form.cfm --->
<form name="input" method="get"action="<cfout
<table width="100%">
<tr>
<input type="hidden" name="exhibitornumber">
<input type="hidden" name="updated" value="<cfoutput>#mid(NOW(
<!--- current show year field --->
<td width="192" height="35" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Current
Show Year</font></td>
<td width="30" height="35" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="ExpoYear" size="8" maxlength="8" value="2002">
</font></td>
<td width="101" height="35" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<!--- <div align="right"><font size="2" face="Geneva, Arial, Helvetica, san-serif">Exhibitor
Number</font></div> --->
</font></td>
<td width="126" height="35" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="hidden" name="ExhibitorNumber" size="4" maxlength="4">
</font></td>
<!--- Exhibitor code field --->
<td width="118" height="35" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Exhibitor
Code</font></td>
<td height="35" colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="Exhibitor_Code" maxlength="1">
<option>Select Code </option>
<option value="1">Exhibitor</optio
<option value="2">Prospect</option
<option value="3">Not Returning</option>
<option value="4">Application Sent</option>
</select>
</font></div>
</td>
</tr>
<tr>
<!--- comp or trade out field. Default value set to NA--->
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Comp
or Trade Out</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="Comp_TO" maxlength="3">
<option value="2" selected>Not Applicable</option>
<option value="0">Comp</option>
<option value="1">Trade Out</option>
</select>
</font></td>
<!--- Receive mail default value set to Yes --->
<td width="118" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Receive
Mail</font></td>
<td colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="Receive_mail" maxlength="1">
<option value="1" selected>Yes</option>
<option value="0">No</option>
</select>
</font></div>
</td>
</tr>
<tr>
<!--- years in expo default set to 2000 --->
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Years
in Exposition</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="Total_Years" size="2" maxlength="2">
</font></td>
<td width="118" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Last
Exhibited</font></td>
<td colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="LastYrExhibited">
<option value="2000" selected>2000</option>
<option value="2001">2001</option>
<option value="2002">2002</option>
<option value="2003">2003</option>
<option value="2004">2004</option>
</select>
</font></div>
</td>
</tr>
<tr>
<!--- Company Name --->
<td width="192" height="35" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Company
Name</font></td>
<td colspan="3" height="35" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="Co_Name" maxlength="50">
</font></td>
<!--- Contact Name --->
<td width="118" height="35" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">First
Name
<input type="hidden" name="M_FName">
</font></td>
<td width="130" height="35" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="ct_FName" onchange="this.form.M_FNam
</font></div>
<td width="87" rowspan="2"><font face="Geneva, Arial, Helvetica, san-serif" size="1">
</font>
<td width="160" rowspan="2"><font face="Geneva, Arial, Helvetica, san-serif" size="1">
</font>
</tr>
<tr>
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Product
Codes</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="productcodes" size="3" multiple>
<cfoutput query="getproducts">
<option value="#description#">#des
</cfoutput>
</select>
</font></td>
<td width="118" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Last
Name
<input type="hidden" name="M_lName">
</font></td>
<td width="130" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="CT_lName" onChange="this.form.M_lNam
</font>
</tr>
<tr>
<!--- Contact title --->
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Title</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="Title" maxlength="50">
</font></td>
<!--- Salutation --->
<td width="118" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Salutation</font>
<td colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="dear">
</font></div>
</td>
</tr>
<tr>
<!--- address line 1 --->
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Address</font></t
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="c_Address1" onchange="this.form.M_addr
<INPUT type="hidden" name="M_address1">
</font></td>
<!--- address line 2 --->
<td width="118" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Address2</font></
<td colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_address2" onchange="this.form.M_addr
<INPUT type="hidden" name="M_address2">
</font></div>
</td>
</tr>
<tr>
<!--- city field --->
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">City</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_CIty" onchange="this.form.M_city
<INPUT type="hidden" name="M_city">
</font></td>
<!--- State or Province field --->
<td valign="top" width="118" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">State
or Province</font></td>
<td valign="top" colspan="3" align="left">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_State_province" onchange="this.form.M_stat
<INPUT type="hidden" name="M_state_province">
</font></div>
</td>
</tr>
<tr>
<!--- Postal zip field --->
<td valign="top" width="192" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Postal
Zip</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_Postal_ZIP" onchange="this.form.M_post
<INPUT type="hidden" name="M_postal_zip">
</font></td>
<!--- country field --->
<td valign="top" width="118" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Country</font></t
<td valign="top" colspan="3" align="left">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_Country" onchange="this.form.M_coun
<INPUT type="hidden" name="M_country">
</font></div>
</td>
</tr>
<tr>
<!--- Email default set to not available--->
<td valign="top" height="24" width="192" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">E-mail</font></td
<td height="24" colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="E_Mail" value="Not Available" maxlength="60">
</font></td>
<!--- phone --->
<td valign="top" height="24" width="118" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Phone</font></td>
<td valign="top" height="24" colspan="3" align="left">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="c_Phone">
</font></div>
</td>
</tr>
<tr>
<!--- Fax --->
<td valign="top" rowspan="2" width="192" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Fax</font></td>
<td valign="top" rowspan="2" colspan="3" align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="text" name="C_Fax">
</font></td>
<td valign="top" colspan="4" height="23" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Is
the current address the same for billing? Yes:
<input type="radio" name="same_address" value="1" checked>
No:
<input type="radio" name="same_address" value="0">
</font></td>
</tr>
<tr>
<!--- Current address check default is set to yes --->
</tr>
<tr>
<!--- competitors --->
<td valign="top" width="192" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Competitors</font
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<textarea name="Competitors" cols="20" rows="5" wrap="virtual">Enter competiors.</textarea>
</font></td>
<!--- Comments --->
<td valign="top" width="118" align="left"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Comments</font></
<td colspan="3" align="left" valign="top">
<div align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<textarea name="Comments" cols="20" rows="5" wrap="virtual">Enter any Comments That You May Have.</textarea>
</font></div>
</td>
</tr>
<!--- submit to form.cfm --->
<tr align="center">
<td valign="top" colspan="8" align="left"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<input type="submit" name="Submit" value="Add Record">
</font></td>
</tr>
</table>
</form>
</body>
</html>
I don't even see a select box in the posted code??
What errors were you getting from my previous comment?
CJ
What errors were you getting from my previous comment?
CJ
ASKER
the select box is on the inserted page of the code:
here is the cut from the code above.
as far as the errors that I was getting they were mostly syntax and the type that pop up to remind you that you realy dont know as much as you think(so I am realy considering hitting the books again!)
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Product
Codes</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="productcodes" size="3" multiple>
<cfoutput query="getproducts">
<option value="#description#">#des
</cfoutput>
</select>
</font></td>
here is the cut from the code above.
as far as the errors that I was getting they were mostly syntax and the type that pop up to remind you that you realy dont know as much as you think(so I am realy considering hitting the books again!)
<td width="192" align="left" valign="top"><font face="Geneva, Arial, Helvetica, san-serif" size="1">Product
Codes</font></td>
<td colspan="3" align="left" valign="top"> <font face="Geneva, Arial, Helvetica, san-serif" size="1">
<select name="productcodes" size="3" multiple>
<cfoutput query="getproducts">
<option value="#description#">#des
</cfoutput>
</select>
</font></td>
ASKER
Cheekycj
these were the errors that I received on the first code that you suggested.
Error Diagnostic Information
Just in time compilation error
Invalid parser construct found on line 21 at position 44. ColdFusion was looking at the following text:
"
Invalid expression format. The usual cause is an error in the expression structure.
The last successfully parsed CFML construct was static text occupying document position (20:38) to (21:4).
these were the errors that I received on the first code that you suggested.
Error Diagnostic Information
Just in time compilation error
Invalid parser construct found on line 21 at position 44. ColdFusion was looking at the following text:
"
Invalid expression format. The usual cause is an error in the expression structure.
The last successfully parsed CFML construct was static text occupying document position (20:38) to (21:4).
Which line does it happen on.. which cf code line?
CJ
CJ
ASKER
<cfif IsDefined("form.Productcod
<CFQUERY NAME="getproducts" DATASOURCE="exhibition">
SELECT Description, Prodno
FROM dbo.products
where Description = #productcodes#
<cfif ListLen(form.productcodes)
IN (#ListQuality(form.product
<cfelse>
="#form.productcodes#"</cf
</CFQUERY>
</cfif>
this code isn't going to inser the value of product codes to another table (seperate) however, I think the second code that you have suggested would be more appropriate for what I am trying to accomplish. I am going to retry to debug myself on that one.
I hope that the code listed above is what you were asking me for.
thanks
<CFQUERY NAME="getproducts" DATASOURCE="exhibition">
SELECT Description, Prodno
FROM dbo.products
where Description = #productcodes#
<cfif ListLen(form.productcodes)
IN (#ListQuality(form.product
<cfelse>
="#form.productcodes#"</cf
</CFQUERY>
</cfif>
this code isn't going to inser the value of product codes to another table (seperate) however, I think the second code that you have suggested would be more appropriate for what I am trying to accomplish. I am going to retry to debug myself on that one.
I hope that the code listed above is what you were asking me for.
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Duh!,
I KNEW THAT! LOL.
I KNEW THAT! LOL.
Let me know how the testing goes, and if there are bugs we can try to debug it.
CJ
CJ
ASKER
appeciate it!
ASKER
Sorry it took so long on the acceptance but the sight has been down? or was it. However, I worked thru the errors and as I suspected the problem was at my end.(fat fingers)
Thanks a mil.
Thanks a mil.
<CFIF IsDefined("FORM.selectFiel
<CFQUERY NAME="dynamicQuery" DATASOURCE="yourDB">
SELECT *
FROM myTable WHERE selectFieldEquavalentColum
<CFIF ListLen(FORM.selectFieldNa
IN (#ListQualify(FORM.selectF
<CFELSE>
= "#FORM.selectFieldName#"
</CFIF>
</CFQUERY>
</CFIF>
This assumes the field (in the DB) is a String.. if it isn't a string.. then remove the quotes and the listQualify function.
CJ