Creating Graph with VB.NET, Part 3: Data Binding Chart to Database

This entry is part 3 of 3 in the series Creating Graph with VB.NET

From Part 1: Basic Chart and Part 2: Customize Chart, you see how to create a basic chart using GUI (no coding). But the chart is static, it’s lack flexibility which means that you cannot change properties or data of the chart while the application is running. Therefore, I will show how to create a chart by coding and bind data from a SQL Server’s database to the Chart control.On this example, I will create a connection to “Northwind” database on this SQL Server “BKKSQL001\INSTANCE01” and query product name and units in stock from “Products” table. Then, I create a chart and bind the query’s result to the chart.

Step-by-step to implement data binding chart to database

  1. Create a new Windows Application project on VB.NET and type name as “SampleDataBindChart“.
    Create New VB.NET's Windows Application
  2. On Form1, open code window and import these libraries. The first two libraries are used for SQL. The last one is used for Chart.
    Imports System.Data
    Imports System.Data.SqlClient
     
    Imports System.Windows.Forms.DataVisualization.Charting

    Import Required Libraries

  3. Type code below on Form1_Load().
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    
            Dim strConn As String = "Data Source=BKKSQL001\INSTANCE01;" & _
                "Initial Catalog=Northwind;Integrated Security=True"
     
            Dim conn As New SqlConnection(strConn)
     
            Dim sqlProducts As String = "SELECT Top 8 ProductName, UnitsInStock FROM Products"
            Dim da As New SqlDataAdapter(sqlProducts, conn)
            Dim ds As New DataSet()
            da.Fill(ds, "Products")
     
            Dim ChartArea1 As ChartArea = New ChartArea()
            Dim Legend1 As Legend = New Legend()
            Dim Series1 As Series = New Series()
            Dim Chart1 = New Chart()
            Me.Controls.Add(Chart1)
     
            ChartArea1.Name = "ChartArea1"
            Chart1.ChartAreas.Add(ChartArea1)
            Legend1.Name = "Legend1"
            Chart1.Legends.Add(Legend1)
            Chart1.Location = New System.Drawing.Point(13, 13)
            Chart1.Name = "Chart1"
            Series1.ChartArea = "ChartArea1"
            Series1.Legend = "Legend1"
            Series1.Name = "Series1"
            Chart1.Series.Add(Series1)
            Chart1.Size = New System.Drawing.Size(800, 400)
            Chart1.TabIndex = 0
            Chart1.Text = "Chart1"
     
            Chart1.Series("Series1").XValueMember = "ProductName"
            Chart1.Series("Series1").YValueMembers = "UnitsInStock"
     
            Chart1.DataSource = ds.Tables("Products")

    Code Explanation:

    • Line 1-2: Define a connection string to connect to a database on SQL Server.
      • Data Source is a SQL Server name.
      • Initial Catalog is a database name.
      • Set Integrated Security=True to use the current user as identity to access the SQL Server database.
    • Line 4: Create a SqlConnection’s object.
    • Line 6: Define SQL query string.
    • Line 7-9: Execute the query and populate result to DataSet’s object.
    • Line 11-14: Create Chart’s objects.
    • Line 15: Add Chart’s object to the form.
    • Line 17-29: Set Chart’s properties (ChartArea, Legend and Series).
    • Line 31-32: Bind column “ProductName” to X-axis and column “UnitsInStock” to Y-axis on the “Series1” Chart.
    • Line 34: Set Chart’s data source to the DataTable in the DataSet’s object.

    Form1_Load's code

  4. Run the project. You see a chart displaying “Product Name” on X-axis and “Unit in Stock” on Y-axis which data is gathered from Northwind database on SQL Server.
    Data Binding Chart to Database
Series Navigation<< Creating Graph with VB.NET, Part 2: Customize Chart

