时间戳

首页 > 解决方案 > DataGridView 未显示表 VB.NET SqlServer 中的所有记录

sql-server - DataGridView 未显示表 VB.NET SqlServer 中的所有记录

问题描述

我有一个表 SalariesSettingsForEmployee ，其中保存了员工津贴和扣除额。

所有类型的津贴和扣除都加载到 GridView 列和 Gridview 行中的员工中。（具有各自的值）

问题是数据仅加载到一个（或第一个）员工的网格视图中，而不是全部。

我做了两个子程序。这是我的代码。

如果我在这里做错了什么，请告知。

谢谢并恭祝安康，

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

在此处输入图像描述

'

''''

标签： sql-servervb.netgridview

解决方案

问题是您正在DataRow这条线上创建一个单曲：

Dim dr As DataRow = dt.NewRow

并在循环中重用该行，覆盖在先前迭代中写入该行的值。您需要做的是为DataRow循环的每次迭代创建一个新的。

在您的第一个循环中：

While sreader.Read

    ' Create new row.
    Dim dr As DataRow = dt.NewRow

    subtotal = BasicSalary

同样对于第二个循环：

While rdr.Read
    tempval = 0

        ' Create new row.
        Dim dr As DataRow = dt.NewRow

        If rdr("AmountType").ToString = "Fixed Amount" Then

推荐阅读