How to save null value into a Column in Database

Question

4.00/5 (1 vote)

See more:

Hi,

In My database there are few columns as

Empid(Pk,int,not null)
Deptid (int, null),
DeptName(varchar(50),null),
Location(varchar(50),null)

From the front end..I am displaying a combobox where the Display member is DeptName and ValueMember is DeptID.Combobox intial value is --Select--

So when user doesn't select any item from the combobox I want to insert/update database columns with null value.

I tried to do it by converting the selectedvalue to int and when the user doesn't select an item.It throws an error as it is null.

So how to insert null.

Posted 24-Dec-12 23:57pm

Prathap Gangireddy

Add a Solution

2 solutions

Solution 1

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls562.htm[^]

Posted 25-Dec-12 0:00am

Krunal Rohit

Comments

Prathap Gangireddy 25-Dec-12 6:03am

Hi Krunal,

Thanks for the reply, but my problem is I am using a stored procedure for Inserting/Updating the table in Nlayer application.

objEmployeeDetailsMaster.DeptID = Convert.ToInt32(cmbDepartmenttype.SelectedValue.ToString());

As you can see when I am trying to get the selectedvalue it throws an error as I am not selecting an item.

Can u please help me with this.

[no name] 25-Dec-12 6:10am

Okay, check one condition that cmbDepartmenttype.SelectedIndex==0 then, your desire code....

Prathap Gangireddy 25-Dec-12 6:13am

Actually it is not compulsory that user must select a value.So when slectedindex=0 what shud I do in order to store null value into database.Only option I got is set the value=0 when selectedindex=0 so that we can insert 0 in the database.

[no name] 25-Dec-12 6:16am

You are not getting me, see
if(cmbDepartmenttype.SelectedIndex==0)
{
// write query for inserting NULL
}
else
{
//query containing your selected item.
}

Prathap Gangireddy 25-Dec-12 6:21am

Another problem is..i have just asked you a sample of code.In actualy application there are many comboboxes which have Display nd Value member.So finally all the selected data is entered into a Master table.How can I do this.

[no name] 25-Dec-12 6:26am

I can't provide you entire code.. you need to do it by your own.. Make sure that, if you've got some doubt, ask me but code..

Prathap Gangireddy 25-Dec-12 6:32am

I am done with the entire code...only error is when user doesn't select an item.

Anywaz I thought of using a nullable type while creating entities i.e. in Model layer like below for all int properties

public int? Deptid{get;set;}.

By doing this..there is no error in the UI when used Convert.ToInt32.

But in the business layer when I send data to Datalayer method(InsertUpdateEmployeeMasterDetails) it throws an error like cannot convert from 'int?' to 'int'. So is there any solution for this

if(DBManager.InsertUpdateEmployeeMasterDetails(EDMData.EmpID,EDMData.EmpNo,EDMData.Emptitleid, EDMData.Firstname,EDMData.Surname,EDMData.DOB,EDMData.Age,EDMData.GenderID,EDMData.Address1, EDMData.Address2,EDMData.City,EDMData.State,EDMData.CountryID, EDMData.Phone1,EDMData.Phone2,EDMData.Fax1,EDMData.Fax2,EDMData.Mobile1,EDMData.Mobile2, EDMData.Email,EDMData.Empwebsite,EDMData.DateofJoin,EDMData.Dateleft,EDMData.EmpstatusID, EDMData.EmpTypeID,EDMData.EmpEthnicOriginID,EDMData.DeptID,EDMData.DesignationID,EDMData.LocationGroupId, EDMData.LocaionID,EDMData.ReporttoempID,EDMData.ReportingAuthorityempid,EDMData.RecruitmentTypeID, EDMData.EmpImagepath,EDMData.Comments,EDMData.Company_ID) > 0)

[no name] 25-Dec-12 6:35am

See solution #2.. :)

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gagan sawaliya · Accepted Answer · 2012-12-25T00:27:00

