ADO, Recordsets & DateTime

[tlb1971]

I have recently updated a version 5 application. It uses ASP pages and ADO. Here's a snippet of code:

Code: Select all

'DATE/TIME
AChart.Axis.Bottom.Increment = AChart.GetDateTimeStep(dtOneHour)
AChart.Axis.Bottom.Labels.DateTimeFormat = "hh:nn" '****version5 used AChart.Axis.Bottom.Labels.DateTimeFormat = "hh:mm"
		
AChart.AddSeries(scLine)
AChart.Series(0).Color = vbRed
AChart.Series(0).XValues.DateTime = True
AChart.Series(0).Datasource = Rs
AChart.Series(0).YValues.ValueSource="Value"
AChart.Series(0).LabelsSource="DateTime" '**** version5 used Chart.Series(0).XValues.ValueSource="DateTime"

I expect to have the DateTime on the X axis:

1. be divided into 1 hour segments via AChart.Axis.Bottom.Increment = AChart.GetDateTimeStep(dtOneHour)
2. display DateTime format of hours:minutes via AChart.Axis.Bottom.Labels.DateTimeFormat = "hh:nn"

But it does not display that way. Doesn't seem to be any formatting of the Y axis at all. Is there some order that is required? I feel like I've tried everything to no avail. I have the ASP source code as well as the results of the SQL query but the forum does not allow me to upload.

[Yeray]

Hello,

I see you are setting the "DateTime" field to the Series' LabelsSource:

Code: Select all

AChart.Series(0).LabelsSource="DateTime"

As you said, in v5 you used:

Code: Select all

AChart.Series(0).XValues.ValueSource="DateTime"

I'm not sure to understand why did you change this, but this seems to be the reason of the problem.
- Adding values to a series without specifying the X values, the X values array is populated with a succession of 0, 1, 2,... That's why there's the same horizontal distance between all the points in your result.
- By default, the axis Labels.Style is set to talAuto, and when the axis has a series with labels attached, these labels are used to draw the axis labels. The DateTimeFormat only is considered when displaying values in the axis, not when displaying the labels (strings).

[tlb1971]

If I use AChart.Series(0).XValues.ValueSource="DateTime", my chart does not draw. The only way I get my chart to draw is using the AChart.Series(0).LabelsSource="DateTime" method. What am I missing?

I used it because that's what the ADOTeeChart.asp example used:

Code: Select all

'Connect Series to Recordset
if RSt.RecordCount > 0 then 
      Chart.Series(0).Datasource = RSt
      [b]Chart.Series(0).LabelsSource="OrderNo"[/b]
      Chart.Series(0).YValues.ValueSource="ItemTotal"
else
      Chart.Series(0).Fillsamplevalues(10)
      Chart.Header.Text(0)="ADO database returned no values - using random data"
end if

I could not find any examples using DateTime in the Y Axis. Are there any examples using DateTime as Y axis?

Finally, are you saying these functions are no longer supported even though they are in the help file:

Code: Select all

AChart.Axis.Bottom.Automatic = False
AChart.Axis.Bottom.Increment = AChart.GetDateTimeStep(dtOneHour)
AChart.Axis.Bottom.Labels.DateTimeFormat = "hh:nn"

From the help file:

IAxisLabels.DateTimeFormat
IAxisLabels

property DateTimeFormat: WideString;

Type Library
TeeChartx

Description
Chart AxisLabels have a DateTimeFormat property. DateTimeFormat is a standard DateTime formatting string specifier. Chart Axis uses it to draw the axis labels.

What am I missing?

[Marc]

Hello,

Re.

1. be divided into 1 hour segments via AChart.Axis.Bottom.Increment = AChart.GetDateTimeStep(dtOneHour)
2. display DateTime format of hours:minutes via AChart.Axis.Bottom.Labels.DateTimeFormat = "hh:nn"

Yes. Those should work. If those lines are not working then there must be some other factor in the code impeding them.

This image is an example, simple VB demo (adapted from example demos shipped with TeeChart AX 2017):

datetime_example.png (117.22 KiB) Viewed 23212 times

The example uses variably spaced x data at intervals of less than an hour. The asp code should work on this in exactly the same way. I've added the VB(6!) project to this post.

Re.

If I use AChart.Series(0).XValues.ValueSource="DateTime", my chart does not draw. The only way I get my chart to draw is using the AChart.Series(0).LabelsSource="DateTime" method. What am I missing?

"Datetime" is the name of the field, correct?
- If the "Datetime" field contains double values (datetime in double format) then it can be used with AChart.Series(0).XValues.ValueSource
- If the "Datetime" field contains strings then you can use it as the LabelsSource.

Regards,
Marc

[tlb1971]

Sorry for the delay on my response and thanks for the follow-ups so far. My system is using SQL Server 2014. It has a new DateTime type called DateTime2. It appears to have a higher precision than the "old" DateTime. As far as I can tell it will not work with TeeChart in that it won't see it as a DateTime. A workaround I found was to create a table with the old DateTime format and then do a "Insert Into X Select Y" where X is the table with the "old" DateTime type and Y is the table with the DateTime2 type.

[Yeray]

Hello,

A workaround I found was to create a table with the old DateTime format and then do a "Insert Into X Select Y" where X is the table with the "old" DateTime type and Y is the table with the DateTime2 type.

Another workaround would be to loop your table, convert DateTime2 values to DateTime and add the values manually to the series. Just remember to disable AutoRepaint before adding many points, and activate it when you've finished. This is done internally when you connect a datasource to a series, so there shouldn't be any performance decrease.