C# .NET - How to insert null value to database?

Asked By keping li on 21-Feb-06 01:02 AM
How to insert null value to database? example, I have to textarea, txt1 for Qty (int), Txt2, birthDay (date),  I have a stored procedure to insert records to database, when txt1 and txt2 is empty, how can I inert null value (@Qty=null and @birthday=Null) to database?

Thanks

Setting null values - Asked By drammer _ on 21-Feb-06 01:12 AM

[CODE] IF @var1 IS NULL OR @var2 IS NULL
 	UPDATE YourTable SET @Qty=null, @birthday=Null WHERE ID=@ID[/CODE]
Asked By Shallu Gupta on 21-Feb-06 01:46 AM
Use a Case Statement in your procedure for empty check
insert into tablename(qty,birthday)
select 
case len(@qty) when 0 then null else @qty end, 
case len(@birthday) when 0 then null else @qty end

Or
pass the parameters as null when txt1 and txt2 are empty.

Null values - Asked By Jon Wojtowicz on 21-Feb-06 06:08 AM

When creating your parameters you can use the conditional operator to set the value to null using the DBNull class

SqlParameter param = new SqlParameter("@Qty", txt1.Text.Trim().Length==0?DBNull.Value:int.Parse(txt1.Text));
Asked By Sushila Patel on 21-Feb-06 09:16 AM
You can try
C#
SqlDateTime sqldatenull ; 
sqldatenull = SqlDateTime.Null;
if (txtDate.Text == "") 
{
cmd.Parameters ["@Date"].Value =sqldatenull ;
//cmd.Parameters["@Date"].Value = DBNull.Value;
}  
else
{
cmd.Parameters["@Date"].Value = DateTime.Parse(txtDate.Text);
}

For entire Sample take a look @
http://www.c-sharpcorner.com/Code/2003/Sept/EnterNullValuesForDateTime.asp
Lars replied to Sushila Patel on 18-May-10 11:21 PM

Consider also below for nullable input parameters to a function:

public


ServicesReturnCode AddAccount(int? accountId)
{

try




{

const string query = "INSERT INTO [SimpleAccountTable] ([AccountId]) VALUES (@AccountId)";

using (SqlCommand command = new SqlCommand(query))

{


if (accountId.HasValue)

command.Parameters.AddWithValue(

"@AccountId", accountId);

else



command.Parameters.AddWithValue(

"@AccountId", DBNull.Value);

}
// Execute statement here...
}