Help Stored Procedure Working But Not Doing Anything

Help Stored procedure working but not doing anything New Post

Quote    Reply

Please i need some help.



I am calling a stored procedure from  asp.net and there is a
cursor in the stored procedure that does some processing on serval
tables.



if i run the stored procedure on Query Analyzer it works and does what
it is suppose to do but if i run it from my asp.net/module control it
goes. acts likes it worked but it does not do what is suppose to do.

i believe the cursor in the stroed procedure does not run where is
called programmatically from the asp.net/module control page.plus it
does not throw any errors





This is the code from my control

System.Data.SqlClient.SqlParameter [] param={new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(startDate)),new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(endDate)),new
System.Data.SqlClient.SqlParameter("@addedby",UserInfo.FullName+ "
"+UserInfo.Username)};

               
string
str=System.Configuration.ConfigurationSettings.AppSettings["payrollDS"];

               
System.Data.SqlClient.SqlConnection cn=new
System.Data.SqlClient.SqlConnection(str);

               
cn.Open();              


               
//System.Data.SqlClient.SqlTransaction trans=cn.BeginTransaction();



               
SqlHelper.ExecuteScalar(cn,System.Data.CommandType.StoredProcedure,"generatePaylistTuned",param);

      





------------------------THis is the code for my storedprocedure-------------



CREATE PROCEDURE [dbo].[generatePaylistTuned]

@periodStart datetime,

@periodEnd datetime,

@addedby varchar(40)



AS



begin transaction generatePayList



DECLARE @pensioner_id int, @dateadded datetime,

 @amountpaid float,

@currentMonthlypension float,@actionType varchar(50),

@isAlive bit,@isActive bit,@message varchar(80),@NoOfLoadedPensioners int,

@NoOfDeadPensioners int,@NoOfEnrolledPensioners int,@DeactivatedPensioners int,

@reportSummary varchar(500)



set @NoOfLoadedPensioners =0



set @NoOfDeadPensioners=0

set @NoOfEnrolledPensioners=0

set @DeactivatedPensioners=0

set @actionType ="PayList Generation"



DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR



select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension

from pensioner p left outer join pensionpaypoint py on  p.pensionerid=py.pensionerId



where p.isActive = 1





OPEN paylist_cursor



FETCH NEXT FROM paylist_cursor

INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension



WHILE @@FETCH_STATUS = 0

BEGIN



set @NoOfLoadedPensioners=@NoOfLoadedPensioners+1

if(@isAlive=0)

begin

update Pensioner

set isActive=0

where pensionerid=@pensioner_id

set @DeactivatedPensioners =@@ROWCOUNT+@DeactivatedPensioners

set @NoOfDeadPensioners =@@ROWCOUNT+@NoOfDeadPensioners

end

else

begin

insert into pensionpaylist(pensionerId,dateAdded,addedBy,

periodStart,periodEnd,amountPaid)

values(@pensioner_id,getDate(),@addedby, @periodStart, @periodEnd,@currentMonthlypension)

set @NoOfEnrolledPensioners =@@ROWCOUNT+ @NoOfEnrolledPensioners

end



   -- Get the next author.

   FETCH NEXT FROM paylist_cursor

   INTO  @pensioner_id,@isAlive,@isActive,@currentMonthlypension

END



CLOSE paylist_cursor

DEALLOCATE paylist_cursor



set @reportSummary ="The No. of Pensioners Loaded:
"+Convert(varchar,@NoOfLoadedPensioners)+"<BR>"+"The No. Of
Deactivated Pensioners:
"+Convert(varchar,@DeactivatedPensioners)+"<BR>"+"The No. of
Enrolled Pensioners:
"+Convert(varchar,@NoOfEnrolledPensioners)+"<BR>"+"No Of Dead
Pensioner from Pensioners Loaded: "+Convert(varchar,@NoOfDeadPensioners)

insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)

values(getDate(),0, @periodStart, @periodEnd,@reportSummary,'Pay List Generation')



 if (@@ERROR <> 0)               

          BEGIN



insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)

values(getDate(),1, @periodStart,@periodEnd,@reportSummary,'Pay List Generation')



ROLLBACK TRANSACTION  generatePayList



          END



commit Transaction generatePayList

GO