How to read empty data

Discussion in 'ASP .Net' started by mosipenko@hotmail.com, Apr 21, 2006.

  1. Guest

    I used to have a problem with DBNull, but now my issue is:

    System.InvalidOperationException: Invalid attempt to read when no data
    is present

    Here's my code behind:

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles Me.Load
    Dim connectionString As String = "Data Source=server;Initial
    Catalog=database; User ID=user;Password=password;"
    Dim connection As SqlClient.SqlConnection
    Dim command As SqlClient.SqlCommand
    Dim reader As SqlClient.SqlDataReader
    Dim strID As String
    strID = Request.QueryString("cidL")
    Try
    connection = New SqlClient.SqlConnection(connectionString)
    command = New SqlClient.SqlCommand()
    command.CommandText = "select top 10 a.cid, a.title,
    dbo.GetImagePathNoDT(a.cid) as ImagePath, Body as DisplayText from
    Article a inner join Schedule s on s.cid = a.cid inner join
    ContentIndex x on x.cid = a.cid inner join contentcategory cc on cc.cid
    = a.cid where cc.catid = 185 and GetDate() between s.startdt and
    s.enddt and (x.ownerpid = 'HT8071') order by updateddt desc"
    command.Connection = connection
    connection.Open()
    reader = command.ExecuteReader()
    DataList1.DataSource = reader
    DataList1.DataBind()
    Finally
    'If Not connection Is Nothing Then
    'connection.Dispose()
    'End If
    'If Not command Is Nothing Then
    'command.Dispose()
    'End If
    If reader("ImagePath") IsNot System.DBNull.Value Then
    Session("Image") = reader("ImagePath")
    Else
    Session("Image") = "logo.jpg"
    End If
    End Try

    End Sub

    And on my page, this is the code to display all the stuff from the db:

    <asp:DataList ID="DataList1" runat="server" DataKeyField="cid">
    <ItemTemplate>
    cid:
    <asp:Label ID="cidLabel" runat="server" Text='<%#
    Eval("cid") %>' value="cidL"></asp:Label><br />
    title:
    <asp:Label ID="titleLabel" runat="server" Text='<%#
    Eval("title") %>'></asp:Label><br />
    ImagePath:
    <asp:Label ID="ImagePathLabel" runat="server" Text='<%#
    Eval("ImagePath") %>'></asp:Label><br />
    <asp:TemplateColumn>
    <ItemTemplate>


    <img src='\SuperNova\content\images\<%#
    System.IO.Path.GetFileName(Session("Image"))%>' width="150px">

    </ItemTemplate>
    </asp:TemplateColumn>
    DisplayText:
    <asp:Label ID="DisplayTextLabel" runat="server"
    Text='<%# Eval("DisplayText") %>'>
    </asp:Label><br />
    <br />
    </ItemTemplate>
    </asp:DataList>


    Some of the articles don't have images. The article text and image are
    stored in 2 tables, so if the article doesn't have an image, there's no
    record in the image table. It's no problem just displaying the path,
    which will just be blank if there's no image. The problem is when I'm
    trying to display the actual image.
    , Apr 21, 2006
    #1
    1. Advertising

  2. In your Finally block you are trying to read something from the datareader.
    At this point, your datalist has been bound to it, and the datareader is
    past the last row in the resultset - meaning, you can't get anything out of
    it, since the entire result has been read by the datalist binding.

    So you can't do what you are doing there.

    In general, this error happens when you try to get data out of a datareader,
    but it is not pointing to a row in the result set - either because no rows
    were returned from the query, the datareader has not been positioned to the
    first row in the result, or the reader is past the last record (which is
    your case here).

    <> wrote in message
    news:...
    >I used to have a problem with DBNull, but now my issue is:
    >
    > System.InvalidOperationException: Invalid attempt to read when no data
    > is present
    >
    > Here's my code behind:
    >
    > Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles Me.Load
    > Dim connectionString As String = "Data Source=server;Initial
    > Catalog=database; User ID=user;Password=password;"
    > Dim connection As SqlClient.SqlConnection
    > Dim command As SqlClient.SqlCommand
    > Dim reader As SqlClient.SqlDataReader
    > Dim strID As String
    > strID = Request.QueryString("cidL")
    > Try
    > connection = New SqlClient.SqlConnection(connectionString)
    > command = New SqlClient.SqlCommand()
    > command.CommandText = "select top 10 a.cid, a.title,
    > dbo.GetImagePathNoDT(a.cid) as ImagePath, Body as DisplayText from
    > Article a inner join Schedule s on s.cid = a.cid inner join
    > ContentIndex x on x.cid = a.cid inner join contentcategory cc on cc.cid
    > = a.cid where cc.catid = 185 and GetDate() between s.startdt and
    > s.enddt and (x.ownerpid = 'HT8071') order by updateddt desc"
    > command.Connection = connection
    > connection.Open()
    > reader = command.ExecuteReader()
    > DataList1.DataSource = reader
    > DataList1.DataBind()
    > Finally
    > 'If Not connection Is Nothing Then
    > 'connection.Dispose()
    > 'End If
    > 'If Not command Is Nothing Then
    > 'command.Dispose()
    > 'End If
    > If reader("ImagePath") IsNot System.DBNull.Value Then
    > Session("Image") = reader("ImagePath")
    > Else
    > Session("Image") = "logo.jpg"
    > End If
    > End Try
    >
    > End Sub
    >
    > And on my page, this is the code to display all the stuff from the db:
    >
    > <asp:DataList ID="DataList1" runat="server" DataKeyField="cid">
    > <ItemTemplate>
    > cid:
    > <asp:Label ID="cidLabel" runat="server" Text='<%#
    > Eval("cid") %>' value="cidL"></asp:Label><br />
    > title:
    > <asp:Label ID="titleLabel" runat="server" Text='<%#
    > Eval("title") %>'></asp:Label><br />
    > ImagePath:
    > <asp:Label ID="ImagePathLabel" runat="server" Text='<%#
    > Eval("ImagePath") %>'></asp:Label><br />
    > <asp:TemplateColumn>
    > <ItemTemplate>
    >
    >
    > <img src='\SuperNova\content\images\<%#
    > System.IO.Path.GetFileName(Session("Image"))%>' width="150px">
    >
    > </ItemTemplate>
    > </asp:TemplateColumn>
    > DisplayText:
    > <asp:Label ID="DisplayTextLabel" runat="server"
    > Text='<%# Eval("DisplayText") %>'>
    > </asp:Label><br />
    > <br />
    > </ItemTemplate>
    > </asp:DataList>
    >
    >
    > Some of the articles don't have images. The article text and image are
    > stored in 2 tables, so if the article doesn't have an image, there's no
    > record in the image table. It's no problem just displaying the path,
    > which will just be blank if there's no image. The problem is when I'm
    > trying to display the actual image.
    >
    Marina Levit [MVP], Apr 21, 2006
    #2
    1. Advertising

  3. Guest

    So what's a better way of doing it? I'm still new to asp.net
    , Apr 21, 2006
    #3
  4. Mark Rae Guest

    <> wrote in message
    news:...

    > So what's a better way of doing it? I'm still new to asp.net


    What are you actually trying to achieve in your Finally clause...?
    Mark Rae, Apr 21, 2006
    #4
  5. This is really more of an ADO.NET problem, not realy related to web
    programming.

    I have no idea what it is you are trying to do, so it's hard to say.

    I am not sure why your are trying to put an Image from session into your
    datalist. First off, the binding occurrs *before* you even try to set your
    session variable - so first time around, the binding would all happen before
    the session variable is set, so no image.

    Second of all, if your query is returning the image paths - why not use what
    you get from your data? Presumably you are trying to get an image path from
    the last row - then make that the image for every row in the list. Why?

    <> wrote in message
    news:...
    > So what's a better way of doing it? I'm still new to asp.net
    >
    Marina Levit [MVP], Apr 21, 2006
    #5
  6. Guest

    That is the way that someone had suggested (on a different post) that I
    get my data. What I am trying to do is pull all the articles from the
    database that are for a specific category. Then, on the webpage,
    display the text and images (if any) for those articles. The articles
    are in one table, and if they have an image, it's in a different table.
    The article text and imagepath displays fine. The problem I have is
    with displaying the actual image (the <img
    src='\SuperNova\content\images\<%#
    System.IO.Path.GetFileName(Session("Image"))%>' width="150px"> part on
    my aspx page). If there is an image it displays, if there is no image I
    get an error.
    , Apr 21, 2006
    #6
  7. Mark Rae Guest

    <> wrote in message
    news:...

    <snip>

    > are in one table, and if they have an image, it's in a different table.


    <snip>

    > src='\SuperNova\content\images\<%#
    > System.IO.Path.GetFileName(Session("Image"))%>' width="150px">


    But this is querying a Session variable, not a record in your DataReader...

    ???
    Mark Rae, Apr 21, 2006
    #7
  8. Guest

    I originally had it as Eval("ImagePath") but I changed it for the If
    statement to the Session variable based on what the person who
    suggested that code told me to do.

    My original code for the images was this:

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles Me.Load
    Dim connStr As String = "Data Source=server;Initial
    Catalog=database; User ID=user;Password=password;"

    Dim connObj As SqlClient.SqlConnection = New
    SqlClient.SqlConnection

    Dim connCmd As New SqlClient.SqlCommand

    Dim strID As String
    strID = Request.QueryString("cidL")

    Dim selectstr As String = "Select ImagePath from Image where
    cid='" + strID + "'"


    Dim dataread As SqlClient.SqlDataReader

    Dim unameexists As Boolean

    connObj.ConnectionString = connStr

    connObj.Open()

    connCmd.CommandText = selectstr

    connCmd.Connection = connObj

    dataread = connCmd.ExecuteReader()

    DataList1.DataBind()

    dataread.Close()

    End Sub
    , Apr 21, 2006
    #8
  9. 1 problem here, is that you are never closing your connection. You need to
    do that.

    What was the problem with this code, and how was using a session variable
    supposd to solve that problem?

    What did the other person actually tell you to do? And how did he/she say
    that suggestion would make it work?

    <> wrote in message
    news:...
    >I originally had it as Eval("ImagePath") but I changed it for the If
    > statement to the Session variable based on what the person who
    > suggested that code told me to do.
    >
    > My original code for the images was this:
    >
    > Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles Me.Load
    > Dim connStr As String = "Data Source=server;Initial
    > Catalog=database; User ID=user;Password=password;"
    >
    > Dim connObj As SqlClient.SqlConnection = New
    > SqlClient.SqlConnection
    >
    > Dim connCmd As New SqlClient.SqlCommand
    >
    > Dim strID As String
    > strID = Request.QueryString("cidL")
    >
    > Dim selectstr As String = "Select ImagePath from Image where
    > cid='" + strID + "'"
    >
    >
    > Dim dataread As SqlClient.SqlDataReader
    >
    > Dim unameexists As Boolean
    >
    > connObj.ConnectionString = connStr
    >
    > connObj.Open()
    >
    > connCmd.CommandText = selectstr
    >
    > connCmd.Connection = connObj
    >
    > dataread = connCmd.ExecuteReader()
    >
    > DataList1.DataBind()
    >
    > dataread.Close()
    >
    > End Sub
    >
    Marina Levit [MVP], Apr 21, 2006
    #9
  10. Guest

    The problem with my original code (the one without the Try...Finally)
    was that I get a DBNull to String conversion error. The other person
    said I needed to use a Try...Finally to stop it, and gave me that other
    code that I posted at the top. All they had in the If statement was
    that if the reader was nothing then dispose of it. But what I needed to
    do was if the field ImagePath was DBNull, then to replace it with a
    different image (the logo.jpg). The other person suggested using the
    Session variable in the If statement to do that.
    , Apr 21, 2006
    #10
  11. You can do that in 2 ways:

    1. modify your query to not just return ImagePath, but return
    "IsNull(ImagePath,'logo.jpg')". This guarantees that the query will always
    return the value in the ImagePath column if it is not null, or the string
    'logo.jpg' otherwise.
    2. The other solution is to put the logic into your page. Something like:
    Text='<%# IIF(IsDBNull(DataItem("ImagePath")),
    "logo.jpg",DataItem("ImagePath")) %>'

    Again, I don't really understand the logic of trying to put the last image
    into a session variable and using that during binding. You would get the
    last image as the image for *every* item in the datalist - which isn't what
    you would want anyway. However, since you were trying to get the image
    after the datareader was empty, you were never even able to get that far.

    I don't know what context that code was given. It sounds like that person
    either didn't understand what you were trying to do (just like we sort of
    are trying to guess because it's not clear), or he meant for that snippet to
    be used in some other way.

    <> wrote in message
    news:...
    > The problem with my original code (the one without the Try...Finally)
    > was that I get a DBNull to String conversion error. The other person
    > said I needed to use a Try...Finally to stop it, and gave me that other
    > code that I posted at the top. All they had in the If statement was
    > that if the reader was nothing then dispose of it. But what I needed to
    > do was if the field ImagePath was DBNull, then to replace it with a
    > different image (the logo.jpg). The other person suggested using the
    > Session variable in the If statement to do that.
    >
    Marina Levit [MVP], Apr 21, 2006
    #11
  12. Mark Rae Guest

    <> wrote in message
    news:...

    > The problem with my original code (the one without the Try...Finally)
    > was that I get a DBNull to String conversion error. The other person
    > said I needed to use a Try...Finally to stop it, and gave me that other
    > code that I posted at the top. All they had in the If statement was
    > that if the reader was nothing then dispose of it. But what I needed to
    > do was if the field ImagePath was DBNull, then to replace it with a
    > different image (the logo.jpg). The other person suggested using the
    > Session variable in the If statement to do that.


    OK - a bit of lateral thinking here...

    You say you are getting the underlying data from two tables, one of which
    contains the article data and the other optionally contains an image...

    Therefore, create an outer join between the two tables - for each record
    where there is no corresponding image, you'll get a NULL in that field.

    You don't mention what your RDMBS is, but if it was SQL Server you could use
    say the COALESCE function to check if the value was NULL and optionally
    replace it with your logo.jpg reference. This way, you'll never get nulls in
    your DataList.
    Mark Rae, Apr 21, 2006
    #12
  13. Guest

    I am using SQL Server. How does the COALESCE thing work.
    , Apr 21, 2006
    #13
  14. Guest

    If I use the '<%# IIF(IsDBNull(DataItem("ImagePath")),
    "logo.jpg",DataItem("ImagePath")) %>' code, it works, but it gives me
    the path like this
    http://dotnet/SuperNova/content/images/C:/Galaxy/SuperNova/content/images/727867921.jpg.


    The way I got rid of that before was by putting the link in like this
    <img src='\SuperNova\content\images\<%#
    System.IO.Path.GetFileName(Eval("ImagePath"))%>' width="150px">, but if
    I add in the System.IO stuff into the other code, I get the DBNull to
    String Conversion error again.

    The images are stored in the database like
    C:/Galaxy/SuperNova/content/images/727867921.jpg that's why I was using
    the System.IO...to get just their filename.
    , Apr 21, 2006
    #14
  15. Mark Rae Guest

    <> wrote in message
    news:...

    >I am using SQL Server. How does the COALESCE thing work.


    Look it up in Books OnLine
    Mark Rae, Apr 21, 2006
    #15
  16. Guest

    Nevermind. i figured it out. I just put the System.IO...around the IFF
    part and it works fine.
    , Apr 21, 2006
    #16
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John

    empty/non-empty element

    John, Jul 15, 2003, in forum: XML
    Replies:
    1
    Views:
    1,015
    Klaus Johannes Rusch
    Jul 16, 2003
  2. Lukas
    Replies:
    3
    Views:
    791
    spiff
    Nov 10, 2005
  3. Marcia Hon

    Check if a directory is empty and empty it

    Marcia Hon, Feb 8, 2004, in forum: C Programming
    Replies:
    8
    Views:
    566
    Dave Thompson
    Feb 14, 2004
  4. Brian Roberts

    empty lists vs empty generators

    Brian Roberts, May 3, 2005, in forum: Python
    Replies:
    12
    Views:
    648
    Jeremy Bowers
    May 4, 2005
  5. ButlerDJIAM
    Replies:
    0
    Views:
    546
    ButlerDJIAM
    Nov 9, 2006
Loading...

Share This Page