DataSet has no rows when proc is returning rows??

[teewebco]

Hello,

Im going insane with this problem. I have a stored proc that returns 2
recordsets to a dataset. In the dataset i reference the recordsets by
their tables like below.

ds.tables[0]
ds.tables[1]

In the first table it says their are 0 rows. The columns get populated
but no rows. I run the proc outside VS and the results are correct. In
the datase i can reference the second table and pull the rows i need.


For what ever reason the first dataset is always empty. I have used
this methods before and works great but for some reason it doesnt like
the first recordset.

In my proc I have 'set nocount on'


code below.


Here is a run down of the proc.....

Query 1:
SELECT a.first_name ,a.last_name, upa.organization_id, b.index_code as
EmployeeID
FROM person_name a, person_indices b, v_user_profile_app upa
WHERE a.person_nbr = b.person_nbr
AND b.index_code = (SELECT c.code_nbr
FROM v_code_entries c
WHERE c.table_code = "INDXTP"
AND c.code = "EMPNUM")



Query 2:
Select RoleName FROM UserRoles WHERE person_nber=@person_nbr

 

[teewebco]

the proc:

( @username VARCHAR(255) )
AS
DECLARE @person_nbr INT

BEGIN
set nocount on

-- retrieve person_nbr
EXEC @person_nbr = get_login_person @username



IF(@person_nbr <> 0)
BEGIN

SELECT a.person_nbr
,b.identifier
,a.first_name
,a.last_name, upa.organization_id
FROM person_name a,
person_indices b,
v_user_profile_app upa
WHERE a.person_nbr = b.person_nbr
AND b.index_code = (SELECT c.code_nbr
FROM v_code_entries c
WHERE c.table_code = "INDXTP"
AND c.code = "EMPNUM")
AND b.dt_entered = (SELECT MAX(pi1.dt_entered)
FROM person_Indices pi1
WHERE pi1.person_nbr = b.person_nbr
AND pi1.index_code = 48
AND pi1.dt_invalid = NULL)
AND a.alias_type = (SELECT d.code_nbr
FROM v_code_entries d
WHERE d.table_code = "AKATP"
AND d.code = "REAL")
AND b.person_nbr = upa.person_nbr
AND a.person_nbr=@person_nbr


END


-- return roles for user

SELECT vce.descrip
FROM user_profile_app upa INNER JOIN v_code_entries vce
ON vce.code_nbr = upa.profile_role
AND upa.person_nbr = @person_nbr


END



Dataset:

DataRequest request = new DataRequest();

request.Command = "UserbyUserName";

request.CommandType = CommandType.StoredProcedure;

request.AddParameter("@username", username);

request.Transactional = false;


SybaseDataHelper _Helper = new SybaseDataHelper();

_Helper.ConnectionString =
ConfigurationManager.ConnectionStrings["Sybase"].ConnectionString;

DataSet result;

result = _Helper.ExecuteDataSet(request);

// return dataset

return result;

 

[teewebco]

changed

AND pi1.dt_invalid = NULL

to


AND pi1.dt_invalid is NULL

 

[kferron]

hey i may be in left field here, but when you run that proc in analyzer
how many resultsets are you returning? you're expecting 2 i see, but
how many do you get for real?