Solved

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

Posted on 2013-01-18
7
289 Views
Last Modified: 2013-01-21
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
Comment
Question by:Lorna70
7 Comments
 
LVL 6

Accepted Solution

by:
esolve earned 180 total points
ID: 38793625
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
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 70 total points
ID: 38796787
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
 
LVL 6

Expert Comment

by:esolve
ID: 38797153
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Expert Comment

by:kottamgari
ID: 38800009
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
 

Author Comment

by:Lorna70
ID: 38800753
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
 
LVL 6

Expert Comment

by:esolve
ID: 38800858
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
 

Author Comment

by:Lorna70
ID: 38802786
Aha - that makes sense now :-) Many thanks, L
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS - Date Report Options 2 28
SSMS Opening Mode 9 18
sql server query 18 36
SQL Group By Question 4 19
I have a large data set and a SSIS package. How can I load this file in multi threading?
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question