My Blog

Charting with VB.Net 2010

21

There are many tutorials on the web regarding VB.Net Charting but I couldn’t find one which actually goes into details.  So I thought of creating one. The example that I will be using retrieves the data from an Access Database and populates the chart in VB.Net.


VB.Net Version 2010 Ultimate


MS Access: 2010

1) To start off, let’s create a small database in Access. Your Access Database looks like this once it is ready.

Name the Table as “Table1” and the Database as “Sample.accdb“. Save it to a location of your choice. Remember the location as we will have to use this database later from vb.net.

2) Start Visual Studio and create a new Windows Application Project on VB.NET.
Let’s Call it “Charting“. See snapshot below.

Click “OK” and then place the controls as shown in the image below.

In earlier versions of vb.net, you had to actually activate the chart control by right clicking on the “ContextMenuStrip” and then selecting MS Chart Control from the COM Tab. But now you can find that control right under the “Data” Tab.

Creating Basic Chart

Once your controls are ready, open the code editor and simply paste this code. This code will create a very basic chart.

Imports System.Data.OleDb
Imports System.Windows.Forms.DataVisualization.Charting

Public Class Form1

    '~~> Code required to browse for the Access Database File
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e _
    As System.EventArgs) Handles Button1.Click
        With OpenFileDialog1
            .DefaultExt = "accdb"
            .DereferenceLinks = True
            .Filter = _
             "Access files (*.accdb)|*.accdb"
            .Multiselect = False
            .Title = "Select an Access Database file to open"
            .ValidateNames = True

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Try
                    TextBox1.Text = .FileName
                Catch fileException As Exception
                    Throw fileException
                End Try
            End If
        End With
    End Sub

    '~~> Code to generate the chart
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal _
    e As System.EventArgs) Handles Button2.Click
        Dim strConn As String = _
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & TextBox1.Text & _
        ";Persist Security Info=False;"

        Dim tblFields As String = "SELECT * from Table1"

        Dim conn As New OleDbConnection(strConn)
        Dim oCmd As New OleDbCommand(tblFields, conn)
        Dim oData As New OleDbDataAdapter(tblFields, conn)
        Dim ds As New DataSet

        conn.Open()
        oData.Fill(ds, "Table1")
        conn.Close()

        Chart1.DataSource = ds.Tables("Table1")
        Dim Series1 As Series = Chart1.Series("Series1")
        Series1.Name = "Sales"
        Chart1.Series(Series1.Name).XValueMember = "nFruits"
        Chart1.Series(Series1.Name).YValueMembers = "nSales"

        Chart1.Size = New System.Drawing.Size(780, 350)
    End Sub

End Class

When you run the code your form will pop up and looks as shown in the image below. Select your file by clicking on the “Load File” button. Once you have selected the file, click on “Create Chart

When you click on “Create Chart” you see a chart generated as shown below.

Now this was making a very basic chart. What if we want it to give a more professional look? What if we wanted to format the title or make the chart look more appealing?

Advanced Charting

Now replace the old code with this new code. I have commented the code so you know what the code is doing.

Imports System.Data.OleDb
Imports System.Windows.Forms.DataVisualization.Charting

