Solved

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

Posted on 2013-01-18
7
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 43

Assisted Solution

by:Eugene Z
Eugene Z 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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