Conversion failed when converting....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conversion failed when converting....

Author

Topic

dst101
Starting Member

14 Posts

Posted - 2010-05-19 : 09:46:24

Hi,

I've inherited a database which I would like to do a join with a couple of tables unfortunately my query is returning the error:

Conversion failed when converting the nvarchar value '|10|,' to data type int.

I've a table 'ImageCat' with 'Image.CatID' as an integer.
I also have table 'ImageCategory' with 'ImageCategory.CatID' as a nvarchar, this I thought initially would be the key for the inner join.

Does anyone know how I can make this work?

Here is my sql code:
SELECT * FROM
ImageCat INNER JOIN ImageCategory ON ImageCat.CatID = ImageCategory.CatID
WHERE (ImageCatgeory.CatID = ‘10’)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-19 : 09:48:47

Remove single quotes around 10

Madhivanan

Failing to plan is Planning to fail

dst101
Starting Member

14 Posts

Posted - 2010-05-19 : 10:15:06

Thanks Madhivanan, but I still get the same error.

The ImageCat.CatID has values such as '|10|','|8|' in it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-19 : 10:17:23

quote:
Originally posted by dst101

Thanks Madhivanan, but I still get the same error.

The ImageCat.CatID has values such as '|10|','|8|' in it.

You need to use

SELECT * FROM
ImageCat INNER JOIN ImageCategory ON ImageCat.CatID = ImageCategory.CatID
WHERE (ImageCatgeory.CatID like '%|10|%')

Madhivanan

Failing to plan is Planning to fail

dst101
Starting Member

14 Posts

Posted - 2010-05-19 : 10:52:13

Thanks Madhivanan, I managed to pull some of the information needed, however I've been advised that CatID in the ImageCat table is a string - numbers separated by '|' and ',' characters. Each number corresponds to an image category defined by the user and held in the ImageCategory table (CatID in this table is a number). My SQL statement can't join the two tables as it's trying to compare a string with a number.

Thanks for the help!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-19 : 11:48:09

Please provide some sample data from your tables and the expected output.

dst101
Starting Member

14 Posts

Posted - 2010-05-20 : 04:24:08

Sample Data: ImageCat
ImageID: 32 int
FileName: corner-road.jpg nvarchar(255)
Description: Road on a corner nvarchar(255)
CatID: |10|, nvarchar(255)

Sample Data: ImageCategory
CatID: 10 int
CategoryName: Miscellaneous nvarchar(100)
Description: Miscellaneous nvarchar (100)

Expected output would be pulling the relevant data according to what CatID the user selects.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 10:02:17

Try this

SELECT * FROM 
ImageCategory a INNER JOIN ImageCat b ON b.CatID LIKE '%|' + convert(varchar(10)a.CatID) + '|%'
WHERE a.CatID = @catid

where @catid will be the selected CategoryID in int

dst101
Starting Member

14 Posts

Posted - 2010-05-20 : 10:25:31

Thanks vijayisonly, I tried that but it's pulling an Incorrect syntax near'a' which is this line:
LIKE '%|' + convert(varchar(10)a.CatID) + '|%'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 10:26:41

Oops, missed a comma there.
LIKE '%|' + convert(varchar(10),a.CatID) + '|%'

dst101
Starting Member

14 Posts

Posted - 2010-05-20 : 10:37:58

Big thanks there vijayisonly, that worked a treat!
You've made my headache dissapear!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 11:39:37

np..glad to help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources