Problem handling DBNull on aspx page

Good morning!

I am having a problem with a DBnull on a aspx page using VB  When the column cImageurl in our database is a dbnull we get the following error on page load:

Cast from type 'DBNull' to type 'String' is not valid.

At Line 51 which is  marked

I have tried functions on the web page, but the error is being generated when the custom control is called. I tried to handle it in the custom control and the stored procedure with no luck.   All the code works great except for this field. I have removed all of the other  fields to save space on here.

*************************************
Sql 2000 Stored procedure:

CREATE Procedure spProductDetail
(
    @ProductID    int,
    @ProductImage nvarchar(50) OUTPUT,
)
AS

SELECT
    @ProductImage = cImageurl

FROM
    CMRC_Products
WHERE
    ProductID = @ProductID
GO

******************************************

Code:
   
   Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
      Dim products As ASPNET.StarterKit.Commerce.ProductsDB = New ASPNET.StarterKit.Commerce.ProductsDB()

‘Line 51      
   Dim myProductDetails As ASPNET.StarterKit.Commerce.ProductDetails = products.GetProductDetails(ProductID)

ProductImage.ImageUrl = "Kohler/pict/" & myProductDetails.ProductImage
End sub

Custom control:
    Public Function GetProductDetails(ByVal productID As Integer) As ProductDetails
            Dim myConnection As SqlConnection = NewSqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As SqlCommand = New SqlCommand("spProductDetail", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure

            Dim parameterProductID As SqlParameter = New SqlParameter("@ProductID", SqlDbType.Int, 4)
            parameterProductID.Value = productID
            myCommand.Parameters.Add(parameterProductID)
            Dim parameterProductImage As SqlParameter = New SqlParameter("@ProductImage", SqlDbType.NVarChar, 50)
            parameterProductImage.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterProductImage)

          myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            Dim myProductDetails As ProductDetails = New ProductDetails()
            myProductDetails.ProductImage = CStr(parameterProductImage.Value)
           Return myProductDetails

        End Function

***************************************************

Html area
<asp:image id="ProductImage"  width="250" height="250" runat="server" border="0" />
dplsrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mmarinovCommented:
Hi dplsr,

replace this line

myProductDetails.ProductImage = CStr(parameterProductImage.Value)

with this one

myProductDetails.ProductImage = IIF(parameterProductImage.Value = DBNull.Value, String.Empty, CStr(parameterProductImage.Value))


Regards!
B..M
0
AerosSagaCommented:
Private Sub LoadProductData(ByVal JobNumberID As Integer)
        Dim cnn As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("EmeraldConnStr"))
        Dim cmd As New SqlClient.SqlCommand
        Dim dr As SqlClient.SqlDataReader
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "dbo.jobsViewAllJobs"
        cmd.Parameters.Add(New SqlClient.SqlParameter("@JobNumber", JobNumberID))
        cmd.Parameters.Add(New SqlClient.SqlParameter("@CustomerID", ""))
        cmd.Connection = cnn
        cnn.Open()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        dr.Read()
        Me.lblJobNumber.Text = CStr(dr("JobNumber"))
        If Not IsDBNull(dr("CustomerID")) Then
            Me.txtCustomerID.Text = CStr(dr("CustomerID"))
        Else
            Me.txtCustomerID.Text = "NULL"
        End If
        Me.txtStartDate.Text = CStr(dr("StartDate"))
        If Not IsDBNull(dr("EndDate")) Then
            Me.txtEndDate.Text = CStr(dr("EndDate"))
        Else
            Me.txtDescription.Text = "NULL"
        End If
        Me.txtAssignedStaff.Text = CStr(dr("AssignedStaff"))
        If Not IsDBNull(dr("Description")) Then
            Me.txtDescription.Text = CStr(dr("Description"))
        Else
            Me.txtDescription.Text = "NULL"
        End If
        cnn.Close()
        dr.Close()
        cmd.Dispose()
        cnn.Dispose()
    End Sub
    Private Sub Submit1_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick
        Page.Validate()
        If Me.IsValid Then
            Dim cnn As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("EmeraldConnStr"))
            Dim cmd As New SqlClient.SqlCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "dbo.jobsUpdateJob"
            If Me.txtCustomerID.Text <> "NULL" Then
                cmd.Parameters.Add("@CustomerID", Me.txtCustomerID.Text)
            Else
                cmd.Parameters.Add("@CustomerID", DBNull.Value)
            End If
            cmd.Parameters.Add("@StartDate", Me.txtStartDate.Text)
            cmd.Parameters.Add("@EndDate", Me.txtEndDate.Text)
            cmd.Parameters.Add("@AssignedStaff", Me.txtAssignedStaff.Text)
            cmd.Parameters.Add("@Description", Me.txtDescription.Text)
            cmd.Parameters.Add("@JobNumber", Me.lblJobNumberID.Text)
            cmd.Connection = cnn
            cnn.Open()
            cmd.ExecuteNonQuery()
            cnn.Close()
            cmd.Dispose()
            cnn.Dispose()
            Response.Redirect("ManageJobs.aspx")
        End If
    End Sub
