We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Update table Error: SQL Syntax Error converting the varchar value

tags266 asked
Medium Priority
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
Watch Question

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#' )    

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


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.  


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


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#'       
      <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)#      

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


Yep..it's working..but since your first answer pushed me in the right direction you get awarded the points.  Thanks.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.