是否有方法更改新日期时间(VB.NET，Access)的格式

我正在使用Access数据库来存储记录的数据。目前，我返回0，但我知道下面的子中的变量大约有五次出现。我正在使用下面的子项返回某个月内某些记录的出现次数：

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

当我使用新日期时间格式时，我得到了这个错误：

System.ArgumentOutOfRangeException：'年、月和日参数描述一个不可表示的DateTime

我知道DateTimes通常被格式化为"；YYYY，MM，dd"；，但是有没有什么办法可以像上面显示的那样格式化它呢？我的数据库列使用月/日/年信息，格式为mm/dd/yyyy。

谢谢你的帮助。

DateTime的构造函数有许多重载。你显然使用的是年、月、日。参见

DateTime构造函数

您可以创建一个扩展方法。

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

代码看起来像：

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

如您所见，如果您关注@dbasnett链接。DataTime的构造函数采用(year, month, day)。你把年份放在一天的时段里，所以它当然超出了应该是1-31的一天的范围。

我还想指出，您应该在循环之外创建一次命令和参数集合。唯一更改的是For循环中的参数值。您不需要.Parse返回的值。我们知道Count总是返回Integer(除了MySql返回Long(，所以我们可以用CInt转换它。

我使用DataPoint列表的唯一原因是我不确定如何在For循环中操作Series。我想展示归还的东西。CCD_ 12可以采用CCD_。如果你在这里遇到错误，只需更改为

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

未测试代码。

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

相关内容

最新更新

热门标签：