Gridview is not showing all records VB.NET SQL server

See more:

VB

SQL

Hi All,

I have a table SalariesSettingsForEmployee in which the employee Allowances and Deductions are saved.

All Types of Allowances and Deductions are loaded into GridView columns and employees in Gridview Rows. (with their respective values)

Problem is the data is loaded into Grid View only of one (or first) employee and not all.

I made two subroutines.
Here is my code.

Kindly advise if I am doing something wrong here.

Thanks and Regards,

What I have tried:

Sub CreateEmployeeRows()

        Dim subtotal As Double = 0
        Dim NetTotal As Double = 0
        Dim tempval As Double = 0
        Dim rno As Integer = 0
        Dim BasicSalary As Double = 0
        Dim DeductionTotal As Double = 0
        Dim GrandTotal As Double = 0

        BasicSalary = SQLGetNumericFieldValue("Select Amount From SalariesSettingsForEmployee Where AllowanceName='BASIC SALARY'", "Amount")
        Dim dr As DataRow = dt.NewRow
        Dim sqlcmmd As New SqlClient.SqlCommand
        Try
            sqlcmmd.Connection = DAL.OpenSqlConnection
            sqlcmmd.CommandText = "Select * From SalariesSettingsForEmployee Where AllowanceType='Allowance'"
            Dim sreader As SqlClient.SqlDataReader

            sreader = sqlcmmd.ExecuteReader
            While sreader.Read


                subtotal = BasicSalary

                dr(0) = sreader("EmployeeCode").ToString
                dr(1) = sreader("EmployeeName").ToString
                dr(2) = sreader("BranchCode")
                dr(3) = sreader("BranchName")

                If sreader("AmountType").ToString = "Fixed Amount" Then
                    tempval = sreader("Amount")
                    subtotal = subtotal + tempval
                ElseIf sreader("AmountType").ToString = "Percentage" Then
                    tempval = subtotal * CDbl(sreader("Amount")) / 100
                    subtotal = subtotal + tempval
                End If
                dr(sreader("AllowanceName")) = tempval
                ''sreader("Amount")
                GrandTotal = subtotal
                dr(GrossTotalColName) = GrandTotal
                dt.Rows.Add(dr)
                txtRunPayrollGird.DataSource = dt

            End While
            sreader.Close()
            sreader = Nothing
            DAL.CloseSQLConnection()
        Catch ex As Exception

        End Try
        'End of Allowance Totals


        'Calculate Deduction Totals


        Dim cmd As New SqlClient.SqlCommand
        Try
            cmd.Connection = DAL.OpenSqlConnection
            cmd.CommandText = "Select * From SalariesSettingsForEmployee Where AllowanceType='Deduction'"

            Dim rdr As SqlClient.SqlDataReader
            rdr = cmd.ExecuteReader

            While rdr.Read
                tempval = 0

                If rdr("AmountType").ToString = "Fixed Amount" Then
                    tempval = rdr("Amount")
                    subtotal = subtotal + tempval
                    DeductionTotal = DeductionTotal + tempval
                ElseIf rdr("AmountType").ToString = "Percentage" Then
                    tempval = GrandTotal * CDbl(rdr("Amount")) / 100
                    subtotal = subtotal + tempval
                End If
                dr(rdr("AllowanceName")) = tempval
                ''sreader("Amount")

                dr(NetSalaryColName) = GrandTotal - DeductionTotal
                NetTotal = NetTotal + (GrandTotal - DeductionTotal)
                txtNetPayable.Text = NetTotal
                dt.Rows.Add(dr)
                txtRunPayrollGird.DataSource = dt
            End While

        Catch ex As Exception

        End Try
    End Sub

Sub CreateColumns()
        Dim Sqlcmmd As New SqlClient.SqlCommand
        Dim Sreader As SqlClient.SqlDataReader
        Try

            dt = New DataTable
            dt.Columns.Add("Employee ID")
            dt.Columns.Add("Employee Name")
            dt.Columns.Add("Branch Code")
            dt.Columns.Add("Branch Name")


            'For Additions

            Sqlcmmd.Connection = DAL.OpenSqlConnection
            Sqlcmmd.CommandText = "SELECT * FROM SalariesPaySettings WHERE AllowanceType='Allowance'"
            Sqlcmmd.CommandType = CommandType.Text

            Sreader = Sqlcmmd.ExecuteReader

            While Sreader.Read

                dt.Columns.Add(Sreader("AllowanceName").ToString.Trim)

            End While
            dt.Columns.Add(GrossTotalColName)
            Sreader.Close()
            Sreader = Nothing
            DAL.CloseSQLConnection()
            Sqlcmmd.Connection = Nothing
        Catch ex As Exception

        End Try
        'For Deductions
        Try


            Dim Sqlcmmd1 As New SqlClient.SqlCommand
            Sqlcmmd1.Connection = DAL.OpenSqlConnection
            Sqlcmmd1.CommandText = "SELECT * FROM SalariesPaySettings WHERE AllowanceType='Deduction'"
            Sqlcmmd1.CommandType = CommandType.Text
            Dim Sreader1 As SqlClient.SqlDataReader
            Sreader1 = Sqlcmmd1.ExecuteReader

            While Sreader1.Read

                dt.Columns.Add(Sreader1("AllowanceName").ToString.Trim)

            End While
            dt.Columns.Add(NetSalaryColName)
            txtRunPayrollGird.DataSource = dt
            Sreader1.Close()
            Sreader1 = Nothing
            DAL.CloseSQLConnection()
        Catch ex As Exception

        End Try


    End Sub