NULL value displayed as 1

[johnc83]

Hi all,

got a niggly little problem with my select statement and would appreciate some help pls..

I have a tableadapter which should return the output of this query..

Select ProductID
From tblProducts
Where ProductName = @Param1

Problem I have is that when the ProductName has a NULL ProductID, it is returning '1' instead of an empty string. This is leading users to think that the ProductID is in fact '1' instead of nothing.

My bit of code is as follows

TblProductsTableAdapter.spGetProductIDFromProdName(MyDataSet.tblProducts, ProductTextBox.Text)

Can anyone advise what I need to do to solve this?

Thanks

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Bluejay07]

To avoid the null reference, would it not be easier to avoid it completely.

Select ProductID
From tblProducts
Where ProductName = @Param1 AND (NOT (ProductID IS NULL))

This will for all product id's where there is not a null value.

 

[johnc83]

Hi BlueJay, thanks for the reply.

I'd be quite happy to do that but now it returns the answer as '0' instead of empty string.

If a little more explanation will help - All I need this for is to find out what the ProductID is for the the ProductName currently entered in my ProductNameTextBox. It will never produce more than one answer because the ProductName is a primary key.

Thanks again, John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Bluejay07]

Johnc83,

If that's the case, then you could also perform an additional check, such as:

If SQLResult = 0 then ProductID = String.Empty

Or something similar.

 

[jebenson]

You could also handle this in your SQL statement, using IsNull:

Select [red]IsNull([/red]ProductID[red],'')[/red]
From tblProducts
Where ProductName = @Param1

This will return an empty string if the ProductID is null.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[johnc83]

Hi again, thanks for the replies,

Bluejay - thanks for that but I'm trying to keep the code to a minimum so would like to avoid more checks,if possible. Appreciate the reply very much though.

jebenson - i tried your IsNull method but it was still bringing in an integer. It looks like it makes perfect sense but doesn't seem to want to play. My Stored Procedure in full is..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spGetIDfromProdName]
(
	@Param1 varchar(100)
)
AS
	SET NOCOUNT ON;
SELECT     ISNULL(ProductID, '') AS ProductID
FROM         tblProducts
WHERE     (ProductName = @Param1)set ANSI_NULLS ON

any other ideas - is it something with my tableadapter?

thanks again

John



.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Craig0201]

I suggest it's nothing to do with your SQL and everything to do with what you are doing with the results.

Try stepping through your code.

C