报错信息：
1.
2.
3.
4.
5.
6.
解决方法：
1. Data Layer SQL 语句取数据时，把其列值有为null的字段用0.00替换，（ISNULL的用法）；
2.
1 #region 查询工资信息 2 /// <summary> 3 /// 查询工资信息 4 /// </summary> 5 /// <param name="model"></param> 6 /// <param name="pageIndex"></param> 7 /// <param name="pageCount"></param> 8 /// <param name="ord"></param> 9 /// <param name="TotalCount"></param> 10 /// <returns></returns> 11 public static DataTable GetSalaryInfoByEmployee(SalaryInfoModel model, string ReportType, string DeptID, int pageIndex, int pageCount, string ord, string endmonth, ref int TotalCount) 12 { 13 #region 查询语句 14 StringBuilder searchSql = new StringBuilder(); 15 searchSql.AppendLine(" SELECT "); 16 searchSql.AppendLine(" A.ID,A.ReprotNo,A.CompanyCD,A.DeptName,A.EmployeeID,A.EmployeeName,A.Remarks,"); 17 searchSql.AppendLine("isnull(A.BFGJJ,0.00) BFGJJ,"); 18 searchSql.AppendLine("isnull(A.BFGZ,0.00) BFGZ, "); 19 searchSql.AppendLine("isnull(A.BLGZ,0.00) BLGZ,"); 20 searchSql.AppendLine("isnull(A.CTF,0.00) CTF, "); 21 searchSql.AppendLine("isnull(A.DTF,0.00) DTF,"); 22 searchSql.AppendLine("isnull(A.FTF,0.00) FTF, "); 23 searchSql.AppendLine("isnull(A.GHF,0.00) GHF,"); 24 searchSql.AppendLine("isnull(A.GJJ,0.00) GJJ,"); 25 searchSql.AppendLine("isnull(A.GTS,0.00) GTS,"); 26 searchSql.AppendLine("isnull(A.GWF,0.00) GWF,"); 27 searchSql.AppendLine("isnull(A.JBGZ,0.00) JBGZ,"); 28 searchSql.AppendLine("isnull(A.JiangJ,0.00) JiangJ,"); 29 searchSql.AppendLine("isnull(A.JZZYBF,0.00) JZZYBF,"); 30 searchSql.AppendLine("isnull(A.KCBJ,0.00) KCBJ, "); 31 searchSql.AppendLine("isnull(A.MTF,0.00) MTF, "); 32 searchSql.AppendLine("isnull(A.QT,0.00) QT, "); 33 searchSql.AppendLine("isnull(A.QTE,0.00) QTE, "); 34 searchSql.AppendLine("isnull(A.QTY,0.00) QTY, "); 35 searchSql.AppendLine("isnull(A.SBJ,0.00) SBJ, "); 36 searchSql.AppendLine("isnull(A.Total,0.00) Total, "); 37 searchSql.AppendLine("isnull(A.TotalOne,0.00) TotalOne, "); 38 searchSql.AppendLine("isnull(A.TotalTwo,0.00) TotalTwo, "); 39 searchSql.AppendLine("isnull(A.YBJ,0.00) YBJ, "); 40 searchSql.AppendLine("isnull(A.YLJ,0.00) YLJ "); 41 searchSql.AppendLine(" ,c.DeptName as DeptWprkName "); 42 searchSql.AppendLine(" ,Substring(b.ReportMonth, 1, 4) + ‘年‘ "); 43 searchSql.AppendLine(" + Substring(b.ReportMonth, 5, 2) + ‘月‘ "); 44 searchSql.AppendLine(" AS ReportMonth "); 45 searchSql.AppendLine(" FROM officedba.SalaryInfo a "); 46 searchSql.AppendLine(" left join officedba.SalaryReport b on a.ReprotNo=b.ReprotNo "); 47 searchSql.AppendLine(" left join officedba.DeptInfo c on b.DeptID=c.ID "); 48 searchSql.AppendLine(" left join officedba.EmployeeInfo d on a.employeeID=d.ID "); 49 searchSql.AppendLine(" WHERE "); 50 searchSql.AppendLine(" a.CompanyCD = @CompanyCD "); 51 searchSql.AppendLine(" AND b.ReportType = @ReportType "); 52 53 #endregion 54 55 //定义查询的命令 56 SqlCommand comm = new SqlCommand(); 57 //公司代码 58 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); 59 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReportType", ReportType)); 60 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; 61 if (userInfo.EmployeeID != 10389 && userInfo.EmployeeID != 10403 && userInfo.EmployeeID != 8526 && userInfo.EmployeeID != 1873) 62 { 63 searchSql.AppendLine(" AND b.DeptId in (select emp.DepID from officedba.EmpAndDep emp where emp.EmpID=@EmployeeID) "); 64 comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", userInfo.EmployeeID.ToString())); 65 } 66 67 #region 页面输入条件 68 //员工姓名 69 if (!string.IsNullOrEmpty(model.EmployeeName)) 70 { 71 searchSql.AppendLine(" AND A.EmployeeName LIKE ‘%‘ + @EmployeeName + ‘%‘ "); 72 comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", model.EmployeeName)); 73 } 74 75 if (!string.IsNullOrEmpty(model.DeptName)) 76 { 77 searchSql.AppendLine(" AND B.DeptID in (" + model.DeptName + ") "); 78 //comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptName", model.DeptName)); 79 } 80 //所属月份 81 if (!string.IsNullOrEmpty(model.Month)) 82 { 83 if (endmonth != "") 84 { 85 searchSql.AppendLine(" AND convert(int,b.ReportMonth) between @ReportMonth and @endReportMonth "); 86 comm.Parameters.Add(SqlHelper.GetParameterFromString("@endReportMonth", endmonth)); 87 } 88 else 89 { 90 searchSql.AppendLine(" AND convert(int,b.ReportMonth) > @ReportMonth "); 91 } 92 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReportMonth", model.Month)); 93 94 } 95 96 if (!string.IsNullOrEmpty(DeptID)) 97 { 98 searchSql.AppendLine(" AND (CHARINDEX(‘,‘ +LTRIM(d.DeptID),(@DeptID))>0 or CHARINDEX(RTRIM(d.DeptID)+‘,‘,(@DeptID))>0 or CHARINDEX(LTRIM(d.DeptID),(@DeptID))>0) "); 99 comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", DeptID)); 100 } 101 #endregion
3. SQL Statements
1 SELECT 2 A.ID,A.ReprotNo,A.CompanyCD,A.DeptName,A.EmployeeID,A.EmployeeName,A.Remarks, 3 isnull(A.BFGJJ,0.00) BFGJJ, 4 isnull(A.BFGZ,0.00) BFGZ, 5 isnull(A.BLGZ,0.00) BLGZ, 6 isnull(A.CTF,0.00) CTF, 7 isnull(A.DTF,0.00) DTF, 8 isnull(A.FTF,0.00) FTF, 9 isnull(A.GHF,0.00) GHF, 10 isnull(A.GJJ,0.00) GJJ, 11 isnull(A.GTS,0.00) GTS, 12 isnull(A.GWF,0.00) GWF, 13 isnull(A.JBGZ,0.00) JBGZ, 14 isnull(A.JiangJ,0.00) JiangJ, 15 isnull(A.JZZYBF,0.00) JZZYBF, 16 isnull(A.KCBJ,0.00) KCBJ, 17 isnull(A.MTF,0.00) MTF, 18 isnull(A.QT,0.00) QT, 19 isnull(A.QTE,0.00) QTE, 20 isnull(A.QTY,0.00) QTY, 21 isnull(A.SBJ,0.00) SBJ, 22 isnull(A.Total,0.00) Total, 23 isnull(A.TotalOne,0.00) TotalOne, 24 isnull(A.TotalTwo,0.00) TotalTwo, 25 isnull(A.YBJ,0.00) YBJ, 26 isnull(A.YLJ,0.00) YLJ 27 ,c.DeptName as DeptWprkName 28 ,Substring(b.ReportMonth, 1, 4) + ‘年‘ 29 + Substring(b.ReportMonth, 5, 2) + ‘月‘ 30 AS ReportMonth 31 FROM officedba.SalaryInfo a 32 left join officedba.SalaryReport b on a.ReprotNo=b.ReprotNo 33 left join officedba.DeptInfo c on b.DeptID=c.ID