How to return student attendance in a month

Question

CREATE PROC [dbo].[proc-SelectStudentMonthAttendanceReport]
(
@ClassID NVARCHAR(150), @SectionID NVARCHAR(150), @AttendanceMonth datetime,@AttendanceYear datetime)
AS  
BEGIN
SELECT * INTO #Temp
FROM
  (SELECT  ROW_NUMBER() OVER (ORDER BY StudentName ASC) As SrNo, StudentAdmission.StudentName, StudentAttendance.Attendance, StudentAttendance.AttendanceDate
FROM            StudentAdmission INNER JOIN
                         Class ON StudentAdmission.ClassID = Class.ClassID INNER JOIN
                         Section ON StudentAdmission.SectionID = Section.SectionID INNER JOIN
                         StudentAttendance ON StudentAdmission.StudentID = StudentAttendance.StudentID
                         WHERE Class.ClassID = @ClassID AND Section.SectionID = @SectionID AND MONTH(studentAttendance.AttendanceDate) = MONTH(@AttendanceMonth) AND YEAR(studentAttendance.AttendanceDate) = YEAR(@AttendanceYear)) as x  
  DECLARE @DynamicPivotQuery AS NVARCHAR(MAX), @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName +',','')+ QUOTENAME(AttendanceDate) FROM (SELECT DISTINCT AttendanceDate
FROM #Temp) AS [Days]
SET @DynamicPivotQuery =';WITH CTE AS(SELECT [AttendanceDate], StudentName, Attendance FROM #Temp)
SELECT StudentName,'+@ColumnName+' FROM CTE
PIVOT (MAX(Attendance) FOR [AttendanceDate] IN('+@ColumnName+')) p
ORDER BY StudentName DESC'
EXEC(@DynamicPivotQuery)
DROP TABLE #Temp
END

VB.NET Code to return value

Private Sub lbFilterClass_Click(sender As Object, e As EventArgs) Handles lbFilterClass.Click
    Dim amonth As Integer = Convert.ToInt32(ddlMonth.SelectedValue)
    Dim ayear As Integer = Convert.ToInt32(ddlYear.SelectedItem.Text)

    Dim constring As String = ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString
    Dim con As New SqlConnection(constring)
    Try
        con.Open()
        Dim sqlComm As New SqlCommand("proc-SelectStudentMonthAttendanceReport", con)
        sqlComm.Parameters.AddWithValue("@AttendanceMonth", amonth)
        sqlComm.Parameters.AddWithValue("@AttendanceYear", ayear)
        sqlComm.Parameters.AddWithValue("@ClassID", ddlClass.SelectedValue)
        sqlComm.Parameters.AddWithValue("@SectionID", ddlSection.SelectedValue)

        sqlComm.CommandType = CommandType.StoredProcedure
        Dim da As SqlDataAdapter = New SqlDataAdapter(sqlComm)
        Dim ds As New DataSet()
        'Dim da As New SqlDataAdapter()
        'da.SelectCommand = sqlComm
        da.Fill(ds)
        con.Close()
        If ds.Tables(0).Rows.Count > 0 Then
            For i As Integer = 1 To DateTime.DaysInMonth(ayear, amonth)
                Dim columnExist As Boolean = False
                For j As Integer = 1 To ds.Tables(0).Columns.Count - 1
                    If i.ToString() = ds.Tables(0).Columns(j).ColumnName Then
                        columnExist = True
                    End If
                Next
                If Not columnExist Then
                    Dim col As New DataColumn() With {
                        .ColumnName = i.ToString(),
                        .DataType = GetType(String),
                        .DefaultValue = "P/A"
                    }
                    ds.Tables(0).Columns.Add(col)
                End If
            Next
            gvStaff.DataSource = ds.Tables(0)
            gvStaff.DataBind()
        Else
            lblerror.ForeColor = System.Drawing.Color.Red
            lblerror.Text = "Record Not Found"
        End If
    Catch ex As Exception
        lblerror.ForeColor = System.Drawing.Color.Red
        lblerror.Text = ex.Message
    Finally
        con.Close()
    End Try
End Sub

Problem is code displaying "Cannot find table 0."

Comment out the Try, comment out the Catch section, and comment out the Finally (you can use `con?.Dispose()` instead of the code in the Finally) so that you get to see the complete actual error message - and you'll get to find out which line the error happens on, which we need to know to be able to help you. — Andrew Morton, Jun 21 '22 at 17:58
Did as you advice and it return "Cannot find table 0" indicating this If ds.Tables(0).Rows.Count > 0 Then — Samuel Eli, Jun 21 '22 at 18:32
OK, the next thing to check is if the parameters that were passed to the query have the values you expected. You see how you made `amonth` and `ayear` into separate variables? If you do the same for the other two parameters then it will be easy to put a breakpoint after those variables are set so that you can examine their values. — Andrew Morton, Jun 21 '22 at 20:35
If you run your stored proc via SSMS do you get any result? The error suggests that nothing is being returned via the Fill command. Also notice you're passing integers into varchar parameters, Would warn against using Parameters.AddWithValue(). Better to use Parameters.Add where you've got more control over the types — Hursey, Jun 21 '22 at 20:51
@AndrewMorton, the parameter in SQL Server return the value or result the way i want it to appear with any issue — Samuel Eli, Jun 22 '22 at 06:24
@AndrewMorton, Hursey, thank you guys for your assistance. I have sorted it out and it working the way i want it to run now — Samuel Eli, Jun 22 '22 at 07:34
Perhaps if you’ve got a solution, you might like to post it as a answer so others might be able use what you’ve learned — Hursey, Jun 23 '22 at 03:20

score 0 · Accepted Answer · edited Jun 27 '22 at 17:06

Here is my code of achieving the desire result of attendance in a month using VB.NET, Code below

Private Sub lbFilterClass_Click(sender As Object, e As EventArgs) Handles lbFilterClass.Click

    Dim amonth As Integer = Convert.ToInt32(ddlMonth.SelectedValue)
    Dim ayear As Integer = Convert.ToInt32(ddlYear.SelectedItem.Text)
    Dim classid As String = ddlClass.SelectedValue
    Dim sectionid As String = ddlSection.SelectedValue
    Dim constring As String = ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString
    Dim con As New SqlConnection(constring)
    Try
        con.Open()
        Dim sqlComm As New SqlCommand("proc-SelectStudentMonthAttendanceReport", con)
        sqlComm.Parameters.AddWithValue("@AttendanceMonth", amonth)
        sqlComm.Parameters.AddWithValue("@AttendanceYear", ayear)
        sqlComm.Parameters.AddWithValue("@ClassID", ddlClass.SelectedValue)
        sqlComm.Parameters.AddWithValue("@SectionID", ddlSection.SelectedValue)

        sqlComm.CommandType = CommandType.StoredProcedure
        Dim da As SqlDataAdapter = New SqlDataAdapter(sqlComm)
        Dim ds As New DataSet()
        da.Fill(ds)
        con.Close()
        If ds.Tables(0).Rows.Count > 0 Then
            For i As Integer = 1 To DateTime.DaysInMonth(ayear, amonth)
                Dim columnExist As Boolean = False
                For j As Integer = 1 To ds.Tables(0).Columns.Count - 1
                    If i.ToString() = ds.Tables(0).Columns(j).ColumnName Then
                        columnExist = True
                    End If
                Next
                If Not columnExist Then
                    Dim col As New DataColumn() With {
                            .ColumnName = i.ToString(),
                            .DataType = GetType(String),
                            .DefaultValue = "-"
                        }
                    ds.Tables(0).Columns.Add(col)
                End If
            Next
            gvStaff.DataSource = ds.Tables(0)
            gvStaff.DataBind()
            pnlClass.Visible = True
        Else
            pnlClass.Visible = False
        End If

    Catch ex As Exception

        pnlClass.Visible = False

    Finally
        con.Close()
    End Try

End Sub

Below is the SQL Server Table and Stored Procedure; [Student Attendance Table] which inner join to student table to return student Name using the StudentID

Student Attendance Table

CREATE PROC [dbo].[Proc-selectstudentmonthattendancereport] (@ClassID
NVARCHAR(150),
                                                             @SectionID
NVARCHAR(150),
                                                             @AttendanceMonth
INT,
                                                             @AttendanceYear
INT)
AS
  BEGIN
      SELECT *
      INTO   #temp
      FROM   (SELECT Row_number()
                       OVER (
                         ORDER BY studentname ASC) AS SrNo,
                     studentadmission.studentname,
                     studentattendance.attendance,
                     studentattendance.attendanceday
              FROM   studentadmission
                     INNER JOIN class
                             ON studentadmission.classid = class.classid
                     INNER JOIN section
                             ON studentadmission.sectionid = section.sectionid
                     INNER JOIN studentattendance
                             ON studentadmission.studentid =
                                studentattendance.studentid
              WHERE  class.classid = @ClassID
                     AND section.sectionid = @SectionID
                     AND Month(studentattendance.attendancedate) =
                         @AttendanceMonth
                     AND Year(studentattendance.attendancedate) =
                         @AttendanceYear)
             AS
             x

      DECLARE @DynamicPivotQuery AS NVARCHAR(max),
              @ColumnName        AS NVARCHAR(max)

      SELECT @ColumnName = Isnull(@ColumnName +',', '')
                           + Quotename(attendanceday)
      FROM   (SELECT DISTINCT attendanceday
              FROM   #temp) AS [Days]

      SET @DynamicPivotQuery =';WITH CTE AS(SELECT [AttendanceDay], StudentName, Attendance FROM #Temp) SELECT StudentName,' + @ColumnName
                              + ' FROM CTE PIVOT (MAX(Attendance) FOR [AttendanceDay] IN('
                              + @ColumnName + ')) p ORDER BY StudentName DESC'

      EXEC(@DynamicPivotQuery)

      DROP TABLE #temp
END

Just so you know: [AddWithValue is Evil](https://www.dbdelta.com/addwithvalue-is-evil/), [AddWithValue is evil!](https://chrisrickard.blogspot.com/2007/06/addwithvalue-is-evil.html), and [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). — Andrew Morton, Jun 23 '22 at 20:10
Also, you need to call .Dispose() on the database connection so that unmanaged resources can be cleaned up, e.g. with `conn?.Dispose()` in the `Finally` section. — Andrew Morton, Jun 23 '22 at 20:12
@AndrewMorton, Is there any property to use in place of AddWithValue since it works well with stored procedure — Samuel Eli, Jun 27 '22 at 13:45
@AndrewMorton, Are you advicing to use dispose() instead of close()?. please i need further explanation of the two. — Samuel Eli, Jun 27 '22 at 13:53
The links I gave regarding AddWithValue have the alternative, and [here's an example](https://stackoverflow.com/a/69795931/1115360) of using SQL parameters in a previous answer by me. — Andrew Morton, Jun 27 '22 at 17:00
Yes, you must call .Dispose() on instances of classes which support it. It "[Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.](https://docs.microsoft.com/en-us/dotnet/api/system.idisposable.dispose)" If you look at the documentation for the [SqlCommand Class](https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand) (select VB instead of C# in the menu near the top), you will see that the [Using Statement](https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/using-statement) is used... — Andrew Morton, Jun 27 '22 at 17:04
... which calls Dispose() for you even if something goes wrong. — Andrew Morton, Jun 27 '22 at 17:05

How to return student attendance in a month

1 Answers1