[Solved] ASP.Net Chart not showing any Data in VB.Net

elvisidrizi1
 
on Jul 21, 2020 11:22 PM
978 Views

Hello Everyone,

I am working with data charts in vb.net and I found this code as a reference to work with my code: Create Multi-Series Line Chart (Graph) in Windows Forms Application using C# and VB.Net 

I don't have any error what so ever, it just won't show my data in the chart

I modified lit bit so it fits my needs, this is the code that I have done till now :

    Private Sub DataCharts()
        'Fetch the Statistical data from database.
        'Dim query As String

        Dim dt As DataTable = GetData("Chart_Monthly1")

        'Get the DISTINCT Countries.
        Dim countries As List(Of String) = (From p In dt.AsEnumerable()
                                            Select p.Field(Of String)("Department")).Distinct().ToList()

        'Remove the Default Series.
        If Chart1.Series.Count() = 1 Then
            Chart1.Series.Remove(Chart1.Series(0))
        End If

        For Each country As String In countries

            'Get the Year for each Country.
            Dim x As Integer() = (From p In dt.AsEnumerable()
                                  Where p.Field(Of String)("Department") = country
                                  Order By p.Field(Of Integer)("year")
                                  Select p.Field(Of Integer)("year")).ToArray _
            'Get the Total of Orders for each Country.
            Dim y As Integer() = (From p In dt.AsEnumerable()
                                  Where p.Field(Of String)("Department") = country
                                  Order By p.Field(Of Integer)("year")
                                  Select p.Field(Of Integer)("Total")).ToArray() _

            'Add Series to the Chart.
            Chart1.Series.Add(New Series(country))
            Chart1.Series(country).IsValueShownAsLabel = True
            Chart1.Series(country).BorderWidth = 3
            Chart1.Series(country).ChartType = SeriesChartType.Spline
            Chart1.Series(country).Points.DataBindXY(x, y)
        Next
        Chart1.Legends(0).Enabled = True
    End Sub

    Private Function GetData(ByVal query As String) As DataTable
        Dim dt As DataTable = New DataTable()

        Dim constr As String = "Data Source=####/SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
        Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("db").ConnectionString)
            Using command As New SqlCommand("Chart_Monthly1", con)

                command.CommandType = CommandType.StoredProcedure
                con.Open()
                command.Parameters.AddWithValue("@DateFrom", (TextBox1.Text))
                command.Parameters.AddWithValue("@DateTo", (TextBox2.Text))
                Dim reader As SqlDataReader = command.ExecuteReader()

                dt.Load(reader)
                command.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
        Return dt
    End Function

and this is my SQL store procedure form :

Alter Procedure Chart_Monthly1
(
   @DateFrom VARCHAR(50) = NULL
   ,@DateTo  VARCHAR(50) = NULL
)
as
Begin
    SELECT   SUM(Price) AS 'Total'
         ,CONVERT(char(7), date, 120) AS 'year'
		 ,Department as 'Department' 
     FROM     [dbo].[Expenses]
     WHERE    Department IN ('Electronics' ,'Grocery','Other','Vacation','FixCost','Clothes')
     GROUP BY CONVERT(char(7), date, 120), Department  
     HAVING   CONVERT(char(7), date, 120) >= @DateFrom And CONVERT(char(7), date, 120) <= @DateTo
 End

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 23, 2020 12:21 AM

Hi elvisidrizi1,

First modify the procedure to filter record based on From and To date.

Alter Procedure Chart_Monthly1
( 
    @DateFrom VARCHAR(50) = NULL
   ,@DateTo  VARCHAR(50) = NULL
)
as 
Begin
	SELECT SUM(Price) AS 'Total'
         ,CONVERT(char(7), date, 120) AS 'year'
         ,Department as 'Department'
    FROM     [dbo].[Expenses]
    WHERE    Department IN ('Electronics' ,'Grocery','Other','Vacation','FixCost','Clothes')
	AND CONVERT(char(10), date, 120) >= @DateFrom And CONVERT(char(10), date, 120) <= @DateTo
    GROUP BY CONVERT(char(7), date, 120), Department 
End

Then modify the code for typecasting to proper datatype..

Year returns string, so you need to typecast to String.

Total returns Decimal, so you need to typecast to Decimal.

Refer below modified code.

Private Sub DataCharts()
    Dim dt As DataTable = GetData("Chart_Monthly1")
    Dim departments As List(Of String) = (From p In dt.AsEnumerable()
                                            Select p.Field(Of String)("Department")).Distinct().ToList()
    If Chart1.Series.Count() = 1 Then
        Chart1.Series.Remove(Chart1.Series(0))
    End If

    For Each department As String In departments
        Dim x As String() = (From p In dt.AsEnumerable()
                                Where p.Field(Of String)("Department") = department
                                Order By p.Field(Of String)("year")
                                Select p.Field(Of String)("year")).ToArray

        Dim y As Decimal() = (From p In dt.AsEnumerable()
                                Where p.Field(Of String)("Department") = department
                                Order By p.Field(Of String)("year")
                                Select p.Field(Of Decimal)("Total")).ToArray()

        Chart1.Series.Add(New Series(department))
        Chart1.Series(department).IsValueShownAsLabel = True
        Chart1.Series(department).BorderWidth = 3
        Chart1.Series(department).ChartType = SeriesChartType.Spline
        Chart1.Series(department).Points.DataBindXY(x, y)
    Next
    Chart1.Legends(0).Enabled = True
End Sub
Private Function GetData(ByVal query As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Dim constr As String = "Data Source=####/SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
    Using con As SqlConnection = New SqlConnection(constr)
        Using command As New SqlCommand(query, con)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.AddWithValue("@DateFrom", TextBox1.Text)
            command.Parameters.AddWithValue("@DateTo", TextBox2.Text)
            Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                sda.Fill(dt)
            End Using
        End Using
    End Using

    Return dt
End Function