Solved

conversion from type dbnull to string is not valid

Posted on 2008-10-31
9
429 Views
Last Modified: 2012-05-05
This statement is inside my data reader which I add below along with my stored procedure in case you want to spot any errors....thanks
______if stament where i think the issue is_______
Dim varpic As String
                varpic = dr2("picture").ToString
 
                If varpic Is Nothing Then
                    Image1.ImageUrl = "~/images/sawmills/lt15.gif"
                Else
                    Image1.ImageUrl = dr2("picture")
                End If

____________end statement______________________________

____________datareader_________________________
Dim strConn2 As String = ConfigurationManager.ConnectionStrings("test").ConnectionString
            Dim conn2 As New SqlConnection(strConn2)
 
            conn2.Open()
            Dim cmd2 As New Data.SqlClient.SqlCommand("wmItemOptionShowPictureSp", conn2)
            cmd2.CommandType = CommandType.StoredProcedure

            cmd2.Parameters.Add("@optionnumber", SqlDbType.Int).Value = loghandling.SelectedValue
            Dim dr2 As Data.SqlClient.SqlDataReader = Nothing
 
            dr2 = cmd2.ExecuteReader()
 
            While dr2.Read()
                Dim varpic As String
                varpic = dr2("picture").ToString
 
                Labeldescription.Text = dr2("short_desc").ToString()
                If varpic Is Nothing Then
                    Image1.ImageUrl = "~/images/sawmills/lt15.gif"
                Else
                    Image1.ImageUrl = dr2("picture")
                End If
               
                   
            End While
                         
            conn2.Close()
            conn2.Dispose()
__________end data reader____________________

______________stoired procedure____________

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


Alter   PROCEDURE wmItemOptionShowPictureSp
      @optionnumber      INT
AS

DECLARE
      @Severity      INT


SELECT c.short_desc,c.picture,  v.option_num

FROM dbo.wmItemOptionCodeView AS v (NOLOCK)

LEFT JOIN dbo.wmitemoptioncodelang AS C (NOLOCK) ON (c.option_num=v.option_num)

WHERE v.option_num=@optionnumber


SET @Severity = 0

RETURN @Severity


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
Comment
Question by:gianitoo
[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
  • 4
  • 4
9 Comments
 
LVL 7

Expert Comment

by:the_bachelor
ID: 22853907
Try
  If String.IsNullOrEmpty(varpic ) Then

Open in new window

0
 
LVL 13

Accepted Solution

by:
TechTiger007 earned 250 total points
ID: 22854137
try something like this.
1. you should check for dbnull also
2. db connections should be done within try catch block to handle exceptions

Dim varpic As String

if ((Not dr2("picture") Is Nothing) and (dr2("picture") <> DBNull.Value)) then
                varpic = Convert.ToString(dr2("picture")) 'This will avoid exception if dr2("picture") is null
end if
 
                If String.IsNullOrEmpty(varpic) Then
                    Image1.ImageUrl = "~/images/sawmills/lt15.gif"
                Else
                    Image1.ImageUrl = dr2("picture")
                End If

____________end statement______________________________

____________datareader_________________________
Dim strConn2 As String = ConfigurationManager.ConnectionStrings("test").ConnectionString
            Dim conn2 As New SqlConnection(strConn2)
  try
            conn2.Open()
            Dim cmd2 As New Data.SqlClient.SqlCommand("wmItemOptionShowPictureSp", conn2)
            cmd2.CommandType = CommandType.StoredProcedure

            cmd2.Parameters.Add("@optionnumber", SqlDbType.Int).Value = loghandling.SelectedValue
            Dim dr2 As Data.SqlClient.SqlDataReader = Nothing
 
            dr2 = cmd2.ExecuteReader()
 
            While dr2.Read()
                Dim varpic As String
                varpic = dr2("picture").ToString
 
                Labeldescription.Text = dr2("short_desc").ToString()
                If varpic Is Nothing Then
                    Image1.ImageUrl = "~/images/sawmills/lt15.gif"
                Else
                    Image1.ImageUrl = dr2("picture")
                End If
               
                   
            End While
                  catch
finally        
            conn2.Close()
            conn2.Dispose()

end finally
0
 
LVL 1

Author Comment

by:gianitoo
ID: 22867905
what can i catch for example in this connection?
what is the benefit?
0
Industry Leaders: 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 1

Author Comment

by:gianitoo
ID: 22868064
this error

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30452: Operator '<>' is not defined for types 'Object' and 'System.DBNull'.

Source Error:

 

Line 32:                     Dim varpic As String
Line 33:
Line 34:                     If ((Not dr2("picture") Is Nothing) And (dr2("picture") <> DBNull.Value)) Then
Line 35:                         varpic = Convert.ToString(dr2("picture")) 'This will avoid exception if dr2("picture") is null
Line 36:                     End If
 

Source File: C:\Inetpub\wwwroot\us\configurator\step.aspx    Line: 34
0
 
LVL 7

Expert Comment

by:the_bachelor
ID: 22868249
you CANNOT do: dr2("picture") <> DBNull.Value
the the correct syntax is as follows:
If Not IsDBNull(dr2("picture"))

Open in new window

0
 
LVL 1

Author Comment

by:gianitoo
ID: 22868318
so this look ok?

  If ((Not dr2("picture") Is Nothing)) And (Not IsDBNull(dr2("picture"))) Then
0
 
LVL 7

Expert Comment

by:the_bachelor
ID: 22868360
since youre doing a While dr2.Read() loop.
I'd say
If Not IsDBNull(dr2("picture"))
will suffice plenty
but yes your syntax should be OK
0
 
LVL 1

Author Comment

by:gianitoo
ID: 22868406
why varpic gets highlighted in visual studio and says ehen i put my mouseover
varpic is used before it has been assigned a value. a null result exception could resukt at runtime

code below in case
 Dim strConn2 As String = ConfigurationManager.ConnectionStrings("test").ConnectionString
            Dim conn2 As New SqlConnection(strConn2)
 
            Try
                conn2.Open()
                Dim cmd2 As New Data.SqlClient.SqlCommand("wmItemOptionShowPictureSp", conn2)
                cmd2.CommandType = CommandType.StoredProcedure
 
                cmd2.Parameters.Add("@optionnumber", SqlDbType.Int).Value = loghandling.SelectedValue
                Dim dr2 As Data.SqlClient.SqlDataReader = Nothing
 
                dr2 = cmd2.ExecuteReader()
 
                While dr2.Read()
                    Dim varpic As String
 
                    If ((Not dr2("picture") Is Nothing)) And (Not IsDBNull(dr2("picture"))) Then
 
                        varpic = Convert.ToString(dr2("picture")) 'This will avoid exception if dr2("picture") is null
                    End If
 
                    If String.IsNullOrEmpty(varpic) Then
                        Image1.ImageUrl = "~/images/sawmills/lt15.gif"
                    Else
                        Image1.ImageUrl = dr2("picture")
                    End If
 
 
                    Labeldescription.Text = dr2("short_desc").ToString()
                    If varpic Is Nothing Then
                        Image1.ImageUrl = "~/images/sawmills/lt15.gif"
                    Else
                        Image1.ImageUrl = dr2("picture")
                    End If
              
                    
                End While
            Catch
            Finally
                conn2.Close()
                conn2.Dispose()
 
            End Try

Open in new window

0
 
LVL 7

Assisted Solution

by:the_bachelor
the_bachelor earned 250 total points
ID: 22868707
line 15 just do smthn like Dim varpic As String = String.Empty
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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