Solved

conversion from type dbnull to string is not valid

Posted on 2008-10-31
9
428 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
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 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