Nirmal KumarC

Nirmal KumarC

  • 1.3k
  • 327
  • 75.8k

How to store procedure using data Reterived in label ?

Oct 21 2014 4:21 AM
My Source is below code problem

 SqlCommand cmd = new SqlCommand("SP_OverTime_Details_1", new SqlConnection(ConfigurationManager.AppSettings["connectionString"]));
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@In_UserID", gvStaffDetails.SelectedRow.Cells[2].Text.ToString().Trim());
        cmd.Parameters.AddWithValue("@In_Month", Session["paymonth"].ToString().Trim());
        cmd.Parameters.AddWithValue("@In_Year", Session["payyear"].ToString().Trim());
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        dr = cmd.ExecuteReader();
        while (dr.Read())
        {
           lbltotalovertime.Text = dr["@totalovertime"].ToString();
        }
        cmd.Connection.Close(); 



 I am using Above source but not reterived storeprocedure data  this my problem, how to solve this problem 

My Store Procedure:


Craete procedure [dbo].[SP_OverTime_Details_1]
(
@In_UserID int,
@In_Month char(10),
@In_Year int,
@overtime time(7) output
)
AS
begin
DECLARE @total_sec INT 
--DECLARE @totalovertime time(7)
begin tran
DECLARE @getOverTime CURSOR
SET @getOverTime = CURSOR FOR


SELECT total_sec  = SUM(( DATEPART(hh, Overtime_HHMM) * 3600 ) +
                          ( DATEPART(mi, Overtime_HHMM) * 60 ) +
                            DATEPART(ss, Overtime_HHMM))
FROM   Accounts_DailyAttendance 
where UserID=1046 and datename(month,processdate) = 'May' and datepart(yyyy,processdate)= 2014 and Overtime_HHMM<>'00:00:00.0000000'
 OPEN @getOverTime
FETCH NEXT
FROM @getOverTime INTO @total_sec
WHILE @@FETCH_STATUS = 0
BEGIN
--print @total_sec
SELECT @overtime =  CONVERT(TIME, DATEADD(s, @total_sec, 0))
FROM Accounts_DailyAttendance


--print @totalovertime
FETCH NEXT
FROM @getOverTime INTO @total_sec
END
commit tran
CLOSE @getOverTime
DEALLOCATE @getOverTime
END



Thanks




Answers (1)