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

x

Update table Error: SQL Syntax Error converting the varchar value

tags266
tags266 asked
on
Medium Priority
479 Views
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

</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
 
Comment
Watch Question

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>

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

Ask the Experts

Author

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.  

Author

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

Author

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>

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

Author

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

OR

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.