ADO.NET provides database connectivity between relational and non relational systems through a common set of components. The ADO.NET classes are divided into two components, the Data Providers and the DataSet.
In many situations while reading data from Dataset, we have seen the error message like this,
Conversion from type 'DBNull' to type '
Here is the solution for how to handle DBNull. The DBNull class represents a nonexistent value. The function IsDBNull returns True if the data type of expression evaluates to the DBNull type otherwise, IsDBNull returns False.
If IsDBNull(ds.Tables(0).Rows(i).Item(0)) Then
MsgBox("DBNULL exist in the field ")
Else
MsgBox(CInt(ds.Tables(0).Rows(i).Item(0)))
End If
In the above code we are using the function IsDBNull to check wether the Dataset value is a DBNULL or not.
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim i As Integer
Dim sql As String
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
sql = "your sql select statements here"
connection = New SqlConnection(connetionString)
connection.Open()
command = New SqlCommand(sql, connection)
adapter.SelectCommand = command
adapter.Fill(ds)
connection.Close()
Try
If IsDBNull(ds.Tables(0).Rows(i).Item(0)) Then
MsgBox("DBNULL exist in the field ")
Else
MsgBox(CInt(ds.Tables(0).Rows(i).Item(0)))
End If
Catch ex As Exception
MsgBox(ex.ToString())
End Try
End Sub
End Class