Check value if it exist? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Check value if it exist?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-21 : 10:34:15

I want to do a search where the user can enter a primary key and do a search of my sql database to see if that certain value is available or if not.

I'm using sqldatareader to go and check for that value, but I'm getting an error saying that " Invalid attempt to read when no data is present".

What's the syntax to check if a value is Null or not from my asp.net page?

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-03-21 : 10:43:17

You should do an ExecuteNonQuery for queries like that.
Your stored proc should have an output parameter that will tell you if the PK exists.

If you need more details, let me know.

If your datareader, check the HasRows() property to see if your datareader returned anything. I don't suggest using the reader in this case, but you could.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-21 : 11:25:06

Thanks Michael. I created this stored procedure but not sure if it will work.

CREATE PROCEDURE SP_QUOTESEARCH
@QUOTESEARCHID VARCHAR(10) = '',
@QUOTESEARCH int OUTPUT
AS
BEGIN
SET @QUOTESEARCH = ISNULL((select id from quote where id = @QUOTESEARCHID),0)
RETURN
END

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-03-21 : 11:31:38

That might work, but I think you need to change that SET to a SELECT.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-21 : 12:27:53

Michael. Some how it's not outputing the output parameter. Here's what I have for my stored procedure. I'm not sure if it's right though.

CREATE PROCEDURE CW_QUOTESEARCH
@QUOTESEARCHID VARCHAR(10) = '',
@QUOTESEARCH int OUTPUT
AS
BEGIN

SELECT @QUOTESEARCH = IsNull((select id from quote where id = @QUOTESEARCHID), 0)
END

Can you provide an example that might work?

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-21 : 12:38:41

I'm kind of new to SQL Query Analyzer. Is there a way that I can run my sp in there and test it out. Kind of like a debug so I can see what's going on.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-21 : 12:43:22

I found the debug now. BUt somehow my select @QuoteSearch is not right or something cuz it's returning blank even though I do have that value in database.

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-03-21 : 13:24:42

Here's an example to run on the Northwind database


DECLARE @OrderID INT
DECLARE @Exists BIT --Make this an output param in your stored proc, and you may want to default it to 0
			

--Exists
--select @OrderID = 10248

--Doesn't exist
select @OrderID = 666 

SELECT @Exists = (SELECT 1 FROM Orders WHERE OrderID = @OrderID)

select @Exists  -- Don't do this in the stored proc, but this value should be the value of the output param

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-21 : 14:29:28

Thanks Michael for that example. I got it to work now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources