check if any recordsets are not empty

i have the a stored procedure that has some conditional updates to different tables and after updates has select statements to return the updated data. Not all the selects return results.

However in my asp code i want to display a success message if any of the selects return results.

Here is what i have which doesn't work;

'my connection object and string
...

'my stored procedure
      storedProc = "exec myStoreProc"

'my recordset object and stored procedure
      rs = New ADODB.Recordset
      rs.Open storedProc, dbCon1

      response.write(rs.state)
      If rs.state > 0 Then
              response.write "Success!"
      End If
LVL 30
VirusMinusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark FranzProject ManagerCommented:
What are the values in the "state" field of the dB?  Are they a string or numeric?  Or is it a "true/false" field?

Post your SQL.

What I do if I'm looking for a "null" or "nothing" is something like this;

If IsNull(rs.state) or IsEmpty(rs.state) Or rs.state = "" Then
      Response.Write "Success!"
Else
VirusMinusAuthor Commented:
mgfranz,

state is not a field in the DB, its a property of the recordset object, which returns a value that describes if the Recordset object is open, closed, connecting, executing or retrieving data. (Ref: http://www.w3schools.com/ado/prop_state.asp)

SQL is something like this;

CREATE PROC [dbo].[myStoredProc]
(
     @empId nvarchar(5)
    ,@empFirstName nvarchar(9)
    ,@empLastName nvarchar(9)
)
AS

INSERT INTO empLeave
(
    ,empId
    ,available

)
VALUES
(
 @empId,
 1
)

SELECT empId from empLeave where available = 1;

IF empId LIKE 'E%O'
--do more updates
--select updated data
ELSE
--do other updates
--select updated data
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
kelvinwkwCommented:
CREATE PROC [dbo].[myStoredProc]
(
     @empId nvarchar(5)
    ,@empFirstName nvarchar(9)
    ,@empLastName nvarchar(9)
)
AS
SET NOCOUNT ON
declare @returnData int

.....

IF empId LIKE 'E%O'
--do more updates
--select updated data
-- i placed in test data, please assign the one that u prefer
set @returnData = 0
ELSE
--do other updates
--select updated data
-- i placed in test data, please assign the one that u prefer
set @returnData = 1
END

select @returnData as returnValue
return
GO


in your asp

try this


response.write rs("returnValue")
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Anthony PerkinsCommented:
All you have to do is check the value of the recordset's EOF property and then use the NextRecordset method to retrieve the next recordset.
Anthony PerkinsCommented:
>>Here is what i have which doesn't work;<<
Try it this way:

'my connection object and string
...

'my stored procedure
     storedProc = "exec myStoreProc 'empId',  'empFirstName', 'empLastName'"

'my recordset object and stored procedure
Set rs = dbCon1.Execute(storedProc)

If rs.EOF Then
'    ...

End If

' If there are more recordsets than
Set rs = rs.Recordset

If rs.EOF Then
'    ...

End If

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VirusMinusAuthor Commented:
acperkins,

u mean

' If there are more recordsets than
Set rs = rs.NextRecordset

?
Anthony PerkinsCommented:
Yes.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.