Is it possible to insert null(" ") value to the datetime field in the database

Question

I have two fields DOB(date of birth) and (date of joining) If user enter the date then in the database insert->date else insert->null

I have define properties as

  public DateTime DOB
    {
        get;
        set;
    }
    public DateTime DOJ
    {
        get;
        set;
    }

Now in save_click event

        IFormatProvider provider = new System.Globalization.CultureInfo("en-CA", true);
        String datetime = txtDOB.Text.Trim();
        DateTime date = DateTime.Parse(datetime, provider, System.Globalization.DateTimeStyles.NoCurrentDateDefault);
        objEmp.DOB = date;

        provider = new System.Globalization.CultureInfo("en-CA", true);
        datetime = txtDOJ.Text.Trim();
        date = DateTime.Parse(datetime, provider, System.Globalization.DateTimeStyles.NoCurrentDateDefault);
        objEmp.DOJ = date;

class.cs

        param[7] = new Service.SqlParameter();
        param[7].ParameterName = "@DOB";
        param[7].Value = DOB;
        param[7].SqlDbType = Service.SqlDbType.DateTime;

        param[8] = new Service.SqlParameter();
        param[8].ParameterName = "@DOJ";
        param[8].Value = DOJ;
        param[8].SqlDbType = Service.SqlDbType.DateTime;

I want that when user did not insert any date then null should be inserted... But by this code i am getting an error Store procedure

@FK_EmployeeTypeID Numeric(18,0),
    @EmployeeName NVARCHAR(50), 
    @CellNo  NVARCHAR(50),  
    @PhNo  NVARCHAR(50),    
    @Address  NVARCHAR(Max),    
    @Email  NVARCHAR(50),   
    @DOB DATETIME,
    @DOJ DATETIME,
    @DOR NVARCHAR(12),
    @Status NVARCHAR(50),
    @Remarks NVARCHAR(Max)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    BEGIN TRY
        BEGIN TRANSACTION Employee_Insert
    INSERT 
    INTO
    Employee
    (
        FK_EmployeeTypeID,
        EmployeeName,   
        CellNo, 
        PhNo,   
        Address,    
        Email,  
        DOB,
        DOJ,
        DOR,
        Status,
        Remarks

    )
    VALUES
    (

        @FK_EmployeeTypeID,
        @EmployeeName,  
        @CellNo,    
        @PhNo,  
        @Address,   
        @Email, 
        @DOB,
        @DOJ,
        @DOR,
        @Status,
        @Remarks
    )

        COMMIT TRANSACTION Employee_Insert
    SELECT '1'
    END TRY
    BEGIN CATCH
        SELECT 'Error : ' + ERROR_MESSAGE()
        ROLLBACK TRANSACTION Employee_Insert
    END CATCH

Webservice

