Error when calling an SProc from DataList DataSource (ASP.Net)

I have the following SProc which works fine when executing in SQL:

[spListProductsByCatOrAll]
(
@catId int
)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
    declare @sql varchar(255)
   
    -- Insert statements for procedure here
      set @sql = 'SELECT Product_ID, Description, Cat_ID, Product_Title,Created_By FROM Products'
    If @catId <> 0
         set @sql = @sql + ' WHERE Cat_ID = '+@catId
    set @sql = @sql +' ORDER BY Sequence'
      Exec(@sql)
END

However, when it gets called from the DataSource on my DataList (code attached), it comes up with the following error:

Conversion failed when converting the nvarchar value
'SELECT Product_ID, Description, Cat_ID, Product_Title,Created_By
FROM Products
WHERE Cat_ID = ' to data type int.

I'm not attempting to convert to an Int so why on earth am I getting this?  I can only assume it must be an ASP.Net issue??
DataList-error.txt
Lorna70Asked:
Who is Participating?
 
esolveConnect With a Mentor Software Development ManagerCommented:
Haha no its not .NET. Clean up your proc. I suggest this approach

CREATE PROCEDURE [spListProductsByCatOrAll]
(
      @CatId int = 0
)
AS
BEGIN
SET NOCOUNT ON

IF(@CatId = 0) BEGIN SET @CatId = NULL END

SELECT
      Product_ID
      ,Description
      ,Cat_ID
      ,Product_Title
      ,Created_By
FROM
      Products
      WHERE Cat_ID = ISNULL(@Cat_ID, Cat_ID)
ORDER BY Sequence

END


Just ensure that all your dropdown items has integer "values". An empty string will cause an error but I see your existing dropdown default value is 0. Just check the other being populated!

Nice weekend
0
 
Eugene ZConnect With a Mentor Commented:
try: change +@catid to +CAST(@catId as Varchar(10))

Alter Proc [code][spListProductsByCatOrAll]
(
@catId int
)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
    declare @sql varchar(255)
    
    -- Insert statements for procedure here
      set @sql = 'SELECT Product_ID, Description, Cat_ID, Product_Title,Created_By FROM Products'
    If @catId <> 0
         set @sql = @sql + ' WHERE Cat_ID = '+ CAST(@catId as Varchar(10))
   set @sql = @sql +' ORDER BY Sequence'
      Exec(@sql)
END

Open in new window

0
 
esolveSoftware Development ManagerCommented:
Yeah I was also thinking if the Cat_ID field in the database is null or not of type int (or any other numeric type) you will definitely get this error.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
kottamgariCommented:
Hi,
Generally this error/exception is thrown when trying to serve char data to integer data type. So just check weather you are passing correct data type.
0
 
Lorna70Author Commented:
Thanks Esolve - that worked although I don't actually understand it - especially the line:

WHERE Cat_ID = ISNULL(@Cat_ID, Cat_ID)

Also, there are no NULLs in the Cat_ID field and the Cat_ID field in the DB is of Int datatype.

Thanks also Eugene but not idea how your solution worked as it looks like you're casting to a varchar when Cat_ID is an Int datatype.  It would be really great if you guys could answer some of my questions as I'm trying to learn SQL :-)
0
 
esolveSoftware Development ManagerCommented:
This line

WHERE Cat_ID = ISNULL(@Cat_ID, Cat_ID)

Just means

If the @CatID is null then use the field Cat_ID. So what it is actually doing is it is retrieving everything where Cat_ID = Cat_ID when the @CatID = NULL. At the same time it is also retrieving everything where Cat_ID = @CatID when the parameter is not null.

Just an easier way to optimize your code which will help with the syntax.

If Cat_ID is of type INT an the database and it does not allow for nulls then the query should work.

Regards
0
 
Lorna70Author Commented:
Aha - that makes sense now :-) Many thanks, L
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.