报错信息：
1. 
2. 
3. 
4. 
5. 
6. 
解决方法：
1. Data Layer SQL 语句取数据时，把其列值有为null的字段用0.00替换，（ISNULL的用法）；
2.
#region 查询工资信息
/// <summary>
/// 查询工资信息
/// </summary>
/// <param name="model"></param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="ord"></param>
/// <param name="TotalCount"></param>
/// <returns></returns>
public static DataTable GetSalaryInfoByEmployee(SalaryInfoModel model, string ReportType, string DeptID, int pageIndex, int pageCount, string ord, string endmonth, ref int TotalCount)
{
#region 查询语句
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" A.ID,A.ReprotNo,A.CompanyCD,A.DeptName,A.EmployeeID,A.EmployeeName,A.Remarks,");
searchSql.AppendLine("isnull(A.BFGJJ,0.00) BFGJJ,");
searchSql.AppendLine("isnull(A.BFGZ,0.00) BFGZ, ");
searchSql.AppendLine("isnull(A.BLGZ,0.00) BLGZ,");
searchSql.AppendLine("isnull(A.CTF,0.00) CTF, ");
searchSql.AppendLine("isnull(A.DTF,0.00) DTF,");
searchSql.AppendLine("isnull(A.FTF,0.00) FTF, ");
searchSql.AppendLine("isnull(A.GHF,0.00) GHF,");
searchSql.AppendLine("isnull(A.GJJ,0.00) GJJ,");
searchSql.AppendLine("isnull(A.GTS,0.00) GTS,");
searchSql.AppendLine("isnull(A.GWF,0.00) GWF,");
searchSql.AppendLine("isnull(A.JBGZ,0.00) JBGZ,");
searchSql.AppendLine("isnull(A.JiangJ,0.00) JiangJ,");
searchSql.AppendLine("isnull(A.JZZYBF,0.00) JZZYBF,");
searchSql.AppendLine("isnull(A.KCBJ,0.00) KCBJ, ");
searchSql.AppendLine("isnull(A.MTF,0.00) MTF, ");
searchSql.AppendLine("isnull(A.QT,0.00) QT, ");
searchSql.AppendLine("isnull(A.QTE,0.00) QTE, ");
searchSql.AppendLine("isnull(A.QTY,0.00) QTY, ");
searchSql.AppendLine("isnull(A.SBJ,0.00) SBJ, ");
searchSql.AppendLine("isnull(A.Total,0.00) Total, ");
searchSql.AppendLine("isnull(A.TotalOne,0.00) TotalOne, ");
searchSql.AppendLine("isnull(A.TotalTwo,0.00) TotalTwo, ");
searchSql.AppendLine("isnull(A.YBJ,0.00) YBJ, ");
searchSql.AppendLine("isnull(A.YLJ,0.00) YLJ ");
searchSql.AppendLine(" ,c.DeptName as DeptWprkName ");
searchSql.AppendLine(" ,Substring(b.ReportMonth, 1, 4) + '年' ");
searchSql.AppendLine(" + Substring(b.ReportMonth, 5, 2) + '月' ");
searchSql.AppendLine(" AS ReportMonth ");
searchSql.AppendLine(" FROM officedba.SalaryInfo a ");
searchSql.AppendLine(" left join officedba.SalaryReport b on a.ReprotNo=b.ReprotNo ");
searchSql.AppendLine(" left join officedba.DeptInfo c on b.DeptID=c.ID ");
searchSql.AppendLine(" left join officedba.EmployeeInfo d on a.employeeID=d.ID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" a.CompanyCD = @CompanyCD ");
searchSql.AppendLine(" AND b.ReportType = @ReportType "); #endregion //定义查询的命令
SqlCommand comm = new SqlCommand();
//公司代码
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReportType", ReportType));
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
if (userInfo.EmployeeID != && userInfo.EmployeeID != && userInfo.EmployeeID != && userInfo.EmployeeID != )
{
searchSql.AppendLine(" AND b.DeptId in (select emp.DepID from officedba.EmpAndDep emp where emp.EmpID=@EmployeeID) ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", userInfo.EmployeeID.ToString()));
} #region 页面输入条件
//员工姓名
if (!string.IsNullOrEmpty(model.EmployeeName))
{
searchSql.AppendLine(" AND A.EmployeeName LIKE '%' + @EmployeeName + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeName", model.EmployeeName));
} if (!string.IsNullOrEmpty(model.DeptName))
{
searchSql.AppendLine(" AND B.DeptID in (" + model.DeptName + ") ");
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptName", model.DeptName));
}
//所属月份
if (!string.IsNullOrEmpty(model.Month))
{
if (endmonth != "")
{
searchSql.AppendLine(" AND convert(int,b.ReportMonth) between @ReportMonth and @endReportMonth ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@endReportMonth", endmonth));
}
else
{
searchSql.AppendLine(" AND convert(int,b.ReportMonth) > @ReportMonth ");
}
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReportMonth", model.Month)); } if (!string.IsNullOrEmpty(DeptID))
{
searchSql.AppendLine(" AND (CHARINDEX(',' +LTRIM(d.DeptID),(@DeptID))>0 or CHARINDEX(RTRIM(d.DeptID)+',',(@DeptID))>0 or CHARINDEX(LTRIM(d.DeptID),(@DeptID))>0) ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", DeptID));
}
#endregion
3. SQL Statements
SELECT
A.ID,A.ReprotNo,A.CompanyCD,A.DeptName,A.EmployeeID,A.EmployeeName,A.Remarks,
isnull(A.BFGJJ,0.00) BFGJJ,
isnull(A.BFGZ,0.00) BFGZ,
isnull(A.BLGZ,0.00) BLGZ,
isnull(A.CTF,0.00) CTF,
isnull(A.DTF,0.00) DTF,
isnull(A.FTF,0.00) FTF,
isnull(A.GHF,0.00) GHF,
isnull(A.GJJ,0.00) GJJ,
isnull(A.GTS,0.00) GTS,
isnull(A.GWF,0.00) GWF,
isnull(A.JBGZ,0.00) JBGZ,
isnull(A.JiangJ,0.00) JiangJ,
isnull(A.JZZYBF,0.00) JZZYBF,
isnull(A.KCBJ,0.00) KCBJ,
isnull(A.MTF,0.00) MTF,
isnull(A.QT,0.00) QT,
isnull(A.QTE,0.00) QTE,
isnull(A.QTY,0.00) QTY,
isnull(A.SBJ,0.00) SBJ,
isnull(A.Total,0.00) Total,
isnull(A.TotalOne,0.00) TotalOne,
isnull(A.TotalTwo,0.00) TotalTwo,
isnull(A.YBJ,0.00) YBJ,
isnull(A.YLJ,0.00) YLJ
,c.DeptName as DeptWprkName
,Substring(b.ReportMonth, 1, 4) + '年'
+ Substring(b.ReportMonth, 5, 2) + '月'
AS ReportMonth
FROM officedba.SalaryInfo a
left join officedba.SalaryReport b on a.ReprotNo=b.ReprotNo
left join officedba.DeptInfo c on b.DeptID=c.ID