locked
To filter information using two datatimepickers and datagridview. RRS feed

  • Question

  • 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, 


    • Edited by Ivanoskie Thursday, August 24, 2017 8:13 PM
    Thursday, August 24, 2017 7:57 PM

All replies

  • 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


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, August 24, 2017 10:46 PM
  • Hi lvanoskie,

    Based on your description, I think you need to set DateTimePicker's format, so it can work fine. Please refer to the code below.

       Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            DateTimePicker1.Format = DateTimePickerFormat.Custom
            DateTimePicker1.CustomFormat = "dd/MM/yyyy HH:mm:ss"
            DateTimePicker2.Format = DateTimePickerFormat.Custom
            DateTimePicker2.CustomFormat = "dd/MM/yyyy HH:mm:ss"
    
        End Sub
        Private Sub fun()
    
            Dim dt As New DataTable
            Dim conn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Data3.mdf;Integrated Security=True")
            Dim sql As String = "Select * from Test10 where BirthDate between @BirthDate1 and @BirthDate2"
            Dim sql1 As String = "Select * from Test10 where BirthDate > @BirthDate1 and BirthDate < @BirthDate2"
            conn.Open()
            Dim cmd As New SqlCommand(sql, conn)
            cmd.Parameters.AddWithValue("@BirthDate1", DateTimePicker1.Text.ToString())
            cmd.Parameters.AddWithValue("@BirthDate2", DateTimePicker2.Text.ToString())
            Dim adapter = New SqlDataAdapter(cmd)
            adapter.Fill(dt)
            DataGridView1.DataSource = dt
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            fun()
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 25, 2017 4:42 AM