Public Class Form1

    '~~> Code required to browse for the Access Database File
    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
        With OpenFileDialog1
            .DefaultExt = "accdb"
            .DereferenceLinks = True
            .Filter = _
             "Access files (*.accdb)|*.accdb"
            .Multiselect = False
            .Title = "Select an Access Database file to open"
            .ValidateNames = True

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                Try
                    TextBox1.Text = .FileName
                Catch fileException As Exception
                    Throw fileException
                End Try
            End If
        End With
    End Sub

    '~~> Code to generate the chart
    Private Sub Button2_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button2.Click
        '~~> Connection String to connect to the access Database
        '~~> If you are planning to use SQL Database then 
        '~~> please visit www.connectionstrings.com for an
        '~~> appropriate connection string
        Dim strConn As String = _
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & TextBox1.Text & _
        ";Persist Security Info=False;"

        '~~> Query string
        Dim tblFields As String = "SELECT * from Table1"

        '~~> Connecting to Data base and storing the data in a dataset
        Dim conn As New OleDbConnection(strConn)
        Dim oCmd As New OleDbCommand(tblFields, conn)
        Dim oData As New OleDbDataAdapter(tblFields, conn)
        Dim ds As New DataSet

        conn.Open()
        oData.Fill(ds, "Table1")
        conn.Close()

        '''''''''''''''''''''''''''''
        '~~> SET DATA SOURCE <~~'
        '''''''''''''''''''''''''''''
        Chart1.DataSource = ds.Tables("Table1")

        ''''''''''''''''''''''''''''''''
        '~~> WORKING WITH CHARTAREA <~~'
        ''''''''''''''''''''''''''''''''
        Dim CArea As ChartArea = Chart1.ChartAreas(0)
        CArea.BackColor = Color.Azure           '~~> Changing the Back Color of the Chart Area 
        CArea.ShadowColor = Color.Red           '~~> Changing the Shadow Color of the Chart Area 
        CArea.Area3DStyle.Enable3D = True       '~~> Changing the Chart Style to 3D 

        '~~> Formatting X Axis
        CArea.AxisX.MajorGrid.Enabled = False   '~~> Removed the X axis major grids
        CArea.AxisX.LabelStyle.Font = New System.Drawing.Font("Times New Roman", _
        11.0F, System.Drawing.FontStyle.Italic) '~~> Setting Font, Font Size and Italicizing

        '~~> Formatting Y Axis
        CArea.AxisY.MajorGrid.Enabled = False   '~~> Removed the Y axis major grids
        CArea.AxisY.LabelStyle.Format = "0.00%" '~~> Formatting Y axis to display values in %age
        CArea.AxisY.Interval = 0.2              '~~> Formatting Y axis Interval to 20%

        ''''''''''''''''''''''''''''
        '~~> WORKING WITH TITLE <~~'
        ''''''''''''''''''''''''''''
        '~~> Adding a Title
        Dim T As Title = Chart1.Titles.Add("Sales Report Fruits - Year 2010")
        '~~> Formatting the Title
        With T
            .ForeColor = Color.Black            '~~> Changing the Fore Color of the Title 
            .BackColor = Color.Coral            '~~> Changing the Back Color of the Title 

            '~~> Setting Font, Font Size and Bold/Italicizing
            .Font = New System.Drawing.Font("Times New Roman", 11.0F, System.Drawing.FontStyle.Bold)
            .Font = New System.Drawing.Font("Times New Roman", 11.0F, System.Drawing.FontStyle.Underline)
            .BorderColor = Color.Black          '~~> Changing the Border Color of the Title 
            .BorderDashStyle = ChartDashStyle.DashDotDot '~~> Changing the Border Dash Style of the Title 
        End With

        '''''''''''''''''''''''''''''
        '~~> WORKING WITH SERIES <~~'
        '''''''''''''''''''''''''''''
        Dim Series1 As Series = Chart1.Series("Series1")
        '~~> Setting the series Name
        Series1.Name = "Sales"
        '~~> Assigning values to X and Y Axis
        Chart1.Series(Series1.Name).XValueMember = "nFruits"
        Chart1.Series(Series1.Name).YValueMembers = "nSales"
        '~~> Setting Font, Font Size and Bold
        Chart1.Series(Series1.Name).Font = New System.Drawing.Font("Times New Roman", 11.0F, System.Drawing.FontStyle.Bold)
        '~~> Setting Value Type
        Chart1.Series(Series1.Name).YValueType = ChartValueType.Double
        '~~> Setting the Chart Type for Display 
        Chart1.Series(Series1.Name).ChartType = SeriesChartType.Radar
        '~~> Display Data Labels
        Chart1.Series(Series1.Name).IsValueShownAsLabel = True
        '~~> Setting label's Fore Color
        Chart1.Series(Series1.Name).LabelForeColor = Color.DarkGreen
        '~~> Setting label's Format to %age
        Chart1.Series(Series1.Name).LabelFormat = "0.00%"

        '''''''''''''''''''''''''''''
        '~~> WORKING WITH LEGEND <~~'
        '''''''''''''''''''''''''''''
        Dim LG As Legend = Chart1.Legends(0)
        '~~> Changing the Back Color of the Legend 
        LG.BackColor = Color.Wheat
        '~~> Changing the Fore Color of the Legend
        LG.ForeColor = Color.DarkSlateBlue
        '~~> Setting Font, Font Size and Bold
        LG.Font = New System.Drawing.Font("Times New Roman", 11.0F, System.Drawing.FontStyle.Bold)
        '~~> Assigning a title for the legend
        LG.Title = "Legend"

        '~~> Setting the location for the chart
        Chart1.Size = New System.Drawing.Size(780, 350)
    End Sub
End Class

When you run the above code you will see the old chart now transforms into a much more meaningful chart.

Similarly we can create other charts like Bar Charts, Pie Charts, Line Charts, Area Charts etc. All you need to do is change the line in the code above.