45 Responses to “Creating Graph with VB.NET, Part 3: Data Binding Chart to Database”

  1. Noob

    Good afternoon,

    I have a problem with my chart. The x axis does not show all the labels.
    I thought it was the size of the chart, but to increase it only shows the font bigger, not the space to display x-axis labels.

    what can i do?

  2. vik

    Good afternoon it hears in your code marks error to me in Me.Controls.Add (Chart1) in Chart1 says to me that A value of type ‘ System.Windows.Forms.DataVisualization.Charting.C hart’ it is not possible to be turned into ‘ System.Web.UI.Co ntrol’. Can you help me?

  3. Kashif Gulzar

    Please send me the answers that u provide to both the above mentioned guys.I have a table with fields Name,Absents and Presents.I wanna show a bar graph with complete Names on x-axis and each name having a red bar for absents and a blue bar for presents.Please help me.

  4. Kashif Gulzar

    You are great man.I have used your code and i have succeeded.Please put on more tutorials on how to draw pie charts and 3D Graphs.Thank you very much.

  5. JetSeP

    Hello, im sad because i cant see how to make the chart show all the names in x axis. it only show 4 names .. please help-me

  6. Mike

    This Works fine.
    I just wanted to ask if there is a way to use line graph instead of bars and which are the properties to do it.
    Also, my title is not displayed. Can you help with this, please?

  7. Mike

    OK, I found, it is not the Chart1, but Series1 property:
    Series1.ChartType = SeriesChartType.Line.

    Thanks a lot for this tutorial!

  8. Dany

    Hi,
    Thank you for the Very good tutorial,
    but m having the same problem with the x axis, The x axis does not show all the labels.
    Can you please help me?
    maybe it has to do with scale or font size!

  9. Sophie

    I have used your tutorial but i have variables in my SQL statement, comoing from a combo box, i want to be able to update the chart is this possible? I have tried update and refresh but the chart remains the same.

  10. Dany

    Hi,

    I fixed my problem. just need to add the following line:
    Chart1.ChartAreas(“ChartArea1″).AxisX.Interval = 1

    Best Regards,
    Dany

  11. mrhendrik

    chart1.series.item(“Series1″.points.item(“Point1″).YValue
    –> how to give value to this point?

    thanks for your code.. is very helpful.. great job..

  12. tingting

    Thanks for your tutorial! but i want to get the data in the microsoft access and then do to input them in a table. this is the problem. i followed your step and change the sql to Oledb..well. looks like it still doesn’t work,how to solve. pls help me !

  13. Oscar

    Great, however, how do you make scatter plots where the value of x is not just 1,2,3… like in the examples, but any number. That is, plots of a series of (x,y)points.

  14. Alex

    Thanks,it works perfectly..
    but i have one problem.
    if i refresh the table,i’m not able to load new chart..
    The chart’s graph remain the same insted change my series..
    How can i reload the chart with new values? (in vb script)..
    There’s such “Char1.Refresh()” comand the do this?
    (I’ve tryed a lot but can’t solve it)..Thank you

  15. xawi

    i add first the chart, is it right? but it doesnt work.. it seems like there’s a mistake with da.Fill(ds, “Products”)..

    pls help plsplspls

  16. Wouter

    Great posting! I’ve been searching endlessly through all forums and even my Visual Basic 2010 Step by Step book apparently missed out an entire chapter about graphs. You explained it very well and I was able to use it all and even build on it to customize it to my own needs.
    I wonder if you ever though about writing another section on how to play with some of the parameters of the charts in vb.net code, such as change a graph to 3D, or modify the labels to show percentages in a pie chart, etc etc.

  17. kalla

    how to display all data point. Suppose chart is display monthly (1-31) then only selected data points are show (0, 5, 10, 15, 20)

  18. Rosemma

    Your tutorial help me to much, but I can’t insert into my chart more than 10 data. When I try to put into it all my 300 data, contained in an arrays of double, it shows me only 2 data. Which setting should I adjust in order to make it consider all my values?
    Sorry to my bad english
    thank you

  19. Nancy Alajarmeh

    For those who got this error:
    An unhandled exception of type ‘System.Data.SqlClient.SqlException’ occurred in System.Data.dll

    You need to make this slight change:
    Use MySqlConnection, MySqlDataAdapter instead of SqlConnection, SqlDataAdapter respectively if you are dealing with MySql Database like in my case.
    Nancy Alajarmeh

  20. marvin

    hi sir. can you help me to make an access database and connect it into vb.net 2010? and once you connect the database of access and automaticaly vb.net will make a graph to it.. please help me.. thx in advance ^_^

  21. Cindy

    This is very helpful, but can you tell me how to use two data sources for my Y-axis? I need series 1 to come from one ds and series 2 to come from another data source. Any assistance will be great.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>