如何在存储过程中传递来自下钻的id [英] How to pass the id in stored procedure which comming from drilldown

问题描述

嗨
我正在使用图表并使条形图向下钻取并显示值，我想知道，如何在存储过程中传递来自下钻的ID注意：请查看elseif中的代码

这是我的代码



我的尝试：



我的代码

Hi am using charting and making the bar to drill down and display the values ,I want to know ,How to pass the id in stored procedure which comming from drilldown Note: Please look at the code in "elseif"
Here Is My code

What I have tried:

My Code

SeriesCollection SC = new SeriesCollection();
DataEngine de = new DataEngine();
        


        de.DataFields = "xAxis=Empname,yAxis=TotalLeave,ToolTip=LeaveType";
        Chart.XAxis.Label.Text = "Department/Employees";
        Chart.TempDirectory = "temp";
        Chart.Debug = true;
        Chart.JS.Enabled = true; 
        de.ConnectionString = conString;
        if (Request.QueryString["ddLevel"] == null )
        {
            Chart.Title = "ABSENTEE REPORT";
            txtstart.Visible = true;
            txtend.Visible = true;
            Button3.Visible = true;
            Button2.Visible = true;
            de.StoredProcedure = "AbsenteeReport2";
            de.ConnectionString = conString;
            de.ParameterCollection.Clear();
            de.AddParameter("@Startdate", txtstart.Text, FieldType.Date);
            de.AddParameter("@Enddate", txtend.Text, FieldType.Date);
            de.DataFields = "Xaxis=DeptName,Yaxis=LeaveCount,url=Deptname";
            Chart.Series.ConnectionString = conString;
            Chart.Series.StoredProcedure = "AbsenteeReport2";
            Chart.Series.ParameterCollection.Clear();
            Chart.Series.AddParameter("@Startdate", txtstart.Text, FieldType.Date);
            Chart.Series.AddParameter("@Enddate", txtend.Text, FieldType.Date);
            Chart.DataBind();
            Chart.Visible = true;
            Chart.SeriesCollection.Add(SC);
            Chart.Visible = true;
            SC = de.GetSeries();


            foreach (Series s in SC)
            {
                foreach (Element el in s.Elements)
                {
                    el.URL = "?id=" + el.URL + "&ddLevel=2";
                }
            }
            Chart.SeriesCollection.Add(SC);
}

        else if (Request.QueryString["ddLevel"] == "2")
        {
            Chart.Title = "ABSENTEE REPORT";
           // de.SqlStatement = "Select Empname,TotalLeave,LeaveType From Charting32 Where Deptname = '" + Request.QueryString["id"] + "'";
            de.StoredProcedure = "AbsenteeReport1 Where Deptname = '" + Request.QueryString["id"] + "'";
            de.ConnectionString = conString;
            de.ParameterCollection.Clear();
            de.AddParameter("@Startdate", txtstart.Text, FieldType.Date);
            de.AddParameter("@Enddate", txtend.Text, FieldType.Date);
            de.DataFields = "xAxis=Empname,yAxis=TotalLeave,ToolTip=LeaveType";
            SC = de.GetSeries();
            Chart.DefaultElement.ToolTip = "<block>Type: <block hAlign='left'>%yvalue<row>";
            txtstart.Visible = false;
            txtend.Visible = false;
            Button3.Visible = false;
            Button2.Visible = false;
            Chart.Height = 550;

            foreach (Series s in SC)
            {
                foreach (Element el in s.Elements)
                {
                    el.ToolTip = "<block>LeaveType:<block>".Replace(":", System.Environment.NewLine) + el.ToolTip.Replace(",", System.Environment.NewLine);     
                } 
            }
            Chart.SeriesCollection.Add(SC);
        }

我的商店程序

My Store Procedure

ALTER procedure [dbo].[AbsenteeReport1](@Startdate DateTime,@Enddate Datetime)
as
Begin
With CTE as (
    SELECT   EmployeeDetails.Empname,
             DepartmentDetails.Deptname ,
             LeaveApplication.LeaveType,
             Sum(LeaveApplication.NoOfDays) As TotalLeave 
    FROM DepartmentDetails 
    Inner JOIN EmployeeDetails on EmployeeDetails.DeptID = DepartmentDetails.DeptID
    INNER JOIN LeaveApplication On EmployeeDetails.EmpID = LeaveApplication.EmpID
    WHERE     LeaveApplication.LeaveFromDate >=@Startdate 
          AND LeaveApplication.LeaveFromDate <=@Enddate 
          AND  EmployeeDetails.Status=0 
          AND  LeaveApplication.leavetype not in ('Forgot Access Card','Permission','Work from Home',
                                          'Holiday Allowance/Weekend Allowance','On Duty','Night Shift Allowance') 
          AND LeaveApplication.LeaveStatus<>'Rejected'
    GROUP BY     LeaveApplication.EmpID ,
             DepartmentDetails.Deptname,
             EmployeeDetails.Empname,
             LeaveApplication.LeaveType
         )
select EmpName, Deptname, stuff ((
    select ',' + LeaveType+'-'+ cast(TotalLeave as varchar(5)) from CTE where EmpName = t.EmpName and Deptname = t.Deptname for xml path('')
    ),1,1,'') as LeaveType,
    Sum(TotalLeave) as TotalLeave
from CTE t
group by EmpName, Deptname

End

推荐答案

首先这一位......

Firstly this bit ...

 Chart.Title = "ABSENTEE REPORT";
// de.SqlStatement = "Select Empname,TotalLeave,LeaveType From Charting32 Where Deptname = '" + Request.QueryString["id"] + "'";
 de.StoredProcedure = "AbsenteeReport1 Where Deptname = '" + Request.QueryString["id"] + "'";
 de.ConnectionString = conString;
 de.ParameterCollection.Clear();
 de.AddParameter("@Startdate", txtstart.Text, FieldType.Date);
 de.AddParameter("@Enddate", txtend.Text, FieldType.Date);

这不是你调用存储过程的方式 - 它应该是（未经测试的！）：

That is not how you call a stored procedure - it should be something like (untested!):

Chart.Title = "ABSENTEE REPORT";
de.StoredProcedure = "AbsenteeReport1";
de.ConnectionString = conString;
de.ParameterCollection.Clear();
de.AddParameter("@Id", Request.QueryString["id"], FieldType.Text);
de.AddParameter("@Startdate", txtstart.Text, FieldType.Date);
de.AddParameter("@Enddate", txtend.Text, FieldType.Date);

并且您需要更改存储过程的签名以接收附加参数（或者如果这不是新的存储过程，则根据此接受额外参数编写一个新存储过程）

And you will need to change the signature of your stored procedure to receive the additional parameter (or if this is not a new stored procedure, write a new one based on this that accepts the extra parameter)

ALTER procedure [dbo].[AbsenteeReport1](@id int, @Startdate DateTime,@Enddate Datetime)
as
...

在体内SP你要么必须使用

Within the body of the SP you will either have to use a

WHERE EmployeeDetails.DeptID  = @id

或将过滤器包含在 ON 子句

AND DepartmentDetails.DeptID = @id

这篇关于如何在存储过程中传递来自下钻的id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！