Chart1.Series(Series1.Name).ChartType = SeriesChartType.Column

Few Examples


Line Chart
Chart1.Series(Series1.Name).ChartType = SeriesChartType.Line

Pie Chart
Chart1.Series(Series1.Name).ChartType = SeriesChartType.Pie

Funnel Chart
Chart1.Series(Series1.Name).ChartType = SeriesChartType.Funnel

Radar Chart
Chart1.Series(Series1.Name).ChartType = SeriesChartType.Radar
  1. Rafael Almeida
    Rafael Almeida04-06-2012

    Thanks for this tutorial.
    It worked like a charm!!!

  2. señor fa
    señor fa04-15-2012

    excelente busque muchas veces en internet pero un tuto como el tuyo es lo mejor para los que apenas comoenzamos con vb 2010 saludos u muchas gracias dios te bendiga

  3. legna
    legna05-21-2012

    Thanks!!! Great tutorial.

  4. Peter Williams
    Peter Williams06-15-2012

    This is an excellent piece of code! This give you a “template” for developing further.

  5. Arnaldo pinheiro
    Arnaldo pinheiro07-02-2012

    Thanks for this magnificent tutorial!
    For a newbie like me this kind of details helps a lot.

  6. Mukesh Chalodiya
    Mukesh Chalodiya07-18-2012

    this code is not display the chart . I used sql server database. there is no error but it wil not display chart.Please upload code with sql database.
    Thanks in advance.

    • Siddharth Rout
      Siddharth Rout07-18-2012

      Mukesh, I would suggest asking such questions in a forums like MSDN/VBForums.com/Stackoverflow. You can then post the link here. If I can help you then I definitely will. If you want specific code to be developed for you then consider sending me an email with the exact requirements + Budget that we are looking at :)

  7. zafi
    zafi09-23-2012

    Hi Siddharth,

    I am trying to run the codes that you have implemented. It works without error! (Great!)
    However, I can’t see the percentage appears correctly. It can be noted it appears as 4000% instead of 40 %.
    And I unable to to values and labels on Y But works fine on X. Can you advise me pertaining this matter please?

    I really appreciate your help. Thank you

    • Siddharth Rout
      Siddharth Rout09-23-2012

      Where and how are the values stored?

  8. jeff
    jeff09-23-2012

    Hi siddharth,

    A very informative information. I would like to get your advise. Is it possible to hide the legend from appears in the chart? Just would like to show the graph and title only.
    I hope to hearing from you soon.

    • Siddharth Rout
      Siddharth Rout09-23-2012

      Hello Jeff

      You can remove the legends by using

      Chart1.Legends.Clear()

  9. jeff
    jeff09-23-2012

    Hello Siddharth,

    Tq so much for prompt answers! :) I really appreciate it.

  10. akuya
    akuya09-28-2012

    Hi

    Your guide on codes are really helpful. Would like some opinion from you. I am using chart-pie. Like in your code you are using wheat back color. In visual studio 2010, there is an option to choose color like “control color” . Is that possible to that for this? The same color like a standard windows back color?

    I would really appreciate if you can guide me on this please? Thank you sir

    • Siddharth Rout
      Siddharth Rout09-28-2012

      Did you try using the ColorDialog1?

  11. jeff
    jeff10-01-2012

    Hi siddharth,

    I am using the codes that you provided. I would like to make a pie chart

    1. show percentage outside the circle/pie chart with percentage
    2. Location of pie chart and legend in parallel
    3.color scheme of pie chart – one color for example blue. but to make it different I can differentiate it using like dark blue, navy and blue (example)

    I have tried to edit the codes but I still unable to figure out ways to achieve my aims. I really appreciate if you as the author can help me. I really appreciate your help.

    • Siddharth Rout
      Siddharth Rout10-02-2012

      Hello Jeff

      Sure I can help you out. But first I need to see your exact code. You may ask a question in any of the below forums and send me the link and I will help you in that thread if I can.

      http://stackoverflow.com
      http://www.vbforums.com
      http://social.msdn.microsoft.com/Forums

      The reason why I request this is because there will be lot of exchange of views and this place is not apt for it as it will clutter the article :) To post in any of those forums you have to first register as a new user (if you already are not a user) and then ask your query. I will wait for your link.

  12. Venny
    Venny09-21-2013

    Thank you, this helped me very much. But chart titles typed in serif face look horrible. It annoys my eyes!

  13. mz
    mz11-25-2013

    how can i create a graph which is by week, by month and by year?

  14. cristian
    cristian12-13-2013

    man, how do you make two axis y ? and also charge data in axis y

Leave a Reply