Solution 2

check combobox value and if it's Value is '--Select--' then
use Convert.DBNull instead of combobox.Value

Posted 25-Dec-12 0:27am

gagan sawaliya

Comments

Prathap Gangireddy 25-Dec-12 6:36am

Hi Gagan,

thanks for the reply.Can you please elaborate on this.

gagan sawaliya 25-Dec-12 7:00am

pls post your code

Prathap Gangireddy 25-Dec-12 7:10am

Below is my SaveButton code in UI which inserts the values into DB

private void btnEmpDSave_Click(object sender, EventArgs e)
{

Restaurant.Entities.EmployeeDetailsMaster objEmployeeDetailsMaster = new Restaurant.Entities.EmployeeDetailsMaster();

if (DateofJoining.Value <= DateofLeaving.Value)
{
if (btnEmpDSave.Text.Equals("Save"))
{
lblEmpID.Text = "0";
}
objEmployeeDetailsMaster.EmpID = Convert.ToInt32(lblEmpID.Text);
objEmployeeDetailsMaster.Address1 = txtAddress1.Text.Trim();
objEmployeeDetailsMaster.Address2 = txtAddress2.Text.Trim();
objEmployeeDetailsMaster.Age = Convert.ToInt32(txtAge.Text.ToString());
objEmployeeDetailsMaster.City = txtcity.Text.Trim();
objEmployeeDetailsMaster.Comments = txtComments.Text.Trim();
objEmployeeDetailsMaster.Company_ID = GlobalData.CompanyID;
objEmployeeDetailsMaster.CountryID = Convert.ToInt32(cmbCountry.SelectedValue.ToString());
objEmployeeDetailsMaster.Dateleft = DateofLeaving.Value;
objEmployeeDetailsMaster.DateofJoin = DateofJoining.Value;
objEmployeeDetailsMaster.DeptID = Convert.ToInt32(cmbDepartmenttype.SelectedValue.ToString());
objEmployeeDetailsMaster.DesignationID = Convert.ToInt32(cmbDesignation.SelectedValue.ToString());
objEmployeeDetailsMaster.DOB = DOBdatetimepicker.Value;
objEmployeeDetailsMaster.Email = txtEmail.Text.Trim();
objEmployeeDetailsMaster.EmpEthnicOriginID = Convert.ToInt32(cmbEthnicOrigin.SelectedValue.ToString());
objEmployeeDetailsMaster.EmpImagepath = string.Empty;
objEmployeeDetailsMaster.EmpNo = txtEmpNO.Text.Trim();
objEmployeeDetailsMaster.EmpstatusID = Convert.ToInt32(cmbEmpStatus.SelectedValue.ToString());
objEmployeeDetailsMaster.Emptitleid = Convert.ToInt32(cmbEmptitle.SelectedValue.ToString());
objEmployeeDetailsMaster.EmpTypeID = Convert.ToInt32(cmbEmpType.SelectedValue.ToString());
objEmployeeDetailsMaster.Empwebsite = string.Empty;
objEmployeeDetailsMaster.Fax1 = txtFax1.Text.Trim();
objEmployeeDetailsMaster.Fax2 = txtFax2.Text.Trim();
objEmployeeDetailsMaster.Firstname = txtFirstName.Text.Trim();
objEmployeeDetailsMaster.GenderID = Convert.ToInt32(cmbGenderType.SelectedValue.ToString());
objEmployeeDetailsMaster.LocationID = Convert.ToInt32(cmbLocation.SelectedValue.ToString());
objEmployeeDetailsMaster.LocationGroupId = Convert.ToInt32(cmbLocationGroup.SelectedValue.ToString());
objEmployeeDetailsMaster.Mobile1 = txtMobile1.Text.Trim();
objEmployeeDetailsMaster.Mobile2 = txtMobile2.Text.Trim();
objEmployeeDetailsMaster.Phone1 = txtPhone1.Text.Trim();
objEmployeeDetailsMaster.Phone2 = txtPhone2.Text.Trim();
objEmployeeDetailsMaster.RecruitmentTypeID = Convert.ToInt32(cmbRecruitmenttype.SelectedValue.ToString());
objEmployeeDetailsMaster.ReportingAuthorityempid = Convert.ToInt32(cmbEmpRAuthority.SelectedValue.ToString());
objEmployeeDetailsMaster.ReporttoempID = Convert.ToInt32(cmbEmpReport.SelectedValue.ToString());
objEmployeeDetailsMaster.State = txtState.Text.Trim();
objEmployeeDetailsMaster.Surname = txtSurName.Text.Trim();

using (EmployeeMasterBusiness objEmployeeMasterBusiness = new EmployeeMasterBusiness())
{
if (objEmployeeMasterBusiness.SaveRUpdateEmpMasterDetails(objEmployeeDetailsMaster))
{
MessageBox.Show("Employee details created/

gagan sawaliya 25-Dec-12 7:29am

if (cmbDepartmenttype.SelectedValue == "--Select--")
{
objEmployeeDetailsMaster.DeptID = -1;
}
else
{
objEmployeeDetailsMaster.DeptID = Convert.ToInt32(cmbDepartmenttype.SelectedValue.ToString());
}

IN save method objEmployeeMasterBusiness.SaveRUpdateEmpMasterDetails(objEmployeeDetailsMaster)

check the value of objEmployeeDetailsMaster.DeptID if it's value is -1 then use Convert.DBNull else use objEmployeeDetailsMaster.DeptID

Prathap Gangireddy 25-Dec-12 8:46am

Hi Gagan,

The only i didn't understand is how to use Convert.DBNULL.

I had used in the Business layer method i.e SaveRUpdateEmpMasterDetails as below but it throws error

if (EDMData.DeptID== -1)
EDMData.DeptID= Convert.DBNull;

Can you please help me with this

gagan sawaliya 26-Dec-12 0:08am

use this condition in data layer i.e. where you have insert, update queries

Prathap Gangireddy 26-Dec-12 1:45am

BUt by using this condition it throws and error becos id Convert.DBNull is object type and DeptID is int

gagan sawaliya 26-Dec-12 2:06am

post your insert update query

Prathap Gangireddy 26-Dec-12 2:11am

This is the method in Business layer

public bool SaveRUpdateEmpMasterDetails(EmployeeDetailsMaster EDMData)
{

bool flgSuccess = false;
try
{
using (DBEmployeeTableManager DBManager = new DBEmployeeTableManager())
{
if (DBManager.InsertUpdateEmployeeMasterDetails(EDMData.EmpID,EDMData.EmpNo,EDMData.Emptitleid,
EDMData.FirstName,EDMData.Surname,EDMData.DOB,EDMData.Age,EDMData.GenderID,EDMData.Address1,
EDMData.Address2,EDMData.City,EDMData.State,EDMData.CountryID,
EDMData.Phone1,EDMData.Phone2,EDMData.Fax1,EDMData.Fax2,EDMData.Mobile1,EDMData.Mobile2,
EDMData.Email,EDMData.empwebsite,EDMData.DateofJoin,EDMData.Dateleft,EDMData.EmpstatusID,
EDMData.EmpTypeID,EDMData.EmpEthnicOriginID,EDMData.DeptID,EDMData.DesignationID,EDMData.LocationGroupId,
EDMData.LocationID,EDMData.ReporttoempID,EDMData.reportingauthorityempid,EDMData.RecruitmentTypeID,
EDMData.empimagepath,EDMData.Comments,EDMData.Company_ID,EDMData.EmpNationalityID) > 0)
{
flgSuccess = true;
}
}
}
catch (Exception ex)
{

}
return flgSuccess;

Prathap Gangireddy 26-Dec-12 2:12am

Below is the code in DataLayer

public int InsertUpdateEmployeeMasterDetails(int EmpID, string EmpNo, int Emptitleid, string Firstname,string Surname, DateTime DOB, int Age, int GenderID, string Address1, string Address2,string City,
string State, int CountryID, string Phone1, string Phone2, string Fax1,string Fax2, string Mobile1,string Mobile2, string Email, string Empwebsite, DateTime DOJ,DateTime Dateleft,
int EmpstatusID, int EmpTypeID, int EmpEthnicOriginID, int DeptID, int DesignationID,int LocationGroupId, int LocationID, int ReporttoempID, int ReportingAuthorityempid,int RecruitmentTypeID,
string EmpImagepath, string Comments, int Company_ID,int NationalityID)

{
int iEffectedRows = 0;
try
{
DbCommand dbInstUpdtCommand = dbRestaurant.GetStoredProcCommand("Emp_master_SaveEmployeeDetails");
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@EmpID", DbType.Int32, EmpID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@EmpNo", DbType.String, EmpNo);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Emptitleid", DbType.Int32, Emptitleid);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Firstname", DbType.String, Firstname);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Surname", DbType.String, Surname);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@DOB", DbType.Date, DOB);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Age", DbType.Int32, Age);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@GenderID", DbType.Int32, GenderID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Address1", DbType.String, Address1);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Address2", DbType.String, Address2);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@City", DbType.String, City);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@State", DbType.String, State);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@CountryID", DbType.Int32, CountryID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Phone1", DbType.String, Phone1);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Phone2", DbType.String, Phone2);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Fax1", DbType.String, Fax1);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Fax2", DbType.String, Fax2);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Mobile1", DbType.String, Mobile1);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Mobile2", DbType.String, Mobile2);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Email", DbType.String, Email);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Empwebsite", DbType.String, Empwebsite);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@DOJ", DbType.DateTime, DOJ);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Dateleft", DbType.DateTime, Dateleft);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@EmpstatusID", DbType.Int32, EmpstatusID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@EmpTypeID", DbType.Int32, EmpTypeID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@EmpEthnicOriginID", DbType.Int32, EmpEthnicOriginID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@DeptID", DbType.Int32, DeptID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@DesignationID", DbType.Int32, DesignationID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@LocationGroupId", DbType.Int32, LocationGroupId);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@LocationID", DbType.Int32, LocationID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Re

