ERROR MESSAGE:
Syntax error converting the varchar value '47,9' to a column of data
type int.
If this is the error that you are getting then you need to remove the single
quotes around your list of values it should say "in (47,9)" not "in
('47,9')" the error is being sent because ID_Product is an integer and it is
trying to compare it to the string '47,9' not the list of numbers 47 and 9.
"in ('47','9') will work as well but conversion will take place which will
impact performance. Hope thats not too confusing.
SQL SYNTAX
This should work in the stored procedure:
SELECT ID_Product
FROM Product,Homepage
WHERE ID_Product IN (replace(homeHotProd,'''',''))
>Is there a way in a stored procedure to break apart a list into it's comma
>sep. elements? ex.('1','2','3')
I usually use a while loop and move through the string to break the list
apart. There is not a "loop list" function that I know of for that.
Feel free to let me know if you need some help with it.
Good luck.
>From: Brian Ferrigno <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: SQL <[EMAIL PROTECTED]>
>Subject: RE: Datatype conversion problem
>Date: Tue, 18 Dec 2001 09:36:43 -0500
>
>John,
>
>I'm doing this as a stored procedure and not from a Cold Fusion script
>(which I might have to change... it looks easier to accomplish in CF)
>
>Is there a way in a stored procedure to break apart a list into it's comma
>sep. elements? ex.('1','2','3')
>
>
>Brian
>
>
>-----Original Message-----
>From: John Wilker [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 17, 2001 8:48 PM
>To: SQL
>Subject: RE: Datatype conversion problem
>
>
>Are there any single quotes? You may want to try ('#homeHotProd#')
>
>Also try making the list put each ID in a single quote ie IN
>('1','2','3') etc.
>
>I know IN likes that a little more.
>
>Hope that is some help.
>
>J.
>
>
>John Wilker
>Web Applications Consultant
>Macromedia Certified ColdFusion Developer
>President/Founder, Inland Empire CFUG.
>
>www.red-omega.com
>
>"With your shield or on it."
>
>Spartan mothers would say this to their sons going to battle. It was
>believed that if you turned to run you'd drop your shield.. If you died
>in combat your comrades would carry your body home on your shield. So
>coming home with it or on it were the only options.
>
>
>-----Original Message-----
>From: Brian Ferrigno [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 17, 2001 1:08 PM
>To: SQL
>Subject: Datatype conversion problem
>
>
>I'm getting an error while running a stored procedure and don't quite
>know the solution to the problem.
>
>What I am trying to do is determine whether an identity column is in a
>varchar list of integer IDs. The code compiles fine in my stored
>procedure but while trying to run it from a CF script I get a syntax
>error (see below). The code works if there is only one element in the
>comma-seperated list but throws the error if there are more than one.
>I've already tried using CONVERT on both ID_Product and homeHotProd in
>the WHERE statement but doing it on ID_Product gives an empty result set
>and homeHotProd returns an error.
>
>
>ERROR MESSAGE:
>Syntax error converting the varchar value '47,9' to a column of data
>type int.
>
>SQL SYNTAX
>SELECT ID_Product
>FROM Product,Homepage
>WHERE ID_Product IN (homeHotProd)
>
>ID_Product is an integer
>homeHotProd is a varchar comma-seperated list of integers
>
>
>Does anyone have the fix to this problem?? Thanks in advance for the
>help.
>
>
>
>
>____________________________________________________________________________
>____
>Structure your ColdFusion code with Fusebox. Get the official book at
>http://www.fusionauthority.com/bkinfo.cfm
>Archives: http://www.mail-archive.com/[email protected]/
>
________________________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
