使用两个datatimepickers和datagridview过滤信息。 [英] To filter information using two datatimepickers and datagridview.

问题描述

大家好，

我一直在努力尝试在两个日期和SQL Server 2012之间进行过滤。

使用sqlCon = Connection（）
 
 Dim dt As New DataTable 
 
 Dim query As String =" select * from Tledlinices，其中saledate> = @ saledateand1 saledate< = @ saledate2 order by saledate DESC" 
 Dim cmd As SqlCommand = New SqlCommand 
 cmd.CommandText = query 
 cmd.Connection = sqlCon 
 cmd.CommandType = CommandType.Text 
 cmd.CommandTimeout = 90 
 
 cmd.Parameters.Add（" @ saledate1"，SqlDbType.Datetime）.Value = dtsaledate1.Text 
 cmd.Parameters.Add（" @ saledate2"，SqlDbType.Datetime）。 Value = dtsaledate2.Text 
 
 Dim da As New SqlDataAdapter 
 da.SelectCommand = cmd 
 
 da.Fill（dt）
 datalistado.DataSource = dt 
 
结束使用
 

当我使用上面的代码时，我只获得datagridview上的日期但由于我在数据库中保存了日期和时间，因此无法显示任何内容。按日期和时间我的意思是这样的  8/11/2008
3:32 PM

是否可以将日期时间从SQL服务器过滤到datagridview，我不想只有日期，我真的需要日期和时间，我真的很感激一些帮助。

问候， 

解决方案

使用BETWEEN例如

 Public Function BetweenTwoDates（ByVal pStartDate As DateTime，pEndDate As DateTime）As DataTable 
 Dim dt As New DataTable 
使用cn作为新的SqlConnection使用{.ConnectionString =" Data Source = KARENS-PC; Initial Catalog = ForumExamples; Integrated Security = True"} 
使用cmd作为新的SqlCommand使用{.Connection = cn} 
 cmd.CommandText =" SELECT id，StartDate，EndDate FROM BetweenDates WHERE StartDate BETWEEN @StartDate AND @ EndDate" 
 cmd.Parameters.AddWithValue（" @ StartDate"，pStartDate）
 cmd.Parameters.AddWithValue（" @ EndDate"，pEndDate）
 cn.Open（）
 dt .Load（cmd.ExecuteReader）
结束使用
结束使用
返回dt 
结束函数

调用（DataOperations in this case是一个包含上面方法的类，你可以直接在你的表单中使用上面的方法，我喜欢使用类）

 Private Sub Button1_Click（ sender As Object，e As EventArgs）处理Button1.Click 
 Dim ops As New DataOperations 
 Dim dt As DataTable = ops.BetweenTwoDates（＃2017-09-01 04：37：42＃，＃2017- 09-04 04：37：42＃）
 End Sub 

我用硬编码的日期，你会使用你的DateTimePickers来代替硬编码值，例如SomeDataTimePicker.Value。

在SQL-Server Management Studio中运行查询。

DataTable中的数据

Hello all,

I've been struggling trying to filter between two dates and also the time from SQL server 2012.

 Using sqlCon = Connection()
 
Dim dt As New DataTable

Dim query As String = "select * from Tblinvoices where saledate >= @saledateand1 saledate <= @saledate2 order by saledate DESC"
  Dim cmd As SqlCommand = New SqlCommand
  cmd.CommandText = query
  cmd.Connection = sqlCon
  cmd.CommandType = CommandType.Text
  cmd.CommandTimeout = 90
                 
 cmd.Parameters.Add("@saledate1", SqlDbType.Datetime).Value = dtsaledate1.Text
cmd.Parameters.Add("@saledate2", SqlDbType.Datetime).Value = dtsaledate2.Text

         Dim da As New SqlDataAdapter
        da.SelectCommand = cmd
              
        da.Fill(dt)
        datalistado.DataSource = dt
             
 End Using
        

When I use the code above I only get the date on the datagridview but since I have saved date and time in the DB it does not display anything. By date and time I mean like this 8/11/2008 3:32 PM

Is it possible to filter datetime from SQL server into the datagridview, I don't want only the date, I honestly need date and time and I will really appreciate some help.

Regards, 

解决方案

Use BETWEEN e.g.

Public Function BetweenTwoDates(ByVal pStartDate As DateTime, pEndDate As DateTime) As DataTable
    Dim dt As New DataTable
    Using cn As New SqlConnection With {.ConnectionString = "Data Source=KARENS-PC;Initial Catalog=ForumExamples;Integrated Security=True"}
        Using cmd As New SqlCommand With {.Connection = cn}
            cmd.CommandText = "SELECT id,StartDate,EndDate FROM BetweenDates WHERE StartDate BETWEEN @StartDate AND @EndDate"
            cmd.Parameters.AddWithValue("@StartDate", pStartDate)
            cmd.Parameters.AddWithValue("@EndDate", pEndDate)
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    Return dt
End Function

Call (DataOperations in this case is a class that contains the method above, you could have the method above directly in your form, I like to use classes)

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim ops As New DataOperations
    Dim dt As DataTable = ops.BetweenTwoDates(#2017-09-01 04:37:42#, #2017-09-04 04:37:42#)
End Sub

I hard coded dates, you would use your DateTimePickers in place of the hard coded values e.g. SomeDataTimePicker.Value.

Query ran in SQL-Server Management Studio.

Data in DataTable

这篇关于使用两个datatimepickers和datagridview过滤信息。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！