把数据从MySQL到vb.net中的表中的标签

Question

我想从MySQL数据库中的表中标签中放置每个月的总和，但我不知道它在标签中cz我有lbl1，lbl2， ... lbl12。
我的代码：

connection.Open() 

      query = " SELECT SUM(Amount_income_table), MONTHNAME(Date_income_table) 
         FROM bacci.income_table 
         where year(Date_income_table)='" & LblYear.Text & "' 
         GROUP BY MONTHNAME(Date_income_table);" 
      Comand = New MySqlCommand(query, connection) 
      READER = Comand.ExecuteReader 
      While READER.Read 
       ChartIncomeYear.Series("Incomes").Points.AddXY(READER.GetString("MONTHNAME(Date_income_table)"), READER.GetString("SUM(Amount_income_table)")) 
      End While 
      connection.Close() 
     Catch ex As Exception 
      MessageBox.Show(ex.Message) 
     Finally 

      connection.Dispose() 
     End Try 

该代码将填补图表，但我也想用相同的查询，以填补标签。

Answer 1

您不应该将您的标签设置为lbl1，lbl2和lbl3等。您应该在运行时创建它们。试试这个代码我是一个空白的项目。从这个例子中调整你的代码。我喜欢使用对象的名单，但你可以使用数组太

Dim LabelList As List(Of Label) 


Sub LoadSumLabel() 
    LabelList = New List(Of Label) 
    For x = 1 To 12 
     Dim NewLabel As New Label 
     With NewLabel 
      .Name = DateAndTime.MonthName(x) 
      .Text = "0" 
      .AutoSize = True 
      .Left = 10 
      .Top = 10 + (LabelList.Count * NewLabel.Height) 
     End With 
     LabelList.Add(NewLabel) 
     Me.Controls.Add(LabelList.Item(LabelList.Count - 1)) 
     AddHandler LabelList.Item(LabelList.Count - 1).Click, AddressOf Label_Click 

     'you can create a panel and add you control to it the same way. So if you resize the form you can have the scroll bars if it doesnt fit 
     'somepanel.controls(LabelList.Item(LabelList.Count - 1)) 
    Next 
End Sub 

Private Sub Label_Click(sender As Object, e As EventArgs) 
    Dim thisLabel As Label = DirectCast(sender, Label) 
    MsgBox(thisLabel.Name, vbOKOnly, "Result") 
End Sub 

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load 
    LoadSumLabel() 
End Sub 

Sub RunQuery() 
    connection.Open() 

    query = " SELECT SUM(Amount_income_table), MONTHNAME(Date_income_table) 
        FROM bacci.income_table 
        where year(Date_income_table)='" & LblYear.Text & "' 
        GROUP BY MONTHNAME(Date_income_table);" 
    Comand = New MySqlCommand(query, connection) 
    READER = Comand.ExecuteReader 
    While READER.Read 
     ChartIncomeYear.Series("Incomes").Points.AddXY(READER.GetString("MONTHNAME(Date_income_table)"), READER.GetString("SUM(Amount_income_table)")) 
     LabelList.Find(Function(lb As Label) lb.Name = READER.GetString("MONTHNAME(Date_income_table)")).Text = READER.GetString("SUM(Amount_income_table)") 


    End While 
    connection.Close() 
    Catch ex As Exception 
    MessageBox.Show(ex.Message) 
    Finally 

    connection.Dispose() 
    End Try 
End Sub 

Answer 2

名称您的标签这样lblJanuary，lblFebruary，lblMarch .... lblDecember。然后你可以使用此代码轻松解决你的问题：

query = " SELECT SUM(Amount_income_table) as Total, MONTHNAME(Date_income_table) 
      FROM bacci.income_table 
      where year(Date_income_table)='" & LblYear.Text & "' 
      GROUP BY MONTHNAME(Date_income_table);" 
Comand = New MySqlCommand(query, connection) 
READER = Comand.ExecuteReader 
While READER.Read 
    ChartIncomeYear.Series("Incomes").Points.AddXY(READER.GetString("MONTHNAME(Date_income_table)"), READER.GetString("SUM(Amount_income_table)")) 
    Me.Controls("lbl" & READER.GetString("MONTHNAME(Date_income_table)")).Text = READER.GetString("Total") 
End While 
connection.Close() 
Catch ex As Exception 
    MessageBox.Show(ex.Message) 
Finally 

    connection.Dispose() 
End Try