How to read empty data

[mosipenko]

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:

<aspataList 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>
</aspataList>


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]]

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).

 

[mosipenko]

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

 

[Mark Rae]

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...?

 

[Marina Levit [MVP]]

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?

 

[mosipenko]

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.

 

[Mark Rae]

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

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...

???

 

[mosipenko]

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]]

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?

 

[mosipenko]

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]]

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.

 

[Mark Rae]

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.

 

[mosipenko]

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

 

[mosipenko]

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.

 

[Mark Rae]

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

Look it up in Books OnLine

 

[mosipenko]

Nevermind. i figured it out. I just put the System.IO...around the IFF
part and it works fine.