Results 1 to 9 of 9

Thread: Filter by Date not working on DatagridView_vb_vs2019

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    10

    Exclamation Filter by Date not working on DatagridView_vb_vs2019

    Hi there,

    I an trying to put multiple 'OR' sql statement in vb for to display data for a table holding transaction data, but It is not working.
    I want that query should search the entire table along with date condition. Below is my code
    Code:
    Dim search As String = "select * from transactions where txn_date between @dt_from and @dt_to and txn_number like '%" & tx_txn_criteria.Text & "%' or txn_remarks like '%" & tx_txn_criteria.Text & "%'"
    
            Dim cmd As New SqlCommand(search, con)
                Dim da As New SqlDataAdapter(cmd)
    
                cmd.Parameters.AddWithValue("@dt_from", SqlDbType.DateTime).Value = dt_from.Value
                cmd.Parameters.AddWithValue("@dt_to", SqlDbType.DateTime).Value = dt_to.Value
    
            Dim table As New DataTable()
    
                da.Fill(table)
    
            If table.Rows.Count > 0 Then
    
                DataGridView1.DataSource = table
    
            Else
    
                MessageBox.Show("No Data Found")
    
            End If
    
            con.Close()
    Last edited by si_the_geek; Oct 18th, 2020 at 12:17 PM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Filter by Date not working on DatagridView_vb_vs2019

    When you post code please put it inside code tags so it is displayed in a more readable way - either using the # or VB buttons in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [code] code here [/code] (I have added them to your post)



    Whenever you have a mixture of AND/OR, which order do you want them applied?

    Assuming you have "A and B or C", does that mean you want:
    • just C to be true, otherwise both A and B to both be true
    • A must be true, plus either B or C must be true

    Programming languages and database systems can't read your mind, so they will "guess" unless you specify your preference, which you can do using brackets:
    "(A and B) or C"
    "A and (B or C)"

  3. #3
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Filter by Date not working on DatagridView_vb_vs2019

    Quote Originally Posted by si_the_geek View Post
    Programming languages and database systems can't read your mind, so they will "guess" unless you specify your preference, which you can do using brackets:
    "(A and B) or C"
    "A and (B or C)"
    if I remember correctly languages have Precedence and order of evaluation (may depends of the language, by the way) and "AND" has precedence over "OR" so "A and B or C" is equivalent to "(A and B) or C"
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Filter by Date not working on DatagridView_vb_vs2019

    Quote Originally Posted by Delaney View Post
    if I remember correctly languages have Precedence and order of evaluation (may depends of the language, by the way)
    That is correct... and if you know the language(s) involved well enough you can tell how it will be interpreted, but it is best to specify the order yourself with brackets (for the sake of readability, and portability to other languages, etc).

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Filter by Date not working on DatagridView_vb_vs2019

    How about you provide an example of the data, what you want and what you actually get?

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    10

    Re: Filter by Date not working on DatagridView_vb_vs2019

    I need to search entire table as per the text which I will insert into the textboxes within the date ranges from datetimepickers. I mean, If i want to search for "mytext" from the table within date range dateX and DateY, it should return the value in dgv.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Filter by Date not working on DatagridView_vb_vs2019

    It's like you just ignored everything that everyone posted and just repeated what we already knew. If you're not prepared to provide details then we have to guess. Some may not be prepared to guess at all. Others may guess and get it wrong. If you provide a FULL and CLEAR explanation of the problem, both scenarios can be avoided.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Filter by Date not working on DatagridView_vb_vs2019

    Also, why are you using parameters for the dates but not the text? Use parameters for ALL values.

  9. #9

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    10

    Re: Filter by Date not working on DatagridView_vb_vs2019

    i read every post on this topic and it worked. Thankx everyone for the help .

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width