Click here to Skip to main content
14,328,797 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Posted
Updated 19-Jul-19 17:43pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You have to make a new DataRow for every record you're going to add. You cannot re-user the same DataRow object over and over.
   
Comments
UCP_2005 20-Jul-19 4:09am
   
Thanks Dave,
I added a new DataRow In the deduction area of the code.
It shows the record from EmployeeID to Gross Total in first row and then in second row
the record from EmployeeID to Gross Total is empty and it shows the Monthly Deduction (Deduction type) and NET Salary of the first employee.

Can you please help.

Thanks and Regards,
Dave Kreskowiak 20-Jul-19 11:13am
   
I have no idea what you did and I'm not even entirely sure you know what your own code is doing. From the description you just gave, I get the feeling you just moved a line somewhere and didn't really think about why you did it. It was just a guess.

There's what appears to be a ton of problems with this code, one of which is the GrandTotal. You're setting GrandTotal to the value of subtotal inside a loop. That's wrong for an actual GrandTotal.
UCP_2005 20-Jul-19 12:58pm
   
Hi Dave, Thanks for your reply. It was really a guess.
I have inserted separate datarows in each loop.

While sreader.Read
Dim Dr as Datarow=dt.NewRow
subtotal = BasicSalary

All employees are shown now but the problem is its creating new row for every new Pay head (Like Basic Salary, Transport allowance). I want to show the payheads of each employee in its respective row. Please suggest me a way to show all payheads of any employee in a row. Thanks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100