Update table Error: SQL Syntax Error converting the varchar value

Posted on 2006-04-05
Last Modified: 2013-12-24
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


    <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>
             <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>
       <tr >
         <td colspan="2" class="txt_verdata_11_bold">&nbsp;</td>
        <td colspan="2" class="red_arial_small"><table width="700" border="0" cellspacing="0" cellpadding="0">
            <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>
            <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>
            <td colspan="10" class="border_top">&nbsp;</td>
                    <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#>
            <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">
                      <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>


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

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


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'  
SQLSTATE   22018
Question by:tags266
    LVL 8

    Accepted Solution

    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#',
              WHERE purchase_id IN ('#FORM.purchase_id#')      

    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#',
              WHERE purchase_id IN (#FORM.purchase_id#' )    

    Author Comment

    by:tags266'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.  

    Author Comment

    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!!

    Author Comment

    ALRIGHTY - I think i figured it 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#'       
          <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)#      
    LVL 8

    Expert Comment

    That looks good to me, did you check to make sure it is updating correctly?

    Author Comment

    by:tags266's working..but since your first answer pushed me in the right direction you get awarded the points.  Thanks.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
    Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now