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
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    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
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

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

    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
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

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

    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
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

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

    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
  •  



Click Here to Expand Forum to Full Width