Getting an error(cannot insert null valuesuse dbnull instead)combobox taking null values.

Question

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.VisualBasic;
//using System.Data.SqlClient;
//using System.Configuration;

namespace ESchool
{
    public partial class frmteacherallocation : frmDef_BaseRoot1
    {
        public ColFieldDescription mCFields = new ColFieldDescription();


        public frmteacherallocation()
        {
            InitializeComponent();
        }
        SqlConnection CON = new SqlConnection(ConfigurationManager.AppSettings["CONSTR"]);
        SqlCommand CMD;// = new SqlCommand();
        DataSet DSLoadData = new DataSet();
        SqlDataAdapter DAA;
        GblDefinitions GV = new GblDefinitions();
        public override bool mfnSaveForm()
        {
            bool FunctionReturnValue=false;

            DSLoadData.Tables[0].Constraints.Clear();
            DataRow drow = DSLoadData.Tables[0].NewRow();
            try
            {
                if (string.IsNullOrEmpty(cmbclassname.Text))
                {
                    MessageBox.Show("Select Class Name");
                    cmbclassname.Focus();
                    FunctionReturnValue = false;
                }
                else
                {
                    drow["Class_Name"] = cmbclassname.SelectedValue;
                }
                if (Information.IsDBNull(cmbSection.Text))
                {
                    MessageBox.Show("Select a section");
                    cmbSection.Focus();
                    FunctionReturnValue = false;
                }
                else
                {
                    drow["Section"] = cmbSection.SelectedValue;
                }
                if (string.IsNullOrEmpty(cmbstaffID.Text))
                {
                    MessageBox.Show("Select a staff id");
                    cmbstaffID.Focus();
                    FunctionReturnValue = false;
                }
                else
                {
                    drow["StaffID"] = cmbstaffID.SelectedValue;
                }
                if (string.IsNullOrEmpty(txtteachername.Text))
                {
                    MessageBox.Show("Field cant be empty");
                    txtteachername.Focus();
                    FunctionReturnValue = false;
                }
                else
                {
                    drow["StaffName"] = txtteachername.Text;
                }
                if (string.IsNullOrEmpty(cmbsubject.Text))
                {
                    MessageBox.Show("Select a subject");
                    cmbsubject.Focus();
                    FunctionReturnValue = false;
                }
                else
                {
                    drow["Subject"] = cmbsubject.SelectedValue;
                }
                DSLoadData.Tables[0].Columns["TeacherID"].AllowDBNull = true;
                DSLoadData.Tables[0].Rows.Add(drow);

                SqlCommand cmdinsert = new SqlCommand();
                cmdinsert.Connection = CON;
                DAA.InsertCommand = cmdinsert;
                cmdinsert.CommandText = "insert into Teacher_Allocation(StaffID,Class_Name,Section,Subject,StaffName)VALUES(@StaffID,@Class_Name,@Section,@Subject,@StaffName)";
                cmdinsert.Parameters.Add("@StaffID", SqlDbType.Int, 50, "StaffID");
                cmdinsert.Parameters.Add("@Class_Name", SqlDbType.Int, 50, "Class_Name");
                cmdinsert.Parameters.Add("@Section", SqlDbType.Int, 50, "Section");
                cmdinsert.Parameters.Add("@Subject", SqlDbType.Int, 50, "Subject");
                cmdinsert.Parameters.Add("@StaffName", SqlDbType.VarChar, 100, "StaffName");

                DAA.Update(DSLoadData.Tables[0]);
                MessageBox.Show("Records Saved Succesfully");

                SqlCommand CCCMM = new SqlCommand("select * from Teacher_Allocation ", CON);
                DSLoadData.Clear();
                DAA.Fill(DSLoadData, "Teacher_Allocation");
                ultraGrid1.DataSource = null;
                ultraGrid1.DataSource = DSLoadData.Tables[0].DefaultView;

                FunctionReturnValue = true;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return FunctionReturnValue;
        }
        private void frmteacherallocation_Load(object sender, EventArgs e)
        {
            MProcLoad("all");
            CMD = new SqlCommand("select * from Teacher_Allocation ", CON);
            DAA = new SqlDataAdapter(CMD);
            DAA.Fill(DSLoadData, "Teacher_Allocation");
            ultraGrid1.DataSource = DSLoadData.Tables[0].DefaultView;
          //LODADATA();

        }
        //SqlCommand cmdLoad;
        //DataSet DSload;
        //SqlDataAdapter DALoad;
        private void LODADATA()
        {
            clsPopulateList Populate = new clsPopulateList();
            DataSet dt = new DataSet();
            dt=GV.LoadDataset("select ClassID,ClassName from Class_Master");
            cmbclassname.DataSource = dt.Tables[0];
            cmbclassname.DisplayMember = "ClassName";
            cmbclassname.ValueMember = "ClassID";
            dt.Clear();
            dt = GV.LoadDataset("select SubjectID,SubjectName from Subject_Master");
            cmbsubject.DataSource = dt.Tables[0];
            cmbsubject.DisplayMember = "SubjectName";
            cmbsubject.ValueMember = "SubjectID";
            dt.Clear();
            dt = GV.LoadDataset("select SectionID,SectionName from Section_Master");
            cmbSection.DataSource = dt.Tables[0];
            cmbSection.DisplayMember = "SectionName";
            cmbSection.ValueMember = "SectionID";
            dt.Clear();
            dt = GV.LoadDataset("select StaffID,Staffname from Staff_Details");
            cmbstaffID.DataSource = dt.Tables[0];
            cmbstaffID.DisplayMember = "StaffID";
            cmbstaffID.ValueMember = "Staffname";

        }
        public bool MProcLoad(string names)
        {
            clsPopulateList CPopulate = new clsPopulateList();
            bool result = false;
            switch (names)
            {
                case "all":
                    CPopulate.Load_Combo(cmbclassname, "Class_Master", "ClassName", "ClassID", "");
                    cmbclassname.DisplayMember = "ClassName";
                    cmbclassname.ValueMember = "ClassID";
                    CPopulate.Load_Combo(cmbsubject, "Subject_Master", "SubjectName", "SubjectID", "");
                    CPopulate.Load_Combo(cmbSection, "Section_Master", "SectionName", "SectionID", "");
                    cmbSection.DisplayMember = "SectionName";
                    cmbSection.ValueMember = "SectionID";
                    CPopulate.Load_Combo(cmbstaffID, "Staff_Details", "StaffID", "Staffname", "");
                    break;
            }
            return result;
        }

        private void cmbstaffID_SelectedIndexChanged(object sender, EventArgs e)
        {
            GblDefinitions GV = new GblDefinitions();
            txtteachername.Text =GV.gfnGetName("select staffname from Staff_Details where StaffID='"+ cmbstaffID.Text+"'");

        }


    }
}

====================================================================
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.VisualBasic;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace ESchool
{
    public class clsPopulateList
    {
        GblDefinitions GV = new GblDefinitions();
        GblDeclarations GD = new GblDeclarations();

        public IList<clsList> gfnLoadCombo(string tblName, string DisplayTextCol, string DisplayKeyCol, string strQuery )
        {
            strQuery = null;
            List<clsList> CmboList = new List<clsList>();
            string strSQL = null;
            SqlCommand cmdCombo = new SqlCommand();
            SqlDataReader RdrCombo = default(SqlDataReader);
            try {
                if (strQuery == null) {
                    strSQL = " SELECT " + DisplayTextCol + "," + DisplayKeyCol + " FROM " + tblName + " ORDER BY " + DisplayTextCol;
                } else {
                    strSQL = strQuery;
                }
                cmdCombo.CommandText = strSQL;
                cmdCombo.Connection = GV.GetConnection();
                if (GD.gblTransBegin)
                    cmdCombo.Transaction = GD.gblSQLTrans;
                RdrCombo = cmdCombo.ExecuteReader();
                if (RdrCombo.HasRows)
                {
                    while (RdrCombo.Read())
                    {
                        CmboList.Add(new clsList(RdrCombo[0].ToString(),Convert.ToInt32( RdrCombo[1].ToString())));
                    }
                }
                RdrCombo.Close();
            } catch (Exception ex) {
                GV.gprocErrorHandler(ex.Source, ex.Message, "gfnLoadCombo", "clsPopulateList");
            }
            return (CmboList);
        }
        public void Load_Combo(ComboBox objCombo, string TableName , string DisplayTextColumn , string CellDataColumn , string sqlStmt )
        {

            //CellDataColumn = "";
            //sqlStmt = "";
            //TableName = "";
            // DisplayTextColumn = "";
            string strSQL = null;
            ArrayList ListItem = new ArrayList();
            SqlCommand cmdCombo = new SqlCommand();
            SqlDataReader RdrCombo ;//= default(SqlDataReader);
            try
            {
                if (string.IsNullOrEmpty(Strings.Trim(DisplayTextColumn)))
                {
                    DisplayTextColumn = CellDataColumn;
                }
                else if (string.IsNullOrEmpty(Strings.Trim(CellDataColumn)))
                {
                    CellDataColumn = DisplayTextColumn;
                }

                if (string.IsNullOrEmpty(Strings.Trim(sqlStmt)))
                {
                    strSQL = " SELECT " + DisplayTextColumn + "," + CellDataColumn + " FROM " + TableName + " ORDER BY " + DisplayTextColumn;
                }
                else
                {
                    strSQL = sqlStmt;
                }
                cmdCombo.CommandText = strSQL;
                cmdCombo.Connection = GV.GetConnection();
                if (GD.gblTransBegin)
                    cmdCombo.Transaction = GD.gblSQLTrans;
                RdrCombo = cmdCombo.ExecuteReader();
                if (RdrCombo.HasRows)
                {
                    while (RdrCombo.Read())
                    {
                        //ListItem.Add(new clsList(RdrCombo[0].ToString(),Convert.ToInt32(RdrCombo[1].ToString())));
                        objCombo.Items.Add(RdrCombo[0].ToString());
                    }
                }
                RdrCombo.Close();

                //if (ListItem.Count > 0)
                //{
                //    objCombo.DataSource = ListItem;
                //    objCombo.DisplayMember = "GetName";
                //    objCombo.ValueMember = "GetID";
                //}
                objCombo.SelectedIndex = -1;
            }
            catch (Exception ex)
            {
                GV.gprocErrorHandler(ex.Source, ex.Message, "Load_Combo", "clsPopulateList");
            }
        }
        public void Load_GridCombo(ref DataGridViewComboBoxColumn objCombo, string TableName , string DisplayTextColumn , string CellDataColumn , string sqlStmt )
        {
            string strSQL = null;
            TableName = "";
            DisplayTextColumn = "";
            CellDataColumn = "";
            sqlStmt = "";
            ArrayList ListItem = new ArrayList();
            SqlCommand cmdCombo = new SqlCommand();
            SqlDataReader RdrCombo = default(SqlDataReader);
            try {
                if (string.IsNullOrEmpty(Strings.Trim(DisplayTextColumn)))
                {
                    DisplayTextColumn = CellDataColumn;
                } else if (string.IsNullOrEmpty(Strings.Trim(CellDataColumn)))
                {
                    CellDataColumn = DisplayTextColumn;
                }

                if (string.IsNullOrEmpty(Strings.Trim(sqlStmt)))
                {
                    strSQL = " SELECT " + DisplayTextColumn + "," + CellDataColumn + " FROM " + TableName + " ORDER BY " + DisplayTextColumn;
                }
                else
                {
                    strSQL = sqlStmt;
                }
                cmdCombo.CommandText = strSQL;
                cmdCombo.Connection = GV.GetConnection();
                if (GD.gblTransBegin)
                    cmdCombo.Transaction = GD.gblSQLTrans;
                RdrCombo = cmdCombo.ExecuteReader();
                if (RdrCombo.HasRows)
                {
                    while (RdrCombo.Read())
                    {
                        objCombo.Items.Add(RdrCombo[0].ToString());
                        //ListItem.Add(New clsList(RdrCombo(0).ToString, RdrCombo(1).ToString))
                    }
                }
                RdrCombo.Close();

            //If ListItem.Count > 0 Then
            //    objCombo.DataSource = ListItem
            //    objCombo.DisplayMember = "GetName"
            //    objCombo.ValueMember = "GetID"
            //End If
            } catch (Exception ex)
            {
                GV.gprocErrorHandler(ex.Source, ex.Message, "Load_GridCombo", "clsPopulateList");
            }
        }
        public void Load_GridItemCombo(ref DataGridViewComboBoxColumn objCombo, string TableName, string DisplayTextColumn1, string DisplayTextColumn2, string CellDataColumn, string sqlStmt)
        {
             sqlStmt = "";
            CellDataColumn = "";
            DisplayTextColumn2 = "";
            DisplayTextColumn1 = "";
            TableName = "";
            string strSQL = null;
            ArrayList ListItem = new ArrayList();
            SqlCommand cmdCombo = new SqlCommand();
            SqlDataReader RdrCombo ;//= default(SqlDataReader);
            try {
                if (string.IsNullOrEmpty(Strings.Trim(DisplayTextColumn1)))
                {
                    DisplayTextColumn1 = CellDataColumn;
                }
                else if (string.IsNullOrEmpty(Strings.Trim(CellDataColumn)))
                {
                    CellDataColumn = DisplayTextColumn1;
                }

                if (string.IsNullOrEmpty(Strings.Trim(sqlStmt)))
                {
                    if (string.IsNullOrEmpty(DisplayTextColumn2))
                    {
                        strSQL = " SELECT " + DisplayTextColumn1 + "," + CellDataColumn + " FROM " + TableName + " ORDER BY " + DisplayTextColumn1;
                    }
                    else
                    {
                        strSQL = " SELECT " + DisplayTextColumn1 + "," + DisplayTextColumn2 + "," + CellDataColumn + " FROM " + TableName + " ORDER BY " + DisplayTextColumn1;
                    }
                }
                else
                {
                    strSQL = sqlStmt;
                }
                cmdCombo.CommandText = strSQL;
                cmdCombo.Connection = GV.GetConnection();
                if (GD.gblTransBegin)
                    cmdCombo.Transaction = GD.gblSQLTrans;
                RdrCombo = cmdCombo.ExecuteReader();
                if (RdrCombo.HasRows)
                {
                    while (RdrCombo.Read())
                    {
                        if (string.IsNullOrEmpty(DisplayTextColumn2))
                        {
                            ListItem.Add(new clsList(RdrCombo[0].ToString(), Convert.ToInt32(RdrCombo[1].ToString())));
                        }
                        else
                        {
                            ListItem.Add(new clsList(RdrCombo[0].ToString()+ Constants.vbCr +Convert.ToInt32(RdrCombo[1].ToString()),Convert.ToInt32( RdrCombo[2].ToString())));
                        }
                    }
                }
                RdrCombo.Close();

                if (ListItem.Count > 0)
                {
                    objCombo.DataSource = ListItem;
                    objCombo.DisplayMember = "GetName";
                    objCombo.ValueMember = "GetID";
                }
            } catch (Exception ex)
            {
                GV.gprocErrorHandler(ex.Source, ex.Message, "Load_GridCombo", "clsPopulateList");
            }
        }

        //Not used anywhere
        public void Load_GridComboWithDS(ref DataGridViewComboBoxColumn objCombo, string TableName , string DisplayTextColumn , string CellDataColumn, string sqlStmt)
        {
            sqlStmt = "";
            CellDataColumn = "";
            DisplayTextColumn = "";
            TableName = "";
            string strSQL = null;
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            try {
                if (string.IsNullOrEmpty(Strings.Trim(DisplayTextColumn))) {
                    DisplayTextColumn = CellDataColumn;
                } else if (string.IsNullOrEmpty(Strings.Trim(CellDataColumn))) {
                    CellDataColumn = DisplayTextColumn;
                }

                if (string.IsNullOrEmpty(Strings.Trim(sqlStmt))) {
                    strSQL = " SELECT " + DisplayTextColumn + "," + CellDataColumn + " FROM " + TableName + " ORDER BY " + DisplayTextColumn;
                } else {
                    strSQL = sqlStmt;
                }
                SqlCommand cmdCombo = new SqlCommand(strSQL, GV.GetConnection());
                if (GD.gblTransBegin)
                    cmdCombo.Transaction = GD.gblSQLTrans;
                da.SelectCommand = cmdCombo;
                da.Fill(ds);

                objCombo.DataSource = ds.Tables[0];
                objCombo.DisplayMember = ds.Tables[0].Columns[0].ToString();
                objCombo.ValueMember = ds.Tables[0].Columns[1].ToString();
            } catch (Exception ex)
            {
                GV.gprocErrorHandler(ex.Source, ex.Message, "Load_GridComboWithDS", "clsPopulateList");
            }
        }

        //Not used anywhere, can be used to load Item Location
        public void Load_GridComboCell(ref DataGridViewComboBoxCell objCombo, string TableName , string DisplayTextColumn, string CellDataColumn, string sqlStmt)
        {
            CellDataColumn = "";
            sqlStmt = "";
            DisplayTextColumn = "";
            TableName = "";
            string strSQL = null;
            ArrayList ListItem = new ArrayList();
            SqlCommand cmdCombo = new SqlCommand();
            SqlDataReader RdrCombo ;//= default(SqlDataReader);
            try {
                if (string.IsNullOrEmpty(Strings.Trim(DisplayTextColumn)))
                {
                    DisplayTextColumn = CellDataColumn;
                } else if (string.IsNullOrEmpty(Strings.Trim(CellDataColumn)))
                {
                    CellDataColumn = DisplayTextColumn;
                }

                if (string.IsNullOrEmpty(Strings.Trim(sqlStmt)))
                {
                    strSQL = " SELECT " + DisplayTextColumn + "," + CellDataColumn + " FROM " + TableName + " ORDER BY " + DisplayTextColumn;
                }
                else
                {
                    strSQL = sqlStmt;
                }
                cmdCombo.CommandText = strSQL;
                cmdCombo.Connection = GV.GetConnection();
                if (GD.gblTransBegin)
                    cmdCombo.Transaction = GD.gblSQLTrans;
                RdrCombo = cmdCombo.ExecuteReader();
                if (RdrCombo.HasRows)
                {
                    while (RdrCombo.Read())
                    {
                        ListItem.Add(new clsList(RdrCombo[0].ToString(),Convert.ToInt32( RdrCombo[1].ToString())));
                    }
                }
                RdrCombo.Close();

                if (ListItem.Count > 0)
                {
                    objCombo.DataSource = ListItem;
                    objCombo.DisplayMember = "GetName";
                    objCombo.ValueMember = "GetID";
                }
            } catch (Exception ex)
            {
                GV.gprocErrorHandler(ex.Source, ex.Message, "Load_GridComboCell", "clsPopulateList");
            }
        }

    }
}
================================================================================
Teacher Allocation Table
USE [SchoolManagement]
GO
/****** Object: Table [dbo].[Teacher_Allocation]    Script Date: 06/01/2011 17:35:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Teacher_Allocation](
    [StaffID] [int] IDENTITY(1,1) NOT NULL,
    [Class_Name] [varchar](50) NOT NULL,
    [Section] [int] NOT NULL,
    [Subject] [int] NOT NULL,
    [Teacher_Name] [varchar](100) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Suthish Nair · Answer

I prefer code as attachment file instead of copy pasting :)

Sam Hobbs · Answer

Do most people really look at code that is uploaded in a zip file? I think the ideal solution is to post just the relevant code. Sometimes it is difficult to know what code is relevant but I would prefer that the first priority should be to try to do that.

raghu v · Answer

can u describe ur database table structure and the query it would be easy to decide whether the problem with code or db structure

Sam Hobbs · Answer

And I am saying that some people just won't look at an attachment at all. I am not saying it has to be done one way but you are saying that.

Suthish Nair · Answer

I never said need whole code to be attached. Some members will not read these long threads will just get avoided. So it will be better to upload attachments. Sometimes we need to see all the page to check exactly why the error comes. Same above example he is getting error from code-behind but he also posted sql scripts. so we need to check both. This how forums works. Sometimes single line of code helps to identify the bug or sometimes we need the whole page.

Sam Hobbs · Answer

So Suthish are you saying you prefer to see a large amount of code to look at? Yes an attachment is better if the code is large but I am saying that small code is better. I am also saying that not everyone is like you and that there are some people that are more likely to help if the amount of code is minimized and not attached. For me I never look at code when it is in an attachment without an adequate explanation of what it is and where to look. I think it would be best to discuss what is best.

Suthish Nair · Answer

do not copy paste whole code nobody have time to read all the thread. you can upload the code as attachment.

Suthish Nair · Answer

You used NOT NULL for each columns in DB table and trying to insert null values. Thus error showing up!

Amit Choudhary · Answer

Hi Friend I think your Table that you are using to bind the combo is having null values. Try to handle them in your SQL using the COALESCE or ISNULL statement. for e.g. SELECT COALESCE(Product ProductDescription Product 'Missing Info') FROM SalesHistory SELECT ProductType = ISNULL(Product'Unknown') + ' Sold:'  COUNT(*) AS ProductCount FROM SalesHistory This error also may arise if you are trying to insert the null values in database. Debug your insert statements see if any null values is coming in any of your parameters. Hope the above will help you. Thanks.

Muralidharan Deenathayalan · Answer

In which method you're getting the error .It is very hard to find out by just looking the code.

Getting an error(cannot insert null values,use dbnull instead)combobox taking null values.