Prathap Gangireddy 26-Dec-12 2:12am

dbRestaurant.AddInParameter(dbInstUpdtCommand, "@LocationID", DbType.Int32, LocationID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@ReporttoempID", DbType.Int32, ReporttoempID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@ReportingAuthorityempid", DbType.Int32, ReportingAuthorityempid);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@RecruitmentTypeID", DbType.Int32, RecruitmentTypeID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@EmpImagepath", DbType.String, EmpImagepath);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Comments", DbType.String, Comments);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@Company_ID", DbType.Int32, Company_ID);
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@EmpNationalityID", DbType.Int32, NationalityID);
iEffectedRows = dbRestaurant.ExecuteNonQuery(dbInstUpdtCommand);
}
catch (Exception ex)
{

}
return iEffectedRows;

gagan sawaliya 26-Dec-12 2:37am

if (DeptID == -1)
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@DeptID", DbType.Int32, Convert.DBNull);
else
dbRestaurant.AddInParameter(dbInstUpdtCommand, "@DeptID", DbType.Int32, DeptID);

Prathap Gangireddy 26-Dec-12 23:44pm

Hi Gagan,

I will try your code and let u know.

Thanks...

Prathap Gangireddy 27-Dec-12 12:44pm

Hi Gagan,

Dude..you are just awesome..it works perfectly..i didn't knew this was so simple..nywaz thanks man.

How to save null value into a Column in Database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to save null value into a Column in Database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us