dcsimg
Results 1 to 13 of 13

Thread: Query for empty fields

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    444

    Query for empty fields

    Hello VBForums
    Hello every one
    Please how to correct this code for query for empty fields
    I have a table named TABLE1 and fields (Favor1 - Favor2 - Favor3 - Favor4)
    The named field (Favor4) is of type Date
    Sometimes I leave this field (Favor4) empty without writing the date
    I want by Button1 selects lines whose (Favor4) is empty and displayed in DataGridView1
    I tried by this code but I did not succeed
    Code:
          Try
                Using InfoCommand = New SqlCommand("SELECT * from TABLE1 where isnull(FAVOR4 ,0 )) ", Conn)
                    Using InfoAdapter = New SqlDataAdapter(InfoCommand)
                        InfoAdapter.SelectCommand = InfoCommand
                        Infotable = New DataTable()
                        InfoAdapter.Fill( Infotable)
                        DataGridView1.DataSource = Infotable
    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Conn_2018.Close()
            End Try
    Thank you in advance for help
    Cordially
    MADA

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

    Re: Query for empty fields

    The IsNull function you used isn't what you want (if the field is null the other value gets returned, otherwise the field does).

    What you want is the simple check:
    "SELECT * from TABLE1 where FAVOR4 is null"

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    444

    Re: Query for empty fields

    Hii Si_The_Geek
    Thank you for help
    I tried like this
    Code:
     Using InfoCommand = New SqlCommand("SELECT * from TABLE1 where Favor4 isnull) ", Conn)
    But i have this error
    (An expression of non-Boolean of type specified in a context where a condition is expected , near 'isnull'
    Cordially
    MADA

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

    Re: Query for empty fields

    spot the difference:

    is null
    isnull


    They have different meanings, and different behaviour. The one I posted is the one you want.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    444

    Re: Query for empty fields

    Now with ( IS NULL ) i have incorrect syntax near ')'

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,210

    Re: Query for empty fields

    Rather than making up variations of what I posted, copy and paste what I wrote back in post #2.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    444

    Re: Query for empty fields

    Really there is no fault but I have DatagridView1 empty despite i left some empty Favor4 line to try

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,039

    Re: Query for empty fields

    Show us the actual code you're using now. Also, are you sure that your database fields are actually NULL and not empty strings? If you have a valid query that filters out non-NULL records and executes successfully but returns no records then it's a simple fact that there are no records in your database that contain NULL.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    444

    Re: Query for empty fields

    Hii jmcilhinney
    Thank you for help
    This is my really code in my Form
    I find this code and it work very very well for me but if change the field ( Favor4 ) in my database to type Nvarchar
    But it does not work when I put the date type field ( Favor4 ) to type Date .. I will not have mistakes but I will have the DataGridView1 empty
    Code:
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
     Try
                Using InfoCommand = New SqlCommand("SELECT * FROM TABLE1 WHERE ISNULL(LTRIM(RTRIM(FAVOR4)),'')=''", Conne)
                    Using InfoAdapter = New SqlDataAdapter(InfoCommand)
                        Infotable = New DataTable()
                        InfoAdapter.Fill( Infotable )
                        DataGridView1.DataSource =  Infotable 
                        Dim Count As Integer = 0
                        Count = DataGridView1.Rows.Count
                        TextBox1.Text = Count
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Conne.Close()
            End Try
        End Sub
    How can change it to work with the Date type please ??
    Cordially
    MADA

  10. #10
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    649

    Re: Query for empty fields

    It looks like youre opening a new connection within a connection that is replacing a connection with the same connection that may or may not have an unrelated connection to the connection you are needlessly closing in the event of an exception within the connection

  11. #11
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,039

    Re: Query for empty fields

    Quote Originally Posted by MADA BLACK View Post
    Hii jmcilhinney
    Thank you for help
    This is my really code in my Form
    I find this code and it work very very well for me but if change the field ( Favor4 ) in my database to type Nvarchar
    But it does not work when I put the date type field ( Favor4 ) to type Date .. I will not have mistakes but I will have the DataGridView1 empty
    Code:
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
     Try
                Using InfoCommand = New SqlCommand("SELECT * FROM TABLE1 WHERE ISNULL(LTRIM(RTRIM(FAVOR4)),'')=''", Conne)
                    Using InfoAdapter = New SqlDataAdapter(InfoCommand)
                        Infotable = New DataTable()
                        InfoAdapter.Fill( Infotable )
                        DataGridView1.DataSource =  Infotable 
                        Dim Count As Integer = 0
                        Count = DataGridView1.Rows.Count
                        TextBox1.Text = Count
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Conne.Close()
            End Try
        End Sub
    How can change it to work with the Date type please ??
    Cordially
    MADA
    Of course that code doesn't work if the column is not a text data type. You can't trim something that isn't text and something that isn't text can't be equal to an empty string. If the data type of your column is Date then it either contains a date or it's NULL. There are no other options. si_the_geek has told you exactly what to do three times and you are still not doing it. If you come here and ask a question and someone gives you the answer, do what they tell you to do. If you don't then you're just wasting everyone's time. Why are you refusing to do what you were told to do?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2015
    Location
    ANTANANARIVO
    Posts
    444

    Re: Query for empty fields

    Thank you Kpmc
    Thank you jmcilhinney
    No master I do not refuse .. but I swear the code if the geek did not work with me and I tried too much
    Cordially
    MADA

  13. #13
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,039

    Re: Query for empty fields

    You've been told that you need to compare a column using something like "WHERE MyColumn IS NULL" and yet every time you have shown the code you are using, you are not using code like that. You've been told not to use the ISNULL function and yet every time you have shown the code you are using, you are using the ISNULL function. Write your code as you have been instructed to write it. If THAT code doesn't work, post THAT code and tell us EXACTLY what DID happen when you ran it. What possible point could there be to your repeatedly posting code that we've already told you won't work and asking us why it's not working and how you should change it? You've been told how to change but, as far as we can tell, you refuse to do so. You say that you have but we've seen no evidence of it. If it didn't work then you did something wrong but if you never show us what you did then we can never tell you what's wrong with it. The way you're carrying on here is only going to frustrate those trying to help you. Some, like me, will voice their displeasure and some will simply stop posting. Either way, it doesn't help you this time and it makes it less likely you'll get help next time, as people remember what a painful experience they had before.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width