如何使用vb.net 2010从access 2007数据库中检索datetime（仅限日期类型）？

My requirement is to let user pick a date from DateTimePicker and a GataGridView will then retrieves and shows the info(s) where match to the value from DateTimePicker.

我的要求是让用户从DateTimePicker中选择一个日期,然后GataGridView将检索并显示与DateTimePicker中的值匹配的信息。

 Private Sub DateTimePicker3_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker3.ValueChanged
    dailyloadglog()
End Sub

.

Public Sub dailyloadglog()
    Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & aaa & "';Persist Security Info=True;")
    Try
        Dim OOL As String = "SELECT Glogtbl.DIN, Glogtbl.clock, Int([clock]) AS JustDate, [clock]-Int([clock]) AS JustTime FROM Glogtbl WHERE JustDate = " & DateTimePicker3.Value.ToShortDateString & ""
        Dim cmd As New OleDbCommand
        Dim odpt As New OleDbDataAdapter
        Dim tbl As New DataTable
        With cmd
            .CommandText = OOL
            .Connection = connection
        End With
        With odpt
            .SelectCommand = cmd
            .Fill(tbl)
        End With
        DataGridView4.Rows.Clear()
        For i = 0 To tbl.Rows.Count - 1
            With DataGridView4
                .Rows.Add(tbl.Rows(i)("DIN"), tbl.Rows(i)("clock"), tbl.Rows(i)("JustDate"), tbl.Rows(i)("JustTime"))
            End With
        Next
        connection.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

ex.exception gave me this:

ex.exception给了我这个:

When i try to retrieve all the date/time column and the result shows me:

当我尝试检索所有日期/时间列时,结果显示我:

 Dim OOL As String = "SELECT Glogtbl.DIN, Glogtbl.clock, Int([clock]) AS JustDate, [clock]-Int([clock]) AS JustTime FROM Glogtbl"

The JustDate value still remain format long datetime value. therefore the values going to do comparison with datetimepicker.value become always wrong. End up with retrieve nothing!!!

JustDate值仍然是格式长日期时间值。因此,与datetimepicker.value进行比较的值总是错误的。最终找不到任何东西!!!

Who can guide me on this?!

谁可以指导我这个?!

1 个解决方案

#1

Don't use string concatenation to insert values into SQL code. Always use parameters. Doing so will avoid issues like incorrect delimiters or formatting, both of which you are experiencing. E.g.

不要使用字符串连接将值插入SQL代码中。始终使用参数。这样做可以避免您遇到的错误分隔符或格式设置等问题。例如。

myCommand.CommandText = "SELECT * FROM MyTable WHERE MyColumn = @MyColumn"
myCommand.Parameters.AddWithValue("@MyColumn", myValue)

There are lots of places that you can find more information on ADO.NET parameters, e.g.

有很多地方可以找到有关ADO.NET参数的更多信息,例如:

http://jmcilhinney.blogspot.com.au/2009/08/using-parameters-in-adonet.html