[WebMethod]
    public string InsUpdDel(string Conn, string ProcName, SqlParameter[] p)
    {
        try
        {
            using (SqlConnection cn = new SqlConnection(Conn))
            {
                if (cn.State == ConnectionState.Open || cn.State == ConnectionState.Broken || cn.State == ConnectionState.Connecting || cn.State == ConnectionState.Executing || cn.State == ConnectionState.Fetching)
                    cn.Close();
                cn.Open();
                SqlCommand cmd = new SqlCommand(ProcName, cn);

                cmd.CommandType = CommandType.StoredProcedure;

                foreach (SqlParameter param in p)
                {
                    cmd.Parameters.Add(param);
                }
                SqlDataReader dr = cmd.ExecuteReader();
                dr.Read();
                if (dr[0].ToString() == "1")
                {
                    return "1";
                }
                else
                {
                    return dr[0].ToString();
                }
                dr.Close();
                cn.Close();
            }
        }
        catch (Exception ex)
        {
            return "Error : " + ex.Message.ToString();
        }

Is the column defined as nullable in the database table? Then yes. Also: don't just tease us *I am getting an error* - please **LET US KNOW** exactly what that error is! (we can't read your screen nor your mind, you know.....) — marc_s, Feb 22 '13 at 06:14
Also, `" "` is not null. It is a string literal. `NULL` is NULL. — , Feb 22 '13 at 06:15
Learn about 1) `Nullable` (alias `DateTime?`) and 2) DateTime.TryParse. You are getting an error if the user doesn't enter a date because you are using a Parse method that throws exceptions. And if you want to insert nulls into the database, you need types that support that. Both in code, and in the database. — Anthony Pegram, Feb 22 '13 at 06:16
catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Error", string.Format("", ex.Message.ToString())); } after calling insert() method the statement goes to catch — Pritesh, Feb 22 '13 at 08:02
possible duplicate of [inserting null values in datetime column and integer column](http://stackoverflow.com/questions/2456755/inserting-null-values-in-datetime-column-and-integer-column) — David G, Feb 22 '13 at 13:19

शेखर · Answer 1 · 2013-02-22T06:27:15.320

1

First you need to check you date column will accept null values or not.
If not then allow null there.

and then you can use

command.Parameters.AddWithValue("@param", DBNull.Value);

combining all will look like this

 param[8] = new Service.SqlParameter();
 param[8].ParameterName = "@DOJ";
 param[8].Value = DOJ==null?DBNull.Value:DOJ;
 param[8].SqlDbType = Service.SqlDbType.DateTime;

And your property should be like this

public DateTime? DOJ
{
    get;
    set;
}

edited Feb 22 '13 at 06:27

answered Feb 22 '13 at 06:21

शेखर

17,412
13
61
117

@Yasser I was updating my answer. – शेखर Feb 22 '13 at 06:28

Yasser Shaikh · Answer 2 · 2013-02-22T06:36:28.570

1

You need to make 3 changes for your code to work,

Change 1 : First make your DateTime variable to DateTime?

public DateTime? DOB { get; set; }
public DateTime? DOJ { get; set; }

Change 2 : Use DateTime.TryParse instead

DateTime DOB;
IFormatProvider provider = new System.Globalization.CultureInfo("en-CA", true);
String datetime = txtDOB.Text.Trim();
DateTime.TryParse(datetime, provider, System.Globalization.DateTimeStyles.NoCurrentDateDefault, out DOB);
if (DOB != DateTime.MinValue)
{
    objEmp.DOB = null; 
}
else
{
    objEmp.DOB = date;
}

do same for DOJ.

Change 3 : Also when the value of datetime is null, make sure that when you are assiging the value of DOB to the sql parameter have a null check and if DOB is null then assign DBNull.Value

For example :

param[7].ParameterName = "@DOB";
if(DOB == null)
{
    param[7].Value = DBNull.Value;
}
else
{
    param[7].Value = DOB;   
}
param[7].SqlDbType = Service.SqlDbType.DateTime;

and yes welcome to Stackoverflow !

edited Feb 22 '13 at 06:36

answered Feb 22 '13 at 06:22

Yasser Shaikh

46,934
46
204
281

@ yasser Still its not working... i m getting this error: "There was an error in generating XML document". I have add the SP in my question. I think the issue is now with SP – Pritesh Feb 22 '13 at 07:20
which line is giving the error ? please post the error, line number, stack trace etc – Yasser Shaikh Feb 22 '13 at 07:29
catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Error", string.Format("", ex.Message.ToString())); } after calling insert() method the statement goes to catch – Pritesh Feb 22 '13 at 07:32
I have add the webservice method... any issue in that?? – Pritesh Feb 22 '13 at 08:04
maybe... cant tell, your initial problem relating to this question is solved, so you can mark one of the answers and post a new question with full details of what is happening – Yasser Shaikh Feb 22 '13 at 12:02

score 1 · Answer 3 · edited May 14 '13 at 07:29

1

this code is worked for insert null vales in datetime field in sql

cmd.Parameters.AddWithValue("@from_date", SqlDbType.DateTime).Value = DBNull.Value;

edited May 14 '13 at 07:29

Soner Gönül

97,193
102
206
364

answered May 14 '13 at 07:10

Desh Raj

11
1

also use this cmd.Parameters.Add("@from_date", SqlDbType.DateTime).Value = DBNull.Value; – Desh Raj May 14 '13 at 07:13

score 0 · Answer 4 · answered Feb 22 '13 at 06:20

0

Make your properties nullable

public DateTime? DOB
    {
        get;
        set;
    }
    public DateTime? DOJ
    {
        get;
        set;
    }

and make your code like this

IFormatProvider provider = new System.Globalization.CultureInfo("en-CA", true);
        String datetime = txtDOB.Text.Trim();

if(DateTime.TryParse(datetime, provider, System.Globalization.DateTimeStyles.NoCurrentDateDefault, out date))
{
  objEmp.DOB = date;
}

answered Feb 22 '13 at 06:20

DSharper

3,177
9
29
47

Still its not working... i m getting this error: "There was an error in generating XML document" – Pritesh Feb 22 '13 at 07:16
[Here is the answer][1] [1]: http://stackoverflow.com/questions/15020551/webservice-method-returns-errorthere-is-an-error-in-generating-an-xml-document – Pritesh Feb 27 '13 at 06:59

Is it possible to insert null(" ") value to the datetime field in the database

4 Answers4