如何解决DataReader未关闭错误？图表数据填充代码求助

Hey there! Let's figure out why you're getting that "There is already an open DataReader" error and get your chart populated correctly.

What's Causing the Error? #

The main issue is that your code tries to use the same database connection for multiple operations while a MySqlDataReader is still open. Specifically, your Catch block calls cmd.ExecuteNonQuery()—this attempts to run another command on the same connection before closing the DataReader, which directly triggers the error. On top of that, your code isn't properly cleaning up database resources (like the DataReader, Command, or Connection), leading to lingering open connections/readers.

Other Issues in Your Code #

• Mismatched Field Name: Your SQL selects department as 'DEPARTMENT', but your code tries to read dr.GetString("Course")—this will throw an error because "Course" doesn't exist in your query results.
• Incomplete Data Handling: You're only adding the "VOTED" count to your chart, but your query also returns "NOT YET VOTED" data that you're ignoring.
• Poor Resource Management: You're not properly disposing of MySqlCommand, MySqlDataReader, or the connection, which can lead to resource leaks and unexpected behavior.

Corrected Code #

Here's a revised version that fixes all these issues, uses proper resource management, and populates both data series in your chart:

Public Sub ChartAll(ByVal chart1 As Object)
    ' Use Using statements to auto-dispose resources when done
    Using myconnection As New MySqlConnection("Your Connection String Here") ' Replace with your actual connection string
        myconnection.Open()
        Dim str As String = "SELECT department as 'DEPARTMENT', 
                             COUNT(CASE WHEN voterstatus = '1' THEN 1 END) AS 'VOTED', 
                             COUNT(CASE WHEN voterstatus = '0' THEN 1 END) AS 'NOT_YET_VOTED' 
                             FROM tblvoter 
                             GROUP BY department"
        
        Using cmd As New MySqlCommand(str, myconnection)
            Using dr As MySqlDataReader = cmd.ExecuteReader()
                ' Clear existing points to avoid duplicate data
                chart1.Series("Series1").Points.Clear()
                chart1.Series("Series2").Points.Clear() ' Ensure this series exists in your chart setup

                While dr.Read()
                    ' Get department name from the query results
                    Dim deptName As String = dr.GetString("DEPARTMENT")
                    ' Handle DBNull cases to avoid errors if counts are zero
                    Dim votedCount As Integer = If(dr.IsDBNull(dr.GetOrdinal("VOTED")), 0, dr.GetInt32("VOTED"))
                    Dim notVotedCount As Integer = If(dr.IsDBNull(dr.GetOrdinal("NOT_YET_VOTED")), 0, dr.GetInt32("NOT_YET_VOTED"))

                    ' Add data to both chart series
                    chart1.Series("Series1").Points.AddXY(deptName, votedCount)
                    chart1.Series("Series2").Points.AddXY(deptName, notVotedCount)
                End While
            End Using ' Auto-closes and disposes the DataReader
        End Using ' Auto-disposes the Command
    End Using ' Auto-closes and disposes the Connection
Catch ex As Exception
    ' Handle errors properly (e.g., show a user-friendly message or log details)
    MessageBox.Show($"Error loading chart data: {ex.Message}")
End Sub

Key Improvements #

• Using Statements: These automatically close and dispose of database objects when they go out of scope, eliminating the chance of open DataReaders/connections lingering.
• Fixed Field Names: Now we read the correct DEPARTMENT field instead of the non-existent "Course".
• Full Data Handling: The code now adds both "VOTED" and "NOT YET VOTED" data to your chart (make sure your chart has two series named "Series1" and "Series2", or adjust the names to match your setup).
• Proper Error Handling: Removed the problematic cmd.ExecuteNonQuery() from the catch block and replaced it with a clear error message.
• DBNull Handling: Added checks to avoid errors if a department has zero votes in either category.

Quick Notes #

• Replace "Your Connection String Here" with your actual MySQL connection string. If your original conn() method handles connection creation, you can adapt that, but using Using for the connection is still the best practice.
• Double-check that your chart control has the two series configured correctly (e.g., bar/column chart type) to display the data as intended.

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