Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem handling DBNull on aspx page

Posted on 2004-11-02
15
Medium Priority
?
4,035 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
Comment
Question by:dplsr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 28

Expert Comment

by:mmarinov
ID: 12472412
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
ID: 12472416
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
ID: 12472423
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:AerosSaga
ID: 12472435
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
ID: 12472438
 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
ID: 12472440
the first is to code sample handles return nulls, the second handles inserting nulls;)
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12472546
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
ID: 12472838
if not isdbnull(parameterProductImage.Value) then
      myProductDetails.ProductImage = CStr(parameterProductImage.Value)
else
      myProductDetails.ProductImage =String.Empty
end if
0
 

Author Comment

by:dplsr
ID: 12473171
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:
mmarinov earned 1000 total points
ID: 12473228
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:Ramesh Srinivas
ID: 12473328
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
ID: 12473344
ok

thanks everyone!
0
 

Author Comment

by:dplsr
ID: 12473483

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
ID: 12473583
strange but possible
good luck

Regards!
B..M
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

610 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