Solved

conversion from type dbnull to string is not valid

Posted on 2008-10-31
9
424 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASCX file or a newer alternative? 1 34
Delete HTML table rows 12 28
ASP.net Show "please wait " message as page loads 2 41
No Data for DropDown List 2 23
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…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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