Inserted Null values into SQL table

Question

i have a windows application form and i want to insert the text box values into sql database, some of them are mandatory others are allowed to insert null values. But i cant insert null values(in the case of int) into database.

 CREATE TABLE [dbo].[tblReg](
[Reg_ID] [int] IDENTITY(1,1) NOT NULL,
[Sep_Status] [varchar](50) NOT NULL,
[Pop_Grp] [varchar](50) NOT NULL,
[Child_Address] [varchar](50) NULL,
[Child_AgeYr] [int] NULL

) ON [PRIMARY]

These are my c# code

private void Children_Save_Click(object sender, EventArgs e)
    {
        ss = SepStat.Text.ToString().Trim();
        pg = PopGr.Text.ToString().Trim();
        Add = ChildAdd.Text.ToString().Trim();
        yr = Convert.ToInt32(ChildDOBYr.Text); 
        createdid=SaveChilDetails(ss,pg,Add,yr);

    }

   public int SaveChilDetails(string ss, string pg,string Add,int yr)
    {
       string constring =Config.GetConnection();
       using (SqlConnection con=new SqlConnection(constring))
       {
           using (SqlCommand cmd = new SqlCommand("Insert into  tblReg(Sep_Status,Pop_Grp,Child_Address,Child_AgeYr) output INSERTED.Reg_ID values(@ss,@pg,@Add,@yr)", con))
           {
               cmd.Parameters.AddWithValue("@ss", ss);
               cmd.Parameters.AddWithValue("@pg", pg);
               if (Add == "")                                   //Problems are here
               { cmd.Parameters.AddWithValue("@add", null); }
               else
               { cmd.Parameters.AddWithValue("@Add", Add); }
               if(yr==0)
               {cmd.Parameters.AddWithValue("@yr", null);}
               else
               {cmd.Parameters.AddWithValue("@yr", yr);}       //***********************
               if (con.State != ConnectionState.Open)
               con.Open();
               int createid = (int)cmd.ExecuteScalar();
               if (con.State == System.Data.ConnectionState.Open) con.Close();
               return createid;
           }
       }
    }

why do u have to pass null when by default null will be taken ? — Rat-a-tat-a-tat Ratatouille, Mar 19 '14 at 05:26
Null and DBNull.Value both are different. If you are trying to set null value in sql parameter then you should try DBNull.Value instead of NULL. — Shell, Mar 19 '14 at 06:40

Vignesh Kumar A · Answer 1 · 2014-03-19T05:48:27.180

You can use NULLIF in your query

NULLIF

Try this

public int SaveChilDetails(string ss, string pg,string Add,int yr)
    {
       string constring =Config.GetConnection();
       using (SqlConnection con=new SqlConnection(constring))
       {
           using (SqlCommand cmd = new SqlCommand("Insert into  tblReg(Sep_Status,Pop_Grp,Child_Address,Child_AgeYr) output INSERTED.Reg_ID values(@ss,@pg,NULLIF(@Add,''),NULLIF(@yr,0))", con))
           {
               cmd.Parameters.AddWithValue("@ss", ss);
               cmd.Parameters.AddWithValue("@pg", pg);
               cmd.Parameters.AddWithValue("@Add", Add);
               cmd.Parameters.AddWithValue("@yr", yr);
               if (con.State != ConnectionState.Open)
               con.Open();
               int createid = (int)cmd.ExecuteScalar();
               if (con.State == System.Data.ConnectionState.Open) con.Close();
               return createid;
           }
       }
    }

Edit

you are Getting error: Input string was not in a correct format.

you need to use Int.TryParse

Try this too

 public int SaveChilDetails(string ss, string pg,string Add,int yr)
        {
           string constring =Config.GetConnection();              
           using (SqlConnection con=new SqlConnection(constring))
           {
               using (SqlCommand cmd = new SqlCommand("Insert into  tblReg(Sep_Status,Pop_Grp,Child_Address,Child_AgeYr) output INSERTED.Reg_ID values(@ss,@pg,NULLIF(@Add,''),NULLIF(@yr,0))", con))
               {
                   cmd.Parameters.AddWithValue("@ss", ss);
                   cmd.Parameters.AddWithValue("@pg", pg);
                   cmd.Parameters.AddWithValue("@Add", Add);
                   cmd.Parameters.AddWithValue("@yr", yr);                      
                   if (con.State != ConnectionState.Open)
                   con.Open();
                   int createid = (int)cmd.ExecuteScalar();
                   if (con.State == System.Data.ConnectionState.Open) con.Close();
                   return createid;
               }
           }
        }

Update

Try this

private void Children_Save_Click(object sender, EventArgs e)
    {
        int result=0;
        ss = SepStat.Text.ToString().Trim();
        pg = PopGr.Text.ToString().Trim();
        Add = ChildAdd.Text.ToString().Trim();
         if(int.TryParse(ChildDOBYr.Text,out result))
         {
            yr=ChildDOBYr.Text;
         }
         else
         {
           yr=result;
         }
        createdid=SaveChilDetails(ss,pg,Add,yr);

    }

And What about yr = Convert.ToInt32(ChildDOBYr.Text); still error ocured here — neel, Mar 19 '14 at 05:44
@neel I have updated that too...please take a look at my updation — Vignesh Kumar A, Mar 19 '14 at 05:48

score 2 · Answer 2 · answered Mar 19 '14 at 05:34

You need to understand that null (used in programming) is different from the Database value null.

if you want to insert Null value into database table you need to use DBNull.Value

Try This:

 if (!string.IsNullOrEmpty(Add))
 {
     cmd.Parameters.AddWithValue("@Add", DBNull.Value);
 }
 else
 { 
     cmd.Parameters.AddWithValue("@Add", Add); 
 }

score 1 · Answer 3 · edited May 23 '17 at 11:58

1

Use int? yr instead of int yr

parameters.Add(""@yr"", yr.HasValue ? yr.Value : (object)DBNull.Value);

Reference : Insert a null value to an int column in sql server

edited May 23 '17 at 11:58

Community

1
1

answered Mar 19 '14 at 05:37

NullReferenceException

1,641
15
22

@NullRefrenceException: And What about yr = Convert.ToInt32(ChildDOBYr.Text); still error cured here – neel Mar 19 '14 at 05:42
@neel try yr=int.Parse(ChildDOBYr.Text); – NullReferenceException Mar 19 '14 at 06:02

score 0 · Answer 4 · edited Mar 20 '14 at 06:08

0

Run this query and you will know how it works.

create table #test(
  ID int null
)

insert into #test(ID)values(NUll)
select * from #test

drop table #test

edited Mar 20 '14 at 06:08

Bernhard Barker

54,589
14
104
138

answered Mar 19 '14 at 05:47

Amul Harad

148
4

and pass null value from stored procedure using DBNull.Value – Amul Harad Mar 19 '14 at 05:48

Inserted Null values into SQL table

4 Answers4