Net-informations.com
SiteMap  | About    

How to handle database null values

The ADO.NET classes are divided into two components , the Data Providers and the DataSet. The DataSet is an in-memory representation of data entirely independent from the original data source.

In many situations while reading data from Dataset, we have seen the error message like this,

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

The DBNull class represents a nonexistent value. The DBNull type is a singleton class, which means only one DBNull object exists. The DBNull.Value member represents the sole DBNull object.

  if (ds.Tables[0].Rows[i].ItemArray[0] == System.DBNull.Value)
  {
	  MessageBox.Show("DBNULL exist in the field ");
  }
  else
  {
	  MessageBox.Show(ds.Tables[0].Rows[i][0].ToString());
  }

The above source code checking whether the dataset value is a DBNULL or not.




using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            int i = 0;
            string sql = null;

			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 (ds.Tables[0].Rows[i].ItemArray[0] == System.DBNull.Value)
                {
                    MessageBox.Show("DBNULL exist in the field ");
                }
                else
                {
                    MessageBox.Show(ds.Tables[0].Rows[i][0].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}






net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.