0
AerosSagaCommented:
This part is the key:

If Not IsDBNull(dr("CustomerID")) Then
            Me.txtCustomerID.Text = CStr(dr("CustomerID"))
        Else
            Me.txtCustomerID.Text = "NULL"
        End If

Aeros
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

AerosSagaCommented:
do it the correct way and use dbnull.value

 Dim cnn As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("EmeraldConnStr"))
            Dim cmd As New SqlClient.SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "Wireless.dbo.RadioInsert"
            If Me.txtCustomerID.Text <> "" Then
                cmd.Parameters.Add("@CustomerID", Me.txtCustomerID.Text)
            Else
                cmd.Parameters.Add("@CustomerID", DBNull.Value)
            End If
            cmd.Parameters.Add("@MacAddress", Me.txtMACAddress.Text)
            cmd.Parameters.Add("@SerialNumber", Me.txtSerialNumber.Text)
            cmd.Parameters.Add("@LastModifyUser", Me.User.Identity.Name)
            cnn.Open()
            cmd.ExecuteNonQuery()
            cnn.Close()
            cmd.Dispose()
            cnn.Dispose()
0
AerosSagaCommented:
 If Me.txtCustomerID.Text <> "" Then
                cmd.Parameters.Add("@CustomerID", Me.txtCustomerID.Text)
            Else
                cmd.Parameters.Add("@CustomerID", DBNull.Value)
            End If
0
AerosSagaCommented:
the first is to code sample handles return nulls, the second handles inserting nulls;)
0
mmarinovCommented:
AerosSaga,

i'm sorry for this reply but do i need to read all this notification emails of your postings and read your posts because you don't see that the dplsr is not inserting values but checking the returned value of the select statement?
Is it too hard to read the question and post just one comment direct to the question and this bunch of codes :(?

B..M
0
YZlatCommented:
if not isdbnull(parameterProductImage.Value) then
      myProductDetails.ProductImage = CStr(parameterProductImage.Value)
else
      myProductDetails.ProductImage =String.Empty
end if
0
dplsrAuthor Commented:
Hi mmarinov,

myProductDetails.ProductImage = IIF(parameterProductImage.Value = DBNull.Value, String.Empty, CStr(parameterProductImage.Value))

returned all kinds of errors when I compiled it.  
I simply did this, and it works great.
 myProductDetails.ProductImage = CStr(parameterProductImage.Value & String.Empty)

here are the compile  errors:
 C:\PLF\Components\ProductsDB.vb(190) : error BC30451: Name 'IIF' is not declare
.

myProductDetails.ProductImage = IIF(parameterProductImage.Value = DBNull.Value,
String.Empty, CStr(parameterProductImage.Value))
                                ~~~

C:\PLF\Components\ProductsDB.vb(190) : error BC31080: Operator '=' is not defin
d for types 'System.Object' and 'System.DBNull'. Use 'Is' operator to compare t
o reference types.

myProductDetails.ProductImage = IIF(parameterProductImage.Value = DBNull.Value,
String.Empty, CStr(parameterProductImage.Value))
0
mmarinovCommented:
dplsr,

it is great that you've found the solution
the errors for IIDF is not declared is because the Microsoft.VisualBasic namespaces is not imported
Imports Microsoft.VisualBasic ( in Microsoft.VisualBasic.dll )

just for you, the correct code should be

myProductDetails.ProductImage = IIF(parameterProductImage.Value Is DBNull.Value,
String.Empty, CStr(parameterProductImage.Value))

Regards!
B..M
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ramesh SrinivasTechnical ConsultantCommented:
I usually do this:

myProductDetails.ProductImage = IIF(parameterProductImage.Value Is DBNull.Value,
Nothing, CStr(parameterProductImage.Value))


works for me!

regards,

KS
0
dplsrAuthor Commented:
ok

thanks everyone!
0
dplsrAuthor Commented:

Hi mmarinov,
I tried this after adding Imports Microsoft.VisualBasic

myProductDetails.ProductImage = IIF(parameterProductImage.Value Is DBNull.Value,
String.Empty, CStr(parameterProductImage.Value))

I get no errors when I compile, but it does not, I am back to the rror on the web page

so far this id the only thing that works
myProductDetails.ProductImage = CStr(parameterProductImage.Value & String.Empty)
0
mmarinovCommented:
strange but possible
good luck

Regards!
B..M
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.