是否可以在数据库的日期时间字段中插入null(“”)值

本文关键字:null 插入 字段 数据库 时间 日期 是否 | 更新日期: 2023-09-27 18:00:44

我有两个字段DOB(出生日期)和(加入日期)如果用户输入日期,则在数据库中插入->日期其他插入->空

我已将属性定义为

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

现在处于save_click事件

        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;

我希望当用户没有插入任何日期时,应该插入null。。。但是通过这个代码我得到了一个错误存储过程

@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

Web服务

[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();
        }

是否可以在数据库的日期时间字段中插入null(“”)值

首先需要检查日期列是否接受空值
如果不允许,则允许那里为null。

然后你可以使用

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

将所有内容组合在一起看起来像这个

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

你的房产应该是这样的

public DateTime? DOJ
{
    get;
    set;
}

您需要对代码进行3次更改才能工作,

更改1:首先将DateTime变量设置为DateTime?

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

更改2:使用DateTime.TryParse代替

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;
}

司法部也是如此。

更改3:此外,当datetime的值为null时,请确保在将DOB的值分配给sql参数时进行null检查,如果DOB为null,则分配DBNull.Value

例如:

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

是的,欢迎来到Stackoverflow!

此代码用于在sql的datetime字段中插入null值

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

使属性可以为空

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

并使你的代码像这个

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;
}