CODEWITHSUNDEEP
databasevb.netvisual-studiodatetimems-access
user14823575
user14823575

Reputation:

Is there a way to change the formatting of a New DateTime (VB.NET, Access)

I am utilizing an Access database to store recorded data. Currently, I am returning 0, but I know for the variable in the sub below there are approximately five occurrences. I am using the sub below to return the count of the occurrences of certain records in a certain month:

Public Sub CountAddedDIM() Handles MyBase.Load
        '***EDIT THESE DATES, DIFFERENT FORMATTING POSSIBLE?***
        Dim logDates() As DateTime = {New DateTime(1, 1, 2021),
                                      New DateTime(2, 1, 2021),
                                      New DateTime(3, 1, 2021),
                                      New DateTime(4, 1, 2021),
                                      New DateTime(5, 1, 2021),
                                      New DateTime(6, 1, 2021),
                                      New DateTime(7, 1, 2021),
                                      New DateTime(8, 1, 2021),
                                      New DateTime(9, 1, 2021),
                                      New DateTime(10, 1, 2021),
                                      New DateTime(11, 1, 2021),
                                      New DateTime(12, 1, 2021)}

        ' set 0,0
        PrintsAndShopCHRT.Series("Added Missing Dimension").Points.AddXY(0, 0)

        Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb")
            connection.Open()

            For counter As Integer = 0 To logDates.Count - 1
                Dim sql = $"SELECT COUNT(*) FROM DataCollection WHERE [ADDED MISSING DIMENSION] = 'Added Missing Dimension' AND [M/Y OF LOG] = @MY_OF_LOG;"
                Using Command As New OleDbCommand(sql, connection)
                    Command.Parameters.Add("@MY_OF_LOG", OleDbType.Date).Value = logDates(counter)
                    Dim returnValue = Command.ExecuteScalar()

                    Dim count As Integer = Integer.Parse(returnValue.ToString)

                    ' other points
                    PrintsAndShopCHRT.Series("Added Missing Dimension").Points.AddXY(counter + 1, count)
                End Using
            Next
        End Using

        ' INSERT CHART SETTINGS
        PrintsAndShopCHRT.ChartAreas(0).AxisX.Minimum = 0.0
        PrintsAndShopCHRT.ChartAreas(0).AxisX.Maximum = 12
        PrintsAndShopCHRT.ChartAreas(0).AxisX.Interval = 1
        PrintsAndShopCHRT.ChartAreas(0).AxisY.Minimum = 0.0
        PrintsAndShopCHRT.ChartAreas(0).AxisY.Maximum = 45
        PrintsAndShopCHRT.ChartAreas(0).AxisY.Interval = 5
    End Sub

When using the New Datetime formatting as I did, I get this error:

System.ArgumentOutOfRangeException: 'Year, Month, and Day parameters describe an un-representable DateTime.'

I understand that DateTimes are normally formatted as "YYYY, MM, dd", but is there any way it could be formatted like the sub above shows? My database column that uses the month/day/year information uses the formatting mm/dd/yyyy.

Thanks for the help.

Upvotes: 0

Views: 66

Answers (3)

Mary
Mary

Reputation: 15091

As you can see if you followed @dbasnett link. The constructor for a DataTime takes (year, month, day). You put the year in the day slot so of course it was out or range for a day that should be 1 - 31.

I also wanted to point out that you should create the command and the parameters collection once, outside the loop. The only thing that changes is the value of the parameter inside the For loop. You don't need to .Parse the returned value. We know that Count always returns an Integer (except MySql where it returns a Long) so we can just convert it with CInt.

The only reason I used a list of DataPoint is I wasn't sure how to manipulate the Series in a For loop. I wanted to show what was returned. .AddXY can take an array of DataPoint. If you get an error here just change to

Chart1.Series("Added Missing Dimension").Points.AddXY(ChartPoints.ToArray)

Code not tested.

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim logDates() As DateTime = {New DateTime(2021, 1, 1),
                                  New DateTime(2021, 2, 1),
                                  New DateTime(2021, 3, 1),
                                  New DateTime(2021, 4, 1),
                                  New DateTime(2021, 5, 1),
                                  New DateTime(2021, 6, 1),
                                  New DateTime(2021, 7, 1),
                                  New DateTime(2021, 8, 1),
                                  New DateTime(2021, 9, 1),
                                  New DateTime(2021, 10, 1),
                                  New DateTime(2021, 11, 1),
                                  New DateTime(2021, 12, 1)}
    Dim sql = $"SELECT COUNT(*) FROM DataCollection WHERE [ADDED MISSING DIMENSION] = 'Added Missing Dimension' AND [M/Y OF LOG] = @MY_OF_LOG;"
    Dim ChartPoints As New List(Of DataPoint)
    Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb"),
            Command As New OleDbCommand(sql, connection)
        Command.Parameters.Add("@MY_OF_LOG", OleDbType.Date)
        connection.Open()
        For counter As Integer = 0 To logDates.Count - 1
            Command.Parameters("@MY_OF_LOG").Value = logDates(counter)
            Dim count = CInt(Command.ExecuteScalar())
            ChartPoints.Add(New DataPoint(counter + 1, count))
        Next
    End Using
    'To see if you are getting the data you expect
    For Each p As DataPoint In ChartPoints
        Debug.Print($"{p.XValue}, {p.YValues}")
    Next
    Chart1.Series("Added Missing Dimension").Points.AddXY(ChartPoints)
End Sub

Upvotes: 0

Xingyu Zhao
Xingyu Zhao

Reputation: 625

You can create a extension method.

Module DateTimeCustomFormatExtensions
<Extension()>
Function ToCustomFormat(ByVal dt As DateTime) As DateTime
    Dim culture = System.Globalization.CultureInfo.CurrentCulture
    Return DateTime.ParseExact(dt.ToString("MM/dd/yyyy"), "MM/dd/yyyy", culture)
End Function
End Module

The code looks like:

Dim logDates() As DateTime = {New DateTime(2021, 1, 1).ToCustomFormat(),
                                  New DateTime(2021, 2, 1).ToCustomFormat(),
                                  ...}

Upvotes: 0

dbasnett
dbasnett

Reputation: 11773

The constructor for DateTime has many overloads. The one you apparently are using is year, month, day. See

DateTime constructor

Upvotes: 1

Related Questions