Update table Error: SQL Syntax Error converting the varchar value

can someone help me with the below code?  Below you'll find the form page, the action page, and the error i'm getting.  I can't seem to update my purchase table.

My Form
--------------------------------------------------
<cfquery name="getorder" datasource="people">
select *
from purchase, customers, units, orders, creditcards
where orders.order_id = #URL.order_id#
and purchase.order_id = orders.order_id
and purchase.unit_id = units.unit_id
and customers.customer_id = orders.customer_id
and orders.order_id = creditcards.order_id

</cfquery>

    <cfset Total = 0>
                    <cfform action="order_details_action.cfm?order_id=#url.order_id#" method="post">
        <cfset gTotal = 0>
        
      
         <td colspan="2"><div align="right"><span class="txt_verdata_11_bold">Order Status:</span>
             <cfoutput>
             
             
             <cfselect name="status" class="txt_verdana_11">
                   <option value="#getOrder.status#">#getOrder.status#</option>
                   <option value="Booked">Booked</option>
                   <option value="Cancelled">Cancelled</option>
                   <option value="Processing">Processing</option>
                   <option value="Void">Void</option>
                </cfselect>
                  </cfoutput>
         </div></td>
       </tr>
       <tr >
         <td colspan="2" class="txt_verdata_11_bold">&nbsp;</td>
       </tr>
       
      <tr>
        <td colspan="2" class="red_arial_small"><table width="700" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td width="10%" class="gray_text_header"><div align="left">CHANNEL</div></td>
            <td width="10%" class="gray_text_header"> <div align="left">AREA </div></td>
            <td width="9%" class="gray_text_header">DATE </td>
            <td width="9%" class="gray_text_header"><div align="left">TIME </div></td>
            <td width="14%" class="gray_text_header" ><div align="left" class="gray_text_header">
                <div align="left" class="txt_verdata_11_bold">LENGTH</div>
            </div></td>
            <td width="14%" class="gray_text_header" >CONFIRMED DATE </td>
            <td width="14%" class="gray_text_header" >CONFIRMED TIME </td>
            <td width="8%" class="gray_text_header">COST </td>
            <td width="8%" class="gray_text_header"><div align="left">QTY</div></td>
            <td width="12%" class="gray_text_header">SUB TOTAL </td>
          </tr>
          <tr>
            <td colspan="10" class="border_top">&nbsp;</td>
            </tr>
                    <cfoutput query="getOrder">
        <input type="hidden" name="purchase_id" value="#getOrder.purchase_id#" />
        <cfset sTotal = #getorder.quantity# * #getorder.price#>
                  
                  <cfset gTotal = sTotal + gTotal>
                  <cfset fTotal = gTotal + #getorder.editing# + #getorder.production#>
          <tr>
            <td class="txt_verdana_11">#network#</td>
            <td class="txt_verdana_11">#subregion_name#</td>
            <td class="txt_verdana_11">#airdate#</td>
            <td class="txt_verdana_11">#timeslot#</td>
            <td class="txt_verdana_11">#length# Sec.</td>
            <td class="txt_verdana_11">
                    <InterAKT:Widget>
                      <input name="confirmed_airdate" size="10" class="txt_verdana_11" value="#confirmed_airdate#"  readonly="true" format="%m/%d/%Y" skin="blue" language="en" label=".." mondayfirst="false" singleclick="true" subtype="wcalendar" />
                    </InterAKT:Widget>                  </td>
            <td class="txt_verdana_11">
                  <cfselect name="confirmed_time" class="txt_verdana_11">
                  <option value="#confirmed_time#">#confirmed_time#</option>
                  <option value="Pending">Pending</option>
                  <option value="6:00 AM">6:00 AM</option>
                  <option value="7:00 AM">7:00 AM</option>
                  <option value="8:00 AM">8:00 AM</option>
                  <option value="9:00 AM">9:00 AM</option>
                  <option value="10:00 AM">10:00 AM</option>
                  <option value="11:00 AM">11:00 AM</option>
                  <option value="12:00 PM">12:00 PM</option>
                  <option value="1:00 PM">1:00 PM</option>
                  <option value="2:00 PM">2:00 PM</option>
                  <option value="3:00 PM">3:00 PM</option>
                  <option value="4:00 PM">4:00 PM</option>
                  <option value="5:00 PM">5:00 PM</option>
                  <option value="6:00 PM">6:00 PM</option>
                  <option value="7:00 PM">7:00 PM</option>
                  <option value="8:00 PM">8:00 PM</option>
                  <option value="9:00 PM">9:00 PM</option>
                  <option value="10:00 PM">10:00 PM</option>
                  <option value="11:00 PM">11:00 PM</option>
                  <option value="12:00 AM">12:00 AM</option>
                  <option value="1:00 AM">1:00 AM</option>
                  <option value="2:00 AM">2:00 AM</option>
                  <option value="3:00 AM">3:00 AM</option>
                  <option value="4:00 AM">4:00 AM</option>
                  <option value="5:00 AM">5:00 AM</option>
                  </cfselect>                  </td>


