Solved

Problem handling DBNull on aspx page

Posted on 2004-11-02
4,031 Views
Last Modified: 2008-02-01
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" />
0
Question by:dplsr
    14 Comments
     
    LVL 28

    Expert Comment

    by:mmarinov
    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
     
    LVL 17

    Expert Comment

    by:AerosSaga
    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
     
    LVL 17

    Expert Comment

    by:AerosSaga
    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
     
    LVL 17

    Expert Comment

    by:AerosSaga
    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
     
    LVL 17

    Expert Comment

    by:AerosSaga
     If Me.txtCustomerID.Text <> "" Then
                    cmd.Parameters.Add("@CustomerID", Me.txtCustomerID.Text)
                Else
                    cmd.Parameters.Add("@CustomerID", DBNull.Value)
                End If
    0
     
    LVL 17

    Expert Comment

    by:AerosSaga
    the first is to code sample handles return nulls, the second handles inserting nulls;)
    0
     
    LVL 28

    Expert Comment

    by:mmarinov
    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
     
    LVL 35

    Expert Comment

    by:YZlat
    if not isdbnull(parameterProductImage.Value) then
          myProductDetails.ProductImage = CStr(parameterProductImage.Value)
    else
          myProductDetails.ProductImage =String.Empty
    end if
    0
     

    Author Comment

    by:dplsr
    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
     
    LVL 28

    Accepted Solution

    by:
    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
     
    LVL 11

    Expert Comment

    by:saleek
    I usually do this:

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


    works for me!

    regards,

    KS
    0
     

    Author Comment

    by:dplsr
    ok

    thanks everyone!
    0
     

    Author Comment

    by:dplsr

    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
     
    LVL 28

    Expert Comment

    by:mmarinov
    strange but possible
    good luck

    Regards!
    B..M
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
    ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
    This video Micro Tutorial is the second in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles a…
    Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now