Help with DBNull issue when displaying images

[mosipenko]

I have a website that has articles and images. The articles are stored
in one table, and if they have an image associated with them, it's
stored in another table with a common id linking them. I can pull all
the information fine into labels, and if the ImagePath field is NULL,
then it just doesn't show up. My problem is that when I'm trying to
display the images, I get a "Conversion from type 'DBNull' to type
'String' is not valid" error when that field is NULL. How can I get
this to work??

This is my code on the aspx page to display the image:

<img src='\main\content\images\<%#
System.IO.Path.GetFileName(Eval("ImagePath"))%>' width="150px">

This is my code on the aspx.vb page to pull the image:

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

 

[Karl Seguin [MVP]]

Well, since you aren't creating proper tiers, you could simply change ur
query to something like:

"SELECT IsNull(ImagePath, "na.gif") AS ImagePath FROM Image...


also, ur not doing urself any favor by not using try/finally and not using
command paramters. Your code is open to a huge (and easily exploitable) SQL
Injection attack. If your connection string is using the SA account, I'd
have ur database formatted in about 2 seconds...after I got the data off of
it.

Karl

 

[mosipenko]

Well, I just switched from using ASP to ASP.NET last week, so I'm still
fairly new to all of this and have been using the tools in VS2005. I
have tried using the Select IsNull.....but it didn't work. Also, what
should I do with the Try/Finally you mentioned and command parameters?

 

[Craig Deelsnyder]

Well, I just switched from using ASP to ASP.NET last week, so I'm still
fairly new to all of this and have been using the tools in VS2005. I
have tried using the Select IsNull.....but it didn't work. Also, what
should I do with the Try/Finally you mentioned and command parameters?

exception handling, etc (there are so many articles that could go over
how to properly code error handling, btw, so feel free to google for
more, they're out there):
http://www.dotnetjohn.com/articles.aspx?articleid=42

parameterized queries are what you're looking for regarding your inline SQL:
http://www.4guysfromrolla.com/webtech/092601-1.shtml

hth

 

[sloan]

Do a google search for "SafeDataReader".

Looks like you're using VB.net , and most versions of it are in vb.net

http://www.lhotka.net/Articles.aspx?id=9280bc86-c706-4d2d-8993-8b5bda6bad22


A couple of things.

At the very least, you ought to move your code to an object, which takes
your strID as a parameter.... and returns an IDataReader (or a
SafeDataReader if you choose that route)

I don't know if you're code is production or example ....it its production,
then I'd try to clean it up a little, by encapsulating the logic somewhere.

Once you get a SafeDataReader back.... then you can use it.

You might also write a little wrapper function on the aspx code behind page

public function CheckForFileExists ( imgName as string) as string
if imgName.Length > 0 then
return imgName
else
return ""
end if
end function

where I have return imgName, you could build your html code to show the
image.

 

[Swanand Mokashi]

You can also use the MS Data access application block. as DAL.
http://www.microsoft.com/downloads/...0A-9877-4A7B-88EC-0426B48DF275&displaylang=en

It is part of the Enterprise Library now:
FOR 2.0 :
http://msdn.microsoft.com/library/?url=/library/en-us/dnpag2/html/EntLib2.asp

http://www.microsoft.com/downloads/...70-406B-4F2A-B723-97BA84AE80B5&displaylang=en

FOR 1.1 :
http://www.microsoft.com/downloads/...09-660E-444E-945A-6B32AF1581B3&displaylang=en

 

[Karl Seguin [MVP]]

Dim connectionString As String = "Data Source=server;Initial
Catalog=database; User ID=user;Password=password;"
dim connection as SqlConnection
dim command as SqlCommand
dim reader as SqlDataReader
try
connection = new SqlConnection(connectionString)
command = new SqlCommand()
command.CommandText = "SELECT ImagePath from Image where cid =
@CategoryId"
command.Parameters.Add("@CategoryId", SqlDbType.VarChar, 5).Value =
categoryId;
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 not reader is nothing then
reader.Dispose()
end if
end try

 

[mosipenko]

Ok, so I put that code in the apsx.vb right? So where do I put what the
@Categoryid is? Do I call the Function on the aspx page?