datareader and empty records-VBForums
Results 1 to 9 of 9

Thread: datareader and empty records

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2006
    Posts
    44

    datareader and empty records

    I have a sql query that sometimes returns nothing...not null, just nothing.

    This is causing my datareader to error out.

    Is there a function, like isDbNull, but is for empty recordsets?

    Thanks!

  2. #2
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    6,900

    Re: datareader and empty records

    Is use a line like this:

    vb.net Code:
    1. If DataReader IsNot Nothing Then
    2.    DataReader.Read()
    3. End If
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3
    PowerPoster
    Join Date
    May 2002
    Posts
    24,777

    Re: datareader and empty records

    I think the key is the DataReader.Read method... if there is a row to read, it will return true... if there are no rows, it returns false...
    Code:
    If myDataReader.Read() Then
       'You have data
    Else
       'You don't
    End if
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,337

    Re: datareader and empty records

    If you want to know whether a DataReader contains any records then you can test its HasRows property. You will also generally use the Read method that tg mentioned in a While loop:
    vb.net Code:
    1. If myDataReader.HasRows Then
    2.     While myDataReader.Read()
    3.         'Read current row.
    4.     End While
    5. Else
    6.     MessageBox.Show("No data returned.")
    7. End If
    If you didn't need to notify the user of the empty result set then you'd do away with the If...Else block.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  5. #5
    Lively Member viper5646's Avatar
    Join Date
    Feb 2009
    Location
    kitchener Ontario
    Posts
    69

    Re: datareader and empty records

    What if the datareader only has an empty row?

    I'm tying to fill this Arraylist, but I get an Specified cast is not valid. error
    when I get a DataReader that only has an empty row.
    So how can I detect if it has data or not?
    I have tried m_dr.hasrows and .isdbnull neither worked.
    Code:
     Dim x As Integer
            While m_dr.Read
                ModelBuilder_Relation.Add(m_dr.GetInt32(0))
                x = x + 1
            End While

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,337

    Re: datareader and empty records

    I don't know where that quote of viper5646's came from from but, if the data reader has an "empty" row, obviously HasRows will return True. An "empty" row is still a row. Presumably an "empty" row is one that contains a DBNull in every field, so using IsDBNull is the answer. Whoever posted that quote in the first place says that they tried IsDBNull but they obviously didn't use it properly. They would have to test every field for DBNull, which you could do by explicitly specifying every field:
    vb.net Code:
    1. If dr.IsDBNull(0) AndAlso dr.IsDBNull(1) AndAlso dr.IsDBNull(2) Then
    2.     'The first three columns are all NULL.
    3. End If
    or you could use a loop or LINQ query.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  7. #7
    Lively Member viper5646's Avatar
    Join Date
    Feb 2009
    Location
    kitchener Ontario
    Posts
    69

    Re: datareader and empty records

    Thanks jmcilhinney for your reply

    when I tried the IsDBnull I get a No data exists for the row/column Error
    When it executes the If m_dr.IsDBNull(0) line
    This error appears even if there is data in the datareader.
    This datareader contains only one column .
    This is the code I used to test it.
    Code:
       If m_dr.IsDBNull(0) Then
                MsgBox("null")
            End If
    Last edited by viper5646; Jun 13th, 2011 at 08:03 PM.

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

    Re: datareader and empty records

    Quote Originally Posted by viper5646 View Post
    Thanks jmcilhinney for your reply

    when I tried the IsDBnull I get a No data exists for the row/column Error
    When it executes the If m_dr.IsDBNull(0) line
    This error appears even if there is data in the datareader.
    This datareader contains only one column .
    This is the code I used to test it.
    Code:
       If m_dr.IsDBNull(0) Then
                MsgBox("null")
            End If
    Have you called Read? The DataReader doesn't advance to the first row until you call Read.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  9. #9
    Lively Member viper5646's Avatar
    Join Date
    Feb 2009
    Location
    kitchener Ontario
    Posts
    69

    Re: datareader and empty records

    Thanks

    But there is still a lot that I need to learn about vb.
    With your help I have it working.
    Last edited by viper5646; Jun 13th, 2011 at 08:43 PM.

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

Survey posted by VBForums.