Yesterday i created a stored procedure with the help of you people in which i use select command to retrieve data from database then perform calculations ,this procedure works fine when it run in SQL Management Studio and display record as i want.
My Stored procedure is:
ALTER PROCEDURE [dbo].[sp_Rpt_Emps_Attnd] @strt date, @end date AS begin with cte as ( select UserID, WorkHrs, case when left(ExtraHrs,1)='-' then -1 else 1 end as multiply, right(ExtraHrs,8) as timestring, --get hours in seconds: DATEPART(HOUR,right(ExtraHrs,8)) * 3600 AS h_in_s, --get minutes in seconds: DATEPART(MINUTE,right(ExtraHrs,8)) * 60 AS m_in_s, --get seconds: DATEPART(SECOND,right(ExtraHrs,8)) AS s,AtnDate,EmpName,EmpType from vw_Rept_Attend ) select UserID,dbo.udfTimeSpanFromSeconds(Sum(Left(workhrs,2) * 3600 + substring(Convert(varchar(8),workhrs), 4,2) * 60 + substring(Convert(varchar(8),workhrs), 7,2))) , case when sum((c.h_in_s + c.m_in_s + c.s) * multiply) < 0 then '-' + CONVERT(varchar,DATEADD(s,ABS(sum((c.h_in_s + c.m_in_s + c.s) * multiply)),0),114) else CONVERT(varchar,DATEADD(s,sum((c.h_in_s + c.m_in_s + c.s) * multiply),0),114) end ,EmpName,EmpType from cte c where convert(date,AtnDate) between @strt and @end group by UserID,UserID,EmpName,EmpType Order by UserID end
This part of stored procedure is user define function which i get from How to format time from dd:hh:mm:ss to only hh:mm:ss in SQL server?
dbo.udfTimeSpanFromSeconds(Sum(Left(workhrs,2) * 3600 + substring(Convert(varchar(8),workhrs), 4,2) * 60 + substring(Convert(varchar(8),workhrs), 7,2)))
and record which i retrieved in MS SQL Management studio is looking as:
Now i need to show this records on crystal report and did this as:
cmd = new SqlCommand("sp_Rpt_Emps_Attnd", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@strt", Convert.ToDateTime(dtStart.Text).Date); cmd.Parameters.AddWithValue("@end", Convert.ToDateTime(dtEnd.Text).Date); conn.Open(); int qry = cmd.ExecuteNonQuery(); SqlDataAdapter sdt = new SqlDataAdapter(cmd); DataTable ds = new DataTable(); sdt.Fill(ds); Rpt_Emps_Attend crp = new Rpt_Emps_Attend(); crp.SetDataSource(ds); crp.SetParameterValue("@total_hrs", t_hrs); crystalReportViewer1.ReportSource = crp; crystalReportViewer1.Refresh(); conn.Close();
But in crystal report i am unable to see these record and my report display some record as:
Please, Any body suggest where am i missing something or am i wrong any where?
thanks for your precious time and kind reply.