c# - 关键字“null”附近的语法不正确
问题描述
我正在生成员工考勤记录,并创建了一个可以正常工作并返回预期结果的存储过程,但是当我尝试在 c# 中运行存储过程时,它会生成异常消息,即关键字“null”附近的语法不正确
Visual Studio 数据集预览数据窗格结果:
CREATE PROCEDURE [dbo].[spMonthlyAttReport]
@fkSerial [NUMERIC] = 0,
@attDateTime [DATETIME],
@Company NVARCHAR(50) = NULL,
@Department NVARCHAR(50) = NULL
AS
BEGIN
DECLARE @StartOfMonth DATETIME
DECLARE @EndOfMonth DATETIME
DECLARE @month INT
DECLARE @year INT
DECLARE @day INT
SET @month = DATEPART(MONTH, @attDateTime)
SET @year = DATEPART(YEAR, @attDateTime)
SET @day = DATEPART(DAY, @attDateTime)
SET @StartOfMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
SET @EndOfMonth =
DATEADD(
SECOND,
-1,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
)
;
WITH DATERANGE AS
(
SELECT
DT = DATEADD(DD, 0, @StartOfMonth)
WHERE DATEADD(DD, 1, @StartOfMonth) <= @EndOfMonth
UNION ALL
SELECT
DATEADD(DD, 1, DT)
FROM DATERANGE
WHERE DATEADD(DD, 1, DT) <= @EndOfMonth
)
SELECT
CONVERT(DATE, DATERANGE.DT, 101) AS DateofAtt,
YEAR(CONVERT(DATE, DATERANGE.DT, 101)) AS [attyear],
MONTH(CONVERT(DATE, DATERANGE.DT, 101)) AS [attMonth],
DAY(CONVERT(DATE, DATERANGE.DT, 101)) AS [attDay],
CONVERT(VARCHAR(3), CONVERT(DATE, DATERANGE.DT, 101), 100) AS [attMonthName],
DATENAME(dw, CONVERT(DATE, DATERANGE.DT, 101)) AS [attDayName],
tblEmployInfo.company,
tblEmployInfo.serial,
attInAt =
(
SELECT
MIN(attDateTime)
FROM tblAttendance
WHERE tblAttendance.attDate = CONVERT(DATE, DATERANGE.DT, 101)
AND tblAttendance.attINOUT = 1
AND tblAttendance.fkSerial = tblEmployInfo.serial
),
attOutAt =
(
SELECT
MAX(attDateTime)
FROM tblAttendance
WHERE tblAttendance.attDate = CONVERT(DATE, DATERANGE.DT, 101)
AND tblAttendance.attINOUT = 0
AND tblAttendance.fkSerial = tblEmployInfo.serial
),
tblEmployInfo.[name],
tblEmployInfo.department
FROM DATERANGE,
tblAttendance,
tblEmployInfo
WHERE ((tblEmployInfo.Serial = @fkSerial OR @fkSerial = 0)
AND (tblEmployInfo.department = @Department OR @Department IS NULL)
AND (tblAttendance.attDate IN (SELECT * FROM DATERANGE))
AND (tblEmployInfo.company = @Company OR @Company IS NULL)
)
GROUP BY CONVERT(DATE, DATERANGE.DT, 101),
tblEmployInfo.company,
tblEmployInfo.serial,
tblEmployInfo.[name],
tblEmployInfo.department
ORDER BY tblEmployInfo.serial,
CONVERT(DATE, DATERANGE.DT, 101)
END
C# 代码
attMonthlyReportDataSetTableAdapters.spMonthlyAttReportTableAdapter adp =
new attMonthlyReportDataSetTableAdapters.spMonthlyAttReportTableAdapter();
attMonthlyReportDataSet ds = new attMonthlyReportDataSet();
DateTime date1 = DateTime.Parse("04/12/2019");
adp.Fill(ds.spMonthlyAttReport, 0, date1,"ZCG","ITD");
DataTable dt = new DataTable();
dt = ds.spMonthlyAttReport;
ReportViewer1.LocalReport.DataSources.Clear();
ReportDataSource rds = new ReportDataSource("attMonthlyReportDataSet_spMonthlyAttReport", dt);
ReportViewer1.LocalReport.DataSources.Add(rds);
ReportViewer1.LocalReport.Refresh();
解决方案
推荐阅读
- c# - 如何在 Angular 中将对象与键和值绑定?
- javascript - 更改 Redoc 鉴别器属性的类型和/或处理 Redoc post init
- delphi - 创建一个函数以根据定义的百分比返回 1 或 0
- sql - 如何将文件夹中的所有文件上传到 MS ACCESS(SQL 查询)?
- android - 即使使用 viewLifeCycleOwner,在更改设备方向时也会多次注册 LiveData 绑定观察者
- linux - 在 Docker 容器中挂载 Windows 网络驱动器 (Linux)
- r - 将列表组件组合成一个向量
- rust - 从 `MaybeUninit<[{integer}; 初始化整数数组的性能;N]>`
- google-sheets - 如何提取前 5 个总和值及其前 3 个总和原因?
- r - 如何在 R 中使用聚类分析来识别仅给定存在-不存在数据的植被群落?