cstr within sql query...

[A1Pat]

Hi all,

I'm trying to get data using such query:

sql = "SELECT a.ShortName, a.ShortDesc, a.ImageFileName, a.ProductID " _
& "FROM tblProducts a, tblProductType b " _
& " WHERE b.ProdTypeID = '43' " _
& " AND a.Hot = 1" _
& " AND CSTR(a.ProdTypeID) = b.ProdTypeID "


The problem is CSTR which is not usable in SQL, does anyone know how to fix that? Thanks!!!

 

[travisbrown]

I'm assuming SQL SERVER?

You want to use CAST or CONVERT

CONVERT(CHAR,a.ProdTypeID)
where n is the length. Optionally, use varchar.

But why do you have to convert it to a string; aren't both b.ProdTypeID and a.ProdTypeID integers?

sql = "SELECT a.ShortName, a.ShortDesc, a.ImageFileName, a.ProductID " _
& "FROM tblProducts a, tblProductType b " _
& " WHERE b.ProdTypeID = 43 " _
& " AND a.Hot = 1" _
& " AND a.ProdTypeID = b.ProdTypeID

 

[snowboardr]

ql = "SELECT a.ShortName, a.ShortDesc, a.ImageFileName, a.ProductID " _
                    & "FROM tblProducts as a, tblProductType as b " _
                    & "        WHERE b.ProdTypeID = '43' " _
                    & "        AND a.Hot = 1" _
                    & "        AND a.ProdTypeID = b.ProdTypeID "

try that.

www.sitesd.com
ASP WEB DEVELOPMENT

 

[Genimuse]

Just removing the CSTR won't help if the problem is that tblProductType.ProdTypeID is a string instead of an integer. As Travis suggested, you do indeed need CAST or CONVERT if that's your problem (though you may be better off changing the second one to an integer instead of the the first one into a string, since integer comparisons are much faster for the database than string comparisons).