如何在VB.NET的Chart控件Y轴显示数据库总行数并解决绑定报错

• YValueMembers属性要求传入绑定数据源中的列名称，你当前传入的是数据表总行数的数值字符串，你的DataTable中不存在对应名称的列，因此触发绑定异常。
• 你当前的SQL查询未做分组聚合处理，无法直接实现按purok列相同取值展示对应计数的需求。

方案1：按purok分组展示每组行数（常规统计需求） #

直接通过SQL的GROUP BY语法完成分组计数，查询结果直接作为图表数据源即可：

Dim READER As OleDbDataReader
Try
    ' 确保数据库连接已打开
    If con.State = ConnectionState.Closed Then
        con.Open()
    End If
    Dim query As String = "SELECT purok, COUNT(*) AS row_count FROM Household GROUP BY purok"
    cmd = New OleDbCommand(query, con)
    READER = cmd.ExecuteReader
    Dim dT As New DataTable
    dT.Load(READER)

    Chart1.Series("Purok").XValueMember = "purok"
    Chart1.Series("Purok").YValueMembers = "row_count"
    Chart1.DataSource = dT
    Chart1.DataBind()

    con.Close()
Catch ex As Exception
    MessageBox.Show(ex.Message)
    ' 异常场景下关闭连接避免资源泄漏
    If con.State = ConnectionState.Open Then
        con.Close()
    End If
End Try

方案2：所有purok项Y值均展示全表总行数 #

如果你的需求确实是所有X轴项的Y值都为整个Household表的总行数，可以给DataTable新增固定值列后再绑定：

Dim READER As OleDbDataReader
Try
    If con.State = ConnectionState.Closed Then
        con.Open()
    End If
    Dim query As String = "SELECT ID, purok FROM Household"
    cmd = New OleDbCommand(query, con)
    READER = cmd.ExecuteReader
    Dim dT As New DataTable
    dT.Load(READER)

    ' 新增存储全表总行数的列
    dT.Columns.Add("total_count", GetType(Integer))
    Dim totalRow As Integer = dT.Rows.Count
    For Each row As DataRow In dT.Rows
        row("total_count") = totalRow
    Next

    Chart1.Series("Purok").XValueMember = "purok"
    Chart1.Series("Purok").YValueMembers = "total_count"
    Chart1.DataSource = dT
    Chart1.DataBind()

    con.Close()
Catch ex As Exception
    MessageBox.Show(ex.Message)
    If con.State = ConnectionState.Open Then
        con.Close()
    End If
End Try

内容的提问来源于stack exchange，提问作者rg64