在日期之间搜索 [英] search between dates

问题描述

大家好，

我试图搜索来自datetimepicker的2个日期之间的数据库，但出现此错误(fill:selectcommand.connection属性尚未初始化).我不知道我的代码有什么问题，因为我仍在VB.net苗圃中.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim connstrg As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=M:\REGISTER.mdb;Persist Security Info=True;Jet OLEDB:Database Password=he123;"
            Dim conn As New OleDbConnection(connstrg)
            Dim DA As New OleDbDataAdapter("select* FROM reg", conn)
            Dim ds As New DataSet
            Dim com As New OleDbCommand
            Dim dd1 As Date = Me.DateTimePicker1.Value.Date

            Dim dd2 As Date = Me.DateTimePicker2.Value.Date
            conn.Open()
            com.CommandType = CommandType.Text
            com.CommandText = "select * from reg where [date] BETWEEN  & dd1 &  And  & dd2"
            DA.SelectCommand = com
            DA.Fill(ds, "reg")
            Me.DataGridView1.DataSource = ds.Tables(0)
            conn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
        End Try
    End Sub

解决方案

该行

com.CommandText = "select * from reg where [date] BETWEEN  & dd1 &  And  & dd2"

是错的.应该是

com.CommandText = "select * from reg where [date] BETWEEN #" & dd1.ToString("mm/dd/yyyy") & "# And #" & dd2.ToString("mm/dd/yyyy") & "#"

在查询中，日期必须用#"字符分隔.

更好的方法是使用参数化查询

com.CommandText = "select * from reg where [date] BETWEEN ? And ?"
com.Parameters.AddWithValue("@StartDate", dd1)
com.Parameters.AddWithValue("@EndDate", dd2)

Hi，这样可以省去将日期转换为查询中正确格式的所有麻烦.

您好
我已经添加了
com.Connection = conn

 私有 子 Button1_Click( ByVal 发​​件人 As 系统.对象， ByVal  e  As  System.EventArgs)句柄 Button1.Click
        尝试
             Dim  connstrg  As  字符串 = " & " 
             Dim  conn  As  新建 OleDbConnection(connstrg)
             Dim  DA  As  新 OleDbDataAdapter(" ，conn)
             Dim  ds  As  新建数据集
             Dim  com  As  新 OleDbCommand
             Dim  dd1  As  日期 = 我 .DateTimePicker1.Value.日期

             Dim  dd2  As  日期 = 我 .DateTimePicker2.Value.日期
            conn.Open()
            com.CommandType = CommandType.Text
            com.CommandText = " 
            --------------------------------------------------
            -添加了Imdad
            com.Connection = conn
             - 结尾
            --------------------------------------------------
            DA.SelectCommand = com
            DA.Fill(ds，" )
            我 .DataGridView1.DataSource = ds.Tables( 0 )
            conn.Close()
        捕获，例如 As 异常
            MessageBox.Show(例如消息)
        最后
        结束 尝试
    结束 子 

如有任何疑问，请让我知道.

请提供"投票":thumbsup:如果有帮助，请提供"接受答案"，如果这是正确的答案.:rose:

谢谢，
Imdadhusen

Hi all,

I am trying to search database between 2 dates that come from datetimepicker but I have got this error ( fill:selectcommand.connection property has not been initialized ). I don''t know what is wrong with my code as I am still in the VB.net nursery.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim connstrg As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=M:\REGISTER.mdb;Persist Security Info=True;Jet OLEDB:Database Password=he123;"
            Dim conn As New OleDbConnection(connstrg)
            Dim DA As New OleDbDataAdapter("select* FROM reg", conn)
            Dim ds As New DataSet
            Dim com As New OleDbCommand
            Dim dd1 As Date = Me.DateTimePicker1.Value.Date

            Dim dd2 As Date = Me.DateTimePicker2.Value.Date
            conn.Open()
            com.CommandType = CommandType.Text
            com.CommandText = "select * from reg where [date] BETWEEN  & dd1 &  And  & dd2"
            DA.SelectCommand = com
            DA.Fill(ds, "reg")
            Me.DataGridView1.DataSource = ds.Tables(0)
            conn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
        End Try
    End Sub

解决方案

The line

com.CommandText = "select * from reg where [date] BETWEEN  & dd1 &  And  & dd2"

is wrong. It should be

com.CommandText = "select * from reg where [date] BETWEEN #" & dd1.ToString("mm/dd/yyyy") & "# And #" & dd2.ToString("mm/dd/yyyy") & "#"

Dates need to be delimited with the ''#'' character in queries.

Even better would be to use a parameterised query

com.CommandText = "select * from reg where [date] BETWEEN ? And ?"
com.Parameters.AddWithValue("@StartDate", dd1)
com.Parameters.AddWithValue("@EndDate", dd2)

which would save all the fiddling about with getting the dates into the correct format in the query.

Hi
I have added on line
com.Connection = conn

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim connstrg As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=M:\REGISTER.mdb;Persist Security Info=True;Jet OLEDB:Database Password=he123;"
            Dim conn As New OleDbConnection(connstrg)
            Dim DA As New OleDbDataAdapter("select* FROM reg", conn)
            Dim ds As New DataSet
            Dim com As New OleDbCommand
            Dim dd1 As Date = Me.DateTimePicker1.Value.Date

            Dim dd2 As Date = Me.DateTimePicker2.Value.Date
            conn.Open()
            com.CommandType = CommandType.Text
            com.CommandText = "select * from reg where [date] BETWEEN  & dd1 &  And  & dd2"
            --------------------------------------------------
            --Imdad was added
            com.Connection = conn
            --end 
            --------------------------------------------------
            DA.SelectCommand = com
            DA.Fill(ds, "reg")
            Me.DataGridView1.DataSource = ds.Tables(0)
            conn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
        End Try
    End Sub

Please do let me know, if you have any doubt.

Please provide "Vote":thumbsup: if this would be helpful, and make "Accept Answer" if this would be correct answer.:rose:

Thanks,
Imdadhusen

这篇关于在日期之间搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！