27

i am receiving this problem

Conversion from type 'DBNull' to type 'String' is not valid.

Line 501: hfSupEmail.Value = dt.Rows(0)("SupEmail")

i am very new to this, i am not really sure what is the exact problem could someone guide me?

Many thanks

Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
user3571305
  • 281
  • 1
  • 3
  • 4

8 Answers8

34

The quick and dirty fix:

hfSupEmail.Value = dt.Rows(0)("SupEmail").ToString()

or for C#:

hfsupEmail.Value = dt.Rows[0]["SupEmail"].ToString();

This works very well when your eventual target and the source data are already strings, because any extra .ToString() call for something that's already a string is likely to be optimized into a no-op by the jitter, and if it's NULL the resulting DBNull.Value.ToString() expression produces the empty string you want.

However, if you're working with non-string types, you may end up doing significant extra work, especially with something like a DateTime or numeric value where you want specific formatting. Remember, internationalization concerns mean parsing and composing date and number values are actually surprisingly expensive operations; doing "extra" work to avoid those operations is often more than worth it.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
7

Hope This Help.... dt.Rows(0)("SupEmail") returns null

To avoid this chcek before assigning 

If Not IsDBNull(dt.Rows(0)("SupEmail")) Then
    hfSupEmail.Value = dt.Rows(0)("SupEmail")
End If
Rohit
  • 324
  • 2
  • 9
4

You should handle it at DB query level itself. 

instead of "select name from student", use "select IsNull(name,'') as name from student"

In this way, DB will handle your NULL value.

Amnesh Goel
  • 2,617
  • 3
  • 28
  • 47
  • It's often much more efficient to do this work in the application tier. – Joel Coehoorn Apr 25 '14 at 04:46
  • Any specific reason? In my view, it is better to handle at DB level rather handling it at application level because at application you are engaging application server again to process the request. – Amnesh Goel Apr 25 '14 at 04:51
  • Not "again": you do this as part of the same engagement. The application server (or app itself directly) engages the DB, and handles this as part of that process. DB servers are often expensive to license compared to app servers, and even when using free database engines they are often difficult to performance tune or scale out relative to application servers, such that cpu time and RAM on the app server are very cheap compared to processing time on the DB server. – Joel Coehoorn Apr 25 '14 at 04:53
  • Joel @ Please also have a look at http://programmers.stackexchange.com/questions/36428/how-will-you-handle-null-values-in-your-application One can debate on this. It depends use case to use case. So you are right too. – Amnesh Goel Apr 25 '14 at 05:00
2

Apparently your dt.Rows(0)("SupEmail") is coming as NULL from DB and you cannot assign NULL to string property. Try replacing that line with:

hfSupEmail.Value = If(IsDbNull(dt.Rows(0)("SupEmail")), String.Empty, dt.Rows(0)("SupEmail").ToString)

The code checks if value is NULL and if it is - replaces it with empty string, otherwise uses original value.

Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
1

To handle it from code, here is a small extension method

Imports Microsoft.VisualBasic
Imports System.Runtime.CompilerServices

Public Module HTMLExtensionMethods
    <Extension()> _
    Public Function DefaultIfDBNull(Of T)(ByVal obj As Object) As T
        Return If(Convert.IsDBNull(obj), CType(Nothing, T), CType(obj, T))
    End Function
End Module

Call it like this.

hfSupEmail.Value = dt.Rows(0)("SupEmail").DefaultIfDBNull(Of String)()
naveen
  • 53,448
  • 46
  • 161
  • 251
1

You can use the Field method of the Datarow combined with an If Operator to check for a Null value in one line like this. If it is null, you can replace it with an empty string (or another string of your choosing):

hfSupEmail.Value = If(dt.Rows(0).Field(Of String)("SupEmail"), "")
Matt Wilko
  • 26,994
  • 10
  • 93
  • 143
0

The easiest way is probably to just concatenate it with an empty string:

hfSupEmail.Value = dt.Rows(0)("SupEmail") & ""
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
FrankB
  • 364
  • 2
  • 4
0
        con.Open()
        cmd = New SqlCommand
        cmd.CommandText = " select  sum (Income_Amount)  from Income where Income_Month= '" & ComboBox1.Text & "' and Income_year=" & txtyearpro.Text & ""
        cmd.Connection = con
        dr = cmd.ExecuteReader
        If dr.Read = True Then
            txtincome1.Text = dr(0).ToString  ' ToString  converts null values into string '

        End If
naasir
  • 1