How to Pass Null Parameter into smalldatetime Datatype

Question

0.00/5 (No votes)

See more:

Hi, All

Here i'am using same store procedure for Inser and Update, when i pass

C#

Dbcon.TerminationDate = DBNull.Value.ToString();

it will show date as 02/Sep/2014 12:00:00 AM

i want to be null in TerminationDate column in DB using to pass null value

Thanks !

My Query :

C#

con.Open();
            cmd = new SqlCommand("Insert_StaffMaster_SP", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TerminateDateD", TerminationDate);
           
            return cmd.ExecuteNonQuery();

My Store Proc:

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Insert_StaffMaster_SP]
(
@TerminateDateD smalldatetime
)
as
begin
if not exists(select UserIdN from StaffMaster where UserIdN=@UserIdN)
insert into StaffMaster (TerminateDateD) values (@TerminateDateD)
else
update StaffMaster set TerminateDateD=@TerminateDateD where UserIdN=@UserIdN
end

Thanks !

Posted 1-Sep-14 20:41pm

prasanna.raj

Add a Solution

Comments

MuhammadUSman1 2-Sep-14 2:52am

TerminationDate = DBNull.Value.ToString();
why you are conerting it in String?

object
you intializing and passing i think both are different.
[Dbcon.TerminationDate = DBNull.Value.ToString(); ]

and [TerminationDate]
confirm it.

4 solutions

Solution 1

What i understand is that you want to pass Null value to store procedure so use this.

C#

con.Open();
            cmd = new SqlCommand("Insert_StaffMaster_SP", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TerminateDateD", DBNull.Value);//DBNull.Value
           
            return cmd.ExecuteNonQuery();

If any Question then please let me know.

Posted 1-Sep-14 20:49pm

MuhammadUSman1

Comments

prasanna.raj 2-Sep-14 3:06am

Yes That's right but when i try to update it's also null na... when i insert that time only it will be null... when i try to update i want to store date...

Solution 2

Parameters default has to be null.

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Insert_StaffMaster_SP]
(
@TerminateDateD smalldatetime = NULL
)
as
begin
if not exists(select UserIdN from StaffMaster where UserIdN=@UserIdN)
insert into StaffMaster (TerminateDateD) values (@TerminateDateD)
else
update StaffMaster set TerminateDateD=@TerminateDateD where UserIdN=@UserIdN
end

Posted 1-Sep-14 20:50pm

David Lee 145

Comments

prasanna.raj 2-Sep-14 3:07am

when i try to insert it save like "01/Jan/1900 12:00:00 AM"

Solution 4

in your proc make

SQL

@TerminateDateD smalldatetime = NULL

as a default value

in your code make your variable as nullable

C#

smalldatetime? TerminationDate = null;

check for null before adding parameter

C#

if(TerminationDate != null)
cmd.Parameters.AddWithValue("@TerminateDate", TerminationDate);

Posted 1-Sep-14 20:57pm

saxenaabhi6

Updated 1-Sep-14 20:58pm

v2

Add a Solution

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

sankarsan parida · Accepted Answer · 2014-09-01T20:53:00

Solution 3

Put directly null in instead of TerminationDate.
Check with one more parameter if you want to pass null or not

if(ForNullValue==true)
 cmd.Parameters.AddWithValue("@TerminateDateD",null );
else 
 cmd.Parameters.AddWithValue("@TerminateDateD", TerminationDate);

Posted 1-Sep-14 20:53pm

sankarsan parida

How to Pass Null Parameter into smalldatetime Datatype

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

How to Pass Null Parameter into smalldatetime Datatype

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us