Solved

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

Posted on 2013-01-18
7
288 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

832 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