ASE Home Page Products Download Purchase Support About ASE
ChartDirector Support
 Forum HomeForum Home   SearchSearch

Message ListMessage List     Post MessagePost Message

  Display datetime from SQL query searching result as XAxis
Posted by cici on Apr-08-2015 04:08
| Reply | Monitor
I have meet a problem when setup the searching result from SQL as XAxis in VB.NET
WinForm.
Error Msg is : Input string was not in a correct format.

FROMTIME = DateTime.Now.AddHours(-2).ToString("yyyy-MM-dd HH:mm:ss")
TOTIME = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim connectionString As String = "Data Source=xxx.xx.xx.xxx;Initial Catalog=xxx;User
ID=sa;Password=system;"
Dim Sql As String = "SELECT TIME,WEIGHT FROM MIXER2 WHERE TIME BETWEEN'" &
FROMTIME & "' AND '" & TOTIME & "' ORDER BY TIME ASC"
        Dim connection As New SqlConnection(connectionString)
        Dim dataadapter As New SqlDataAdapter(Sql, connection)
        Dim ds As New System.Data.DataSet()
        Dim cmd As New SqlCommand
        Try
            connection.Open()
            dataadapter.Fill(ds, MIXER2)
            DataGridView1.Refresh()
            cmd = New SqlCommand(Sql, connection)
            cmd.ExecuteNonQuery()
            DataGridView1.DataSource = ds.Tables(0)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        ' Read the data into the DBTable object
        Dim table As DBTable = New DBTable(cmd.ExecuteReader())
        connection.Close()

        ' Get the data as arrays
        Dim TIME() As Double = table.getCol(0)
        Dim WEIGHT() As Double = table.getCol(1)

        '
        ' Now we have read data into arrays, we can draw the chart using ChartDirector
        '

        ' Create a XYChart object of size 600 x 300 pixels, with a light grey (eeeeee)
        ' background, black border, 1 pixel 3D border effect and rounded corners.
        Dim c As XYChart = New XYChart(600, 300, &HEEEEEE, &H0, 1)
        c.setRoundedFrame()

        ' Set the plotarea at (60, 60) and of size 520 x 200 pixels. Set background color
        ' to white (ffffff) and border and grid colors to grey (cccccc)
        c.setPlotArea(60, 60, 520, 200, &HFFFFFF, -1, &HCCCCCC, &HCCCCCCC)

        ' Add a title to the chart using 15pts Times Bold Italic font, with a light blue
        ' (ccccff) background and with glass lighting effects.
        c.addTitle("PVC Mixer Monitoring Chart", _
            "Times New Roman Bold Italic", 15).setBackground(&HCCCCFF, &H0, _
            ChartDirector.Chart.glassEffect())

        ' Add a legend box at (70, 32) (top of the plotarea) with 9pts Arial Bold font
        c.addLegend(70, 32, False, "Arial Bold",
9).setBackground(ChartDirector.Chart.Transparent)

        ' Add a stacked bar chart layer using the supplied data
        Dim layer As LineLayer = c.addLineLayer2(ChartDirector.Chart.Stack)
        layer.addDataSet(WEIGHT, &HFF0000, ScaleName.Text &
"Scale").setDataSymbol( _
        ChartDirector.Chart.CircleShape, 9)
        'layer.addDataSet(WEIGHT, &HFF00, "Weight").setDataSymbol( _
        'ChartDirector.Chart.DiamondShape, 11)

        ' Use soft lighting effect with light direction from the left
        layer.setBorderColor(ChartDirector.Chart.Transparent,
ChartDirector.Chart.softLighting(ChartDirector.Chart.Left))

        ' Set the x axis labels.
        Dim labels1() As String = table.getColAsString(0)
        c.xAxis().setLabels(labels1)

        ' Draw the ticks between label positions (instead of at label positions)
        c.xAxis().setTickOffset(0.5)

        ' Set the x axis title
        c.xAxis().setTitle("Time")
        ' Set the y axis title
        c.yAxis().setTitle("Weight")

        ' Set axes width to 2 pixels
        c.xAxis().setWidth(2)
        c.yAxis().setWidth(2)

        ' Output the chart in PNG format
        winChartViewer1.Chart = c

        ' Include tool tip for the chart
        winChartViewer1.ImageMap = c.getHTMLImageMap("", "", _
            "title='{dataSetName} Revenue for {xLabel} = USD {value}M'")
    End Sub

I want to set time as XAxis and weight as YAxis. Please help me! Thanks

CiCi

  Re: Display datetime from SQL query searching result as XAxis
Posted by cici on Apr-08-2015 20:46
| Reply | Monitor
I already fixed the problem!
Changed these two :
  Dim TIME() As String = table.getColAsString(0)
  c.xAxis().setLabels(TIME)

Thanks
CiCi