使用日期时间选择器的Visual Basic查询数据库 [英] Visual Basic query database using date time picker

问题描述

Hay家伙对此代码存在很大问题，我试图查询数据库并返回所有通过日期时间选择器输入日期的记录。无论我改变和做什么（搜索灵魂数小时）似乎没有任何作用，并继续在标准表达式中获得数据类型不匹配。

Hay guys having a big issue with this code, im trying to query a database and return all records which have the date inputted through the date time picker. No matter what I change and do (searched for a soultion for hours) nothing seems to work and keep getting Data type mismatch in criteria expression.

Imports System.Data.OleDb

Public Class ViewBookings
    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDbDataAdapter
    Dim sql_1 As String
    Dim sql_2 As String

Private Sub btnDate_Click(sender As Object, e As EventArgs) Handles btnDate.Click
        Dim editdate As date 
        dtpView.Value = Format(dtpView.Value.Date, "dd/MM/yyyy")
        editdate = dtpView.Value
        dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"

        dbSource = "Data Source =" & Application.StartupPath & "/Driving.accdb"

        con.ConnectionString = dbProvider & dbSource
        con.Open()


        sql_2 = "SELECT * FROM tblBookings WHERE LessonDate = '#" & editdate & "#' ORDER BY LessonDate DESC"

        da = New OleDbDataAdapter(sql_2, con)

        da.Fill(ds)

        dgBookings.DataSource = ds.Tables("query2")
        con.Close()
    End Sub
End Class

推荐答案

使用参数化查询 [ ^ ]：

Use parametrized query[^]:

sql_2 = "PARAMETERS [editdate] DATE; SELECT * FROM tblBookings WHERE LessonDate = #[editdate]# ORDER BY LessonDate DESC;"

怎么用？

你需要使用 OledbCommand [ ^ ]与参数 [ ^ ]。按照示例代码的链接。



注意 OledbCommand 通常使用无命名的参数，但MS Access数据库可以使用命名参数。

How to use it?
You need to use OledbCommand[^] with parameters[^]. Follow the links for sample code.

Note that OledbCommand usually uses no-named parameters, but MS Access database can use named parameters.

如评论中所述，您将要删除where子句中日期周围的单引号。



Access使用#s的约会日期，但SQL将使用单引号，但不能同时使用两者。

As discussed in the comments, you'll want to remove the single quotes from around the date in the where clause.

Access uses #'s around dates but SQL will use single quotes but not both at the same time.

这篇关于使用日期时间选择器的Visual Basic查询数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！