Multi-Series chart using SQL query

[JerryCRH]

Does anyone have an example of a multi0series chart using a SQL query as a data source?

[JerryCRH]

Here is what I have so far.  The error is "invalid data"

Dim connectionString10 As String
        Dim ds10 As New DataSet
        Dim stringxml10 As New StringBuilder
        Dim stringxml11 As New StringBuilder
        Dim stringxml12 As New StringBuilder

        connectionString10 = "Data Source=server;Initial Catalog=database;User ID=User;Password=password"
        stringxml10.Append("<graph caption='Discrepancies by Drug by Month' logoURL='logo_crh.gif' logoposition='CC' logoalpha='20' logoscale='150' xAxisName = 'Drug' showDivLineSecondaryValue='1' showSecondaryLimits='1'  showValues='0' animate='1' bgcolor='#006600,#FFFDDD' bgratio='0,100' canvasbgAlpha='0' >")
        Using conn10 As New SqlConnection(connectionString10)


            conn10.Open()
            Dim qry10 As New SqlCommand("select distinct(datepart(month,tdate)) TheMonth9 " & _
            "from floorstock_discrepancy " & _
            "where tdate >= getdate()-180 group by drug, datepart(month,tdate)", conn10)
            Dim rst10 As SqlDataReader = qry10.ExecuteReader()
            stringxml10.Append("<categories> ")
            While rst10.Read()
                stringxml10.Append("<category label='{0}'/>", rst10("TheMonth9").ToString()) ' & "'  />")

            End While
            rst10.Close()
            conn10.Close()
            stringxml10.Append("</categories>")


            conn10.Open()
            Dim qry11 As New SqlCommand("select count(drug) DrugCount, drug Drug2, convert(varchar,datename(month,tdate)) TheMonth2 " & _
            "from floorstock_discrepancy " & _
            "where tdate >= getdate()-180 group by drug, convert(varchar,datename(month,tdate))", conn10)
            Dim rst11 As SqlDataReader = qry11.ExecuteReader()
            While rst11.Read()
                stringxml10.AppendFormat("<dataset seriesName='{0}'>", rst11("Drug2").ToString())
                stringxml10.AppendFormat("<set value='{0}'/>", rst11("drugcount").ToString())
                
            End While
            rst11.Close()
            conn10.Close()
            stringxml10.Append("</dataset>")

            stringxml10.Append("<styles>  <definition> <style name='B' type='animation' easing='bounce' duration='1' /> <style name='Bevel1' type='bevel' angle='0' /> <style name='Anim1' type='animation' param='_xScale' start='0' duration='3' />  <style name='Anim2' type='animation' param='_alpha' start='0' duration='3' easing='bounce' />  </definition> <application> <apply toObject='CANVAS' styles='Anim1, Anim2, Bevel1' /> ")
            stringxml10.Append("<apply toObject='DATAPLOT' styles='Anim1, Anim2, Bevel1, B' />")
            stringxml10.Append("</application> </styles>")

            stringxml10.Append("</graph>")


            
            conn10.Close()


            Literal4.Text = FusionCharts.RenderChart("FusionCharts/MSBar2D.swf", "", stringxml10.ToString(), "TheMonth9", "500", "300", _
             False, True)
        End Using

Edited October 13, 2016 by JerryCRH

[Gagan Sikri]

Hi

 

You can check out this example for the same: https://github.com/fusioncharts/vb-net-wrapper/blob/master/Samples/DBExample/index.aspx.vb

 

Please make sure you are creating XML or JSON data according to FusionCharts format. You can use below JSFiddle samples for reference:

- JSON: http://jsfiddle.net/fusioncharts/X9xPM/

- XML: http://jsfiddle.net/sikrigagan/X9xPM/161/

 

Also, according to code you sent earlier. It seems that you are using flash version of FusionCharts, and flash charts are deprecated now. I would suggest you to upgrade to latest version of FusionCharts and FusionCharts VB wrapper.