Блог

я создаю запись посещаемости сотрудника и создал хранимую процедуру, которая работает нормально и возвращает ожидаемые результаты, но когда я пытаюсь запустить хранимую процедуру в 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();