Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

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
0
Lorna70
Asked:
Lorna70
2 Solutions
 
esolveCommented:
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 ZCommented:
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
 
esolveCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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
 
esolveCommented:
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now