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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.