Solved

conversion from type dbnull to string is not valid

Posted on 2008-10-31
9
422 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now