none
Chart connection with database RRS feed

  • Question

  • Hi everybody

      I wanted to connect a chart to SQL Server database but the chart did not display any value.

    following is the code I have used. Please help me in solving this problem.

    Thanks in advance

      cmd = New SqlCommand("Select Region, SiteCategory from SSG where Region = 'Central' and SiteCategory = 'Mountainous'", cn)
            dt = New DataTable

            Dim dr As SqlDataReader = cmd.ExecuteReader
            While dr.Read
                Chart1.Series("Mountainous").Points.AddXY(dr("Region").ToString, dr("SiteCategory").ToString)
            End While
            dr.Close()
            cmd.Dispose()

            cmd = New SqlCommand("Select Region, SiteCategory from SSG where Region = 'North' and SiteCategory = 'Mountainous'", cn)
            dr = cmd.ExecuteReader
            While dr.Read
                Chart1.Series("Mountainous").Points.AddXY(dr("Region").ToString, dr("SiteCategory").ToString)
            End While

            cmd = New SqlCommand("Select Region, SiteCategory from SSG where Region = 'West' and  SiteCategory = 'Mountainous'", cn)
            'dr = cmd.ExecuteReader
            While dr.Read
                Chart1.Series("Mountainous").Points.AddXY(dr("Region").ToString, dr("SiteCategory").ToString)
            End While

            cmd = New SqlCommand("Select Region, SiteCategory from SSG where Region = 'East' and SiteCategory = 'Mountainous'", cn)
            'dr = cmd.ExecuteReader
            While dr.Read
                Chart1.Series("Mountainous").Points.AddXY(dr("Region").ToString, dr("SiteCategory").ToString)
            End While

            cmd = New SqlCommand("Select Region, SiteCategory from SSG where Region = 'South' and SiteCategory = 'Mountainous'", cn)
            'dr = cmd.ExecuteReader
            While dr.Read
                Chart1.Series("Mountainous").Points.AddXY(dr("Region").ToString, dr("SiteCategory").ToString)
            End While


    Nadem

    Tuesday, August 29, 2017 10:01 AM

All replies

  • Mir,

    Is this all the code?

    Where do you add the Series to the chart? ie:

    Chart1.Series.Add("Series Name")

    You should get an error if the series is not added. Do you get an error?

    Have you checked the values you get from your SQL?

    You need to pin point some details of what is happening. What you have tried already?

    You have two tasks. 1. Draw a chart. 2. Get the data from sql.

    First get the chart to draw using simple sample data you create, not the sql data. ie just make an array similar to what your data is and get the chart to work. Then use the sql part to put the real data in the chart.

    Tuesday, August 29, 2017 12:20 PM
  • Hi Nadem,

    According to your code, can you tell me that you have add one Mountainous series in your chart1 control? I do one sample that showing chart control from sql database, please refer to:

     Private Sub fun()
                   Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Data3.mdf;Integrated Security=True;Connect Timeout=30"
            Dim conn As New SqlConnection(str)
            conn.Open()
            Dim sql As String = "select count(Location) as LocationCount, Location as LocationName from Test11 group by Location"
            Dim cmd As New SqlCommand(sql, conn)
            'Dim adapter As New SqlDataAdapter(cmd)
            'adapter.Fill(ds, "TestTable")
           
            Dim dr As SqlDataReader = cmd.ExecuteReader
            While dr.Read
                Chart1.Series("Series1").Points.AddXY(dr("LocationName").ToString(), dr("LocationCount"))
            End While
           
    
        End Sub
    

    You can also use the another method to show data, binding the chart's datasource

     Private Sub fun()
            Dim ds As New DataSet
            Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Data3.mdf;Integrated Security=True;Connect Timeout=30"
            Dim conn As New SqlConnection(str)
            conn.Open()
            Dim sql As String = "select count(Location) as LocationCount, Location as LocationName from Test11 group by Location"
            Dim cmd As New SqlCommand(sql, conn)
            Dim adapter As New SqlDataAdapter(cmd)
            adapter.Fill(ds, "TestTable")
            conn.Close()
    
            'Dim dr As SqlDataReader = cmd.ExecuteReader
            'While dr.Read
            '    Chart1.Series("Series1").Points.AddXY(dr("LocationName").ToString(), dr("LocationCount"))
            'End While
            Chart1.DataSource = ds.Tables("TestTable")
            Dim Series1 As Series = Chart1.Series("Series1")
            Series1.Name = "TestTable"
            Chart1.Series(Series1.Name).XValueMember = "LocationName"
            Chart1.Series(Series1.Name).YValueMembers = "LocationCount"
    
            'Chart1.Size = New System.Drawing.Size(780, 350)
    
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 30, 2017 3:26 AM
    Moderator