Quote Reply
Please i need some help.
I am calling a stored procedure from asp.net and there is acursor in the stored procedure that does some processing on servaltables.
if i run the stored procedure on Query Analyzer it works and does whatit is suppose to do but if i run it from my asp.net/module control itgoes. 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 iscalled programmatically from the asp.net/module control page.plus itdoes not throw any errors
This is the code from my control
System.Data.SqlClient.SqlParameter [] param={newSystem.Data.SqlClient.SqlParameter("@.periodStart",Convert.ToDateTime(startDate)),newSystem.Data.SqlClient.SqlParameter("@.periodStart",Convert.ToDateTime(endDate)),newSystem.Data.SqlClient.SqlParameter("@.addedby",UserInfo.FullName+ ""+UserInfo.Username)};
stringstr=System.Configuration.ConfigurationSettings.AppSettings["payrollDS"];
System.Data.SqlClient.SqlConnection cn=newSystem.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. OfDeactivated Pensioners:"+Convert(varchar,@.DeactivatedPensioners)+"<BR>"+"The No. ofEnrolled Pensioners:"+Convert(varchar,@.NoOfEnrolledPensioners)+"<BR>"+"No Of DeadPensioner 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
GOYou may need try to use"ExecuteNonQuery() ", not "ExecuteScalar()".|||I used ExecuteNonQuery and it still gave me the same result.it did nothing.
i really need ur help.thanks|||
CREATE PROCEDURE [dbo].[generatePaylistTuned]
@.periodStart datetime,
@.periodEnd datetime,
@.addedby varchar(40)
AS
BEGIN TRANSACTION
DECLARE @.NoOfLoadedPensioners int,
@.NoOfDeadPensioners int,@.NoOfEnrolledPensioners int,@.DeactivatedPensioners int,
@.reportSummary varchar(500)
UPDATE Pensioner
SET isActive=0
WHERE isAlive=0 AND isActive=1
IF (@.@.ERROR=0)
BEGIN
SET @.DeactivatedPensioners=@.@.ROWCOUNT
SET @.NoOfDeadPensioners=@.@.ROWCOUNT
INSERT INTO pensionpaylist(pensionerId,dateAdded,addedBy,periodStart,periodEnd,amountPaid)
SELECT p.pensionerId,getDate(),@.addedby,@.periodStart,@.periodEnd,py.currentMonthlypension
FROM pensioner p
LEFT JOIN pensionpaypoint py on p.pensionerid=py.pensionerId
WHERE p.isActive = 1
IF (@.@.ERROR=0)
BEGIN
SET @.NoOfEnrolledPensioners=@.@.ROWCOUNT
SET @.NoOfLoadedPensioners=@.NoOfEnrolledPensioners+@.DeactivatedPensioners
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)
END
END
IF (@.@.ERROR<>0)
BEGIN
INSERT INTO reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @.periodStart, @.periodEnd,@.reportSummary,'Pay List Generation')
ROLLBACK TRANSACTION
END
ELSE
BEGIN
INSERT INTO reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @.periodStart,@.periodEnd,@.reportSummary,'Pay List Generation')
COMMIT TRANSACTION
END
GO
Should do the same thing as what you had, although run much, much quicker depending on how many Pensioneers you process (The more you process, the faster this will run in comparision). Although, I question the error handling/checking/reporting that you have. I've also changed that part so that if there is an error, only 1 record is inserted into the reportSummary (Your code would have indicated no errors, then another record indicating there was an error, if that is what you really wanted, you'll have to change it)
In any case, I don't see anything in the stored procedure that would cause it to behave differently depending on which environment you are running it in. You've abstracted the sql guts into you sqlhelper class, so we really can't see what you are calling anyhow. I would recommend that you run a sql trace with the sql profiler and see what sql you are generating. Then copy and paste the code into query analyzer/management studio and see if it acts the same there, then play with it until it works. My guess is the problem lies within the SqlHelper class, and it's not generating sql that you would expect from the code you've shown.
|||well thanks but i have run the stored procedure many times in query analyzer and it works fine does what it suppose to do.but the problem is running it from the application using SqlHelper.executeNonquery that is y i am so confused.
I just don't know what the problem is.
It works in query analyzer but does not do anything when called from the application.
thanks|||I found out the problem.
Iused profiler totrace the events when i call the stored procedure and it gave a sp:cachemiss
so i fixed its working now.
thanks forur help
No comments:
Post a Comment