当前位置：首页 → 问答吧 → 这条SQL在MSSQL中有结果，却在ADO.Net里获取不到，为什么

这条SQL在MSSQL中有结果，却在ADO.Net里获取不到，为什么

时间：2011-11-22

来源：互联网

C# code


 string strSql = @"declare @sql varchar(8000)
                                set @sql =
                                 'select ''第''+DATENAME(WEEK,EndDate-1)+''周'' as ''周'',
                                    SUM(case when ActivityType=40 then 1 else 0 end) as ''电询'',
                                    SUM(case when ActivityType=41 then 1 else 0 end) as ''面询'',
                                    SUM(CASE When Sex=1 then 1 else 0 end) as ''男'',
                                    SUM(CASE When Sex=0 then 1 else 0 end) as ''女'',
                                    SUM(CASE When Sex is null then 1 else 0 end) as ''未知'',
                                    SUM(case when EvaluationType=40 then 1 else 0 end) as ''情感家庭'',
                                    SUM(case when EvaluationType is null then 1 else 0 end) as ''其他'',
                                    SUM(CASE When EmployingWay=52 then 1 else 0 end) as ''长期'',
                                    SUM(CASE When EmployingWay=53 then 1 else 0 end) as ''短期'',
                                    SUM(CASE When EmployingWay is null then 1 else 0 end) as ''未登记'''
                                select @sql = @sql+',SUM(CASE When Department='''+Department+''' then 1 else 0 end) as '''+Department+'''' 
                                from (select distinct Department from Crm_CustomerEmployee) as a
                                set @sql = @sql +' from 
                                    (SELECT empid,EndDate,ActivityType,EvaluationType,
                                        (SELECT Sex FROM Crm_CustomerEmployee ce WHERE ce.CustomerEmpID=c.EmpID) as Sex,
                                        (SELECT EmployingWay FROM Crm_CustomerEmployee ce WHERE ce.CustomerEmpID=c.EmpID) as EmployingWay,
                                        (SELECT Department FROM Crm_CustomerEmployee ce WHERE ce.CustomerEmpID=c.EmpID) as Department
                                    from FI_Consult c ) as result
                                where EndDate between ''" + startDate + @"'' and ''" + endDate + @"''
                                group by DATENAME(WEEK,EndDate-1)'
                                exec (@sql)";
//                strSql = @"declare @sql varchar(8000)
//                                set @sql ='SELECT * FROM FI_Consult'
//                            exec (@sql)";
                DataSet ds = new DataSet();
                using (SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=MIS_1119;Integrated Security=True"))
                {
                    try
                    {
                        connection.Open();
                        SqlDataAdapter command = new SqlDataAdapter(strSql, connection);
                        command.Fill(ds, "ds");
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }

                }
                DataTable tb = ds.Tables[0];

上半部分是一条SQL语句，目的是某些字段的汇总情况然后分列显示
但这条语句在MSSQL中执行有结果
可在ASP.NET中获取的时候却什么也得不到，如果把sql语句换成被我注释的那一条的话就没问题有数据。
好奇怪，为什么在MSSQL中有结果，却在ADO.Net里获取不到呢？？？？？高手指点

作者: maxao 发布时间: 2011-11-22

写成存储过程调用。

作者: fredrickhu 发布时间: 2011-11-22

引用 1 楼 fredrickhu 的回复:

写成存储过程调用。

别提了，我用.NET MVC，将语句写在存储过程里，拖拽到dbml文件里，结果系统自动生成的方法居然是void，无返回值，唉！所以只能写在代码里，然后用DataTable解析了！

作者: maxao 发布时间: 2011-11-22

去。net 版问问，你这个是不对的，但是我c#忘完了
呵呵。。。。。

作者: Beirut 发布时间: 2011-11-22

SQL code


select @sql = @sql+',SUM(CASE When Department='''+Department+''' then 1 else 0 end) as '''+Department+'''' 
                                from (select distinct Department from Crm_CustomerEmployee) as a

问题出在这一句SQL上，这个语句的功能是循环Department字段使其成为结果表中的列，去掉这一句ADO.NET就能正常调用了，这是为什么？

作者: maxao 发布时间: 2011-11-22

你拼得很好

作者: ssp2009 发布时间: 2011-11-22

引用 4 楼 maxao 的回复:

SQL code

select @sql = @sql+',SUM(CASE When Department='''+Department+''' then 1 else 0 end) as '''+Department+''''
from (select distinct Department from Crm_Custom……

e 那把strSql 打印出来看下，可能多少引号的问题

作者: Beirut 发布时间: 2011-11-22

引用 6 楼 beirut 的回复:

引用 4 楼 maxao 的回复:

SQL code

select @sql = @sql+',SUM(CASE When Department='''+Department+''' then 1 else 0 end) as '''+Department+''''
from (select distinct Department from Crm_Custom……

e 那……

引号没有问题，这一句我print出来是：
SQL code

SUM(CASE When Department='HR' then 1 else 0 end) as 'HR',SUM(CASE When Department='IT部' then 1 else 0 end) as 'IT部',SUM(CASE When Department='财务部' then 1 else 0 end) as '财务部',SUM(CASE When Department='促进步' then 1 else 0 end) as '促进步',SUM(CASE When Department='培训部' then 1 else 0 end) as '培训部',SUM(CASE When Department='项目部' then 1 else 0 end) as '项目部',SUM(CASE When Department='咨询部' then 1 else 0 end) as '咨询部' from

作者: maxao 发布时间: 2011-11-22

这条SQL在MSSQL中有结果，却在ADO.Net里获取不到，为什么

热门阅读

热门下载