?
Solved

conversion from type dbnull to string is not valid

Posted on 2008-10-31
9
Medium Priority
?
431 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 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 1000 total points
ID: 22868707
line 15 just do smthn like Dim varpic As String = String.Empty
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month10 days, 4 hours left to enroll

762 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