MY ACTION PAGE
-----------------------------------------------------------

        <cfquery datasource="people">
         UPDATE dbo.orders
  SET status='#FORM.status#'
      WHERE order_id = '#URL.order_id#'       
        </cfquery>
      
      
      <cfquery  datasource="people">
         UPDATE dbo.purchase
  SET confirmed_airdate='#FORM.confirmed_airdate#',
      confirmed_time='#FORM.confirmed_time#'
            WHERE purchase_id = '#FORM.purchase_id#'       
        </cfquery>
        



<cflocation url="order_details.cfm?order_id=#URL.order_id#">



THE ERROR I'M GETTING
---------------------------------------------

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value '44,45,46' to a column of data type int.  
 
The error occurred in C:\Documents and Settings\Administrator\Desktop\Websites\peoplespots\admin\orders\order_details_action.cfm: line 13
 
11 :   SET confirmed_airdate='#FORM.confirmed_airdate#',
12 :       confirmed_time='#FORM.confirmed_time#'
13 :             WHERE purchase_id = '#FORM.purchase_id#'       
14 :         </cfquery>
15 :         

 

--------------------------------------------------------------------------------
 
SQL    UPDATE dbo.purchase SET confirmed_airdate='04/19/2006,Pending,Pending', confirmed_time='6:00 PM,Pending,Pending' WHERE purchase_id = '44,45,46'  
DATASOURCE   people
VENDORERRORCODE   245
SQLSTATE   22018
 
tags266Asked:
Who is Participating?
 
JRockFLConnect With a Mentor Commented:
Do you want to update all three of those records?  Then use "IN"

<cfquery  datasource="people">
        UPDATE dbo.purchase
  SET confirmed_airdate='#FORM.confirmed_airdate#',
      confirmed_time='#FORM.confirmed_time#'
          WHERE purchase_id IN ('#FORM.purchase_id#')      
       </cfquery>

Also, you do not need the single quotes if purchase_id  is numeric

<cfquery  datasource="people">
        UPDATE dbo.purchase
  SET confirmed_airdate='#FORM.confirmed_airdate#',
      confirmed_time='#FORM.confirmed_time#'
          WHERE purchase_id IN (#FORM.purchase_id#' )    
       </cfquery>
0
 
tags266Author Commented:
Ok...it's not giving me an error now but it's changing all the purchase records to the same variable i set on the first one.  For example, I have an ORDER table.  i also have a purchase table which contains multiple items for the same order linked by a foreign key to the order table.  when i change one of the variables on one of the purchase records on that order it is changing all purchase records to be the same.  I'm missing something here.  
0
 
tags266Author Commented:
here's what's happening...

let's say i am updated 3 records in my purchase table.  What the code is doing is adding those three variables into each of the three records.  It's not parsing one by one.  Using the CFDUMP tag i'm getting "04/13/2006,04/28/2006,04/22/2006 "  those values are going in to each of my three records instead of 04/13/2006 going into purchaes_id 44, 04/28/2006 into purchase_id 45, and 04/22/2006 going into purchase_id 45 etc... Help!!
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tags266Author Commented:
ALRIGHTY - I think i figured it out....here it is for any others who are following up on this.  You experts can take a look and let me know if it looks good.



        <cfquery datasource="people">
         UPDATE dbo.orders
  SET status='#FORM.status#'
      WHERE order_id = '#URL.order_id#'       
        </cfquery>
      
      <cfloop from="1" to="#listlen(form.confirmed_airdate)#" index="i">
      <cfquery  datasource="people">
        UPDATE dbo.purchase
  SET confirmed_airdate='#ListGetAt(FORM.confirmed_airdate, i)#',
      confirmed_time='#ListGetAt(FORM.confirmed_time, i)#'
          WHERE purchase_id = #ListGetAt(FORM.purchase_id, i)#      
    </cfquery>
</cfloop>
0
 
JRockFLCommented:
That looks good to me, did you check to make sure it is updating correctly?
0
 
tags266Author Commented:
Yep..it's working..but since your first answer pushed me in the right direction you get awarded the points.  Thanks.
0
All Courses

From novice to tech pro — start learning today.