We help IT Professionals succeed at work.

Help with seperating multiple selction as individual records.

jriver12
jriver12 asked
on
Medium Priority
234 Views
Last Modified: 2013-12-24
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.?
Comment
Watch Question

Commented:
you can dynamically create your query.

<CFIF IsDefined("FORM.selectFieldName")>
     <CFQUERY NAME="dynamicQuery" DATASOURCE="yourDB">
     SELECT *
     FROM myTable WHERE     selectFieldEquavalentColumnName
          <CFIF ListLen(FORM.selectFieldName) GT 1>
               IN (#ListQualify(FORM.selectFieldName, """, "," "ALL")#)
          <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

Commented:
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.

Commented:
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

Commented:
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.

Commented:
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

Author

Commented:
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.

Author

Commented:
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. ?

Commented:
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>

Commented:
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.productCodes")>
<!--- 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#" INDEX="productCode">
          <CFQUERY NAME="Table2InsertQuery" DATASOURCE="yourDB">
         Insert into Table2 (recordnum, productcode)
          values (#VARIABLES.recordNum#, '#productcode#')
         </CFQUERY>
     </CFLOOP>
</CFTRANSACTION>
</CFIF>

HTH,
CJ

Author

Commented:
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_address")>
                <cfinsert datasource="exhibition" tablename="EXH_CONTACT_INFO" formfields="exhibitor_code,ct_Lname, CT_FName, ExpoYear, comp_TO, receive_mail, total_years, last_exh, Co_name, contact, title, dear, C_Address1, C_Address2, C_City, C_State_Province, C_postal_Zip, C_Country, E_Mail, C_Phone, C_Fax, M_Lname, M_Fname, M_Address1,M_Address2, M_City, M_State_Province, M_postal_Zip, M_Country, comments, competitors">

                  <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>'#getmax.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#' AND Co_Name = '#co_Name#'
                        </CFQUERY>
                           <cfoutput query="get_mailing" group="Co_Name">
                                 <form name="form1" method="post" action="<cfoutput>#CGI.Script_Name#</cfoutput>">
                                 <input type="text" name="ExhibitorNumber" value="#getmax.ExhibitorNumber#">
                                <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">&nbsp;</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">&nbsp;</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#', M_address2 = '#M_address2#', M_City = '#M_city#', M_state_province = '#M_state_province#', M_postal_Zip = '#M_postal_zip#', M_Lname = '#M_Lname#', M_FName = '#M_Fname#'
                  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.Script_Name#</cfoutput>">
                <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="<cfoutput>#CGI.Script_Name#</cfoutput>">

  <table width="100%">
    <tr>
      <input type="hidden" name="exhibitornumber">
      <input type="hidden" name="updated" value="<cfoutput>#mid(NOW(),6,11)#</cfoutput>">
      <!--- 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</option>
            <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_FName.value=this.value;">
          </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#">#description#
          </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_lName.value=this.value;">
        </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>
      <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></td>
      <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_address1.value=this.value;">
        <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>
      <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_address2.value=this.value;">
          <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.value=this.value;">
        <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_state_province.value=this.value;">
          <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_postal_zip.value=this.value;">
        <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></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_Country" onchange="this.form.M_country.value=this.value;">
          <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?&nbsp;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>
      <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>
      <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>

Commented:
I don't even see a select box in the posted code??

What errors were you getting from my previous comment?

CJ

Author

Commented:
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#">#description#
         </cfoutput>
       </select>
       </font></td>

Author

Commented:
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).

Commented:
Which line does it happen on.. which cf code line?

CJ

Author

Commented:
<cfif IsDefined("form.Productcodes")>
<CFQUERY NAME="getproducts" DATASOURCE="exhibition">
SELECT      Description, Prodno
FROM         dbo.products
where Description = #productcodes#
<cfif ListLen(form.productcodes)GT 1>
IN (#ListQuality(form.productcodes,"", "," "All")#)
<cfelse>
="#form.productcodes#"</cfif>
</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
Commented:
No, this is the code you should be testing:
<!--- I assume the check box field is called productCodes --->
<CFIF IsDefined("FORM.productCodes")>
<!--- 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#" INDEX="productCode">
         <CFQUERY NAME="Table2InsertQuery" DATASOURCE="yourDB">
        Insert into Table2 (recordnum, productcode)
         values (#VARIABLES.recordNum#, '#productcode#')
        </CFQUERY>
    </CFLOOP>
</CFTRANSACTION>
</CFIF>

Author

Commented:
Duh!,

I KNEW THAT! LOL.

Commented:
Let me know how the testing goes, and if there are bugs we can try to debug it.

CJ

Author

Commented:
appeciate it!

Author

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.