ASP.NET Can';t在数据库中插入null DateTime
本文关键字:数据库 插入 null DateTime Can NET ASP | 更新日期: 2023-09-27 18:24:43
我这里有这个方法:
public List<CustomerQuestionsClass> updateCustomersQuestions(List<CustomerQuestionsClass> items)
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(@"IF EXISTS(SELECT * FROM CustomerQuestions WHERE CustomerID = @CustomerID AND QuestionID = @QuestionID)
BEGIN
UPDATE CustomerQuestions SET selected = @Selected, ModifiedBy = @ModifiedBy, DateModified = @DateModified WHERE CustomerID = @CustomerID AND QuestionID = @QuestionID
END
ELSE
BEGIN
INSERT INTO CustomerQuestions (CustomerID, QuestionID, selected, CreatedBy,
DateCreated, ModifiedBy, DateModified, DateCompleted, DueDate)
VALUES (@CustomerID, @QuestionID, @Selected, @CreatedBy, @DateCreated,
@ModifiedBy, @DateModified, @DateCompleted, @DueDate)
END");
command.Parameters.Add("@CustomerID", System.Data.SqlDbType.Int);
command.Parameters.Add("@QuestionID", System.Data.SqlDbType.Int);
command.Parameters.Add("@Selected", System.Data.SqlDbType.Bit);
command.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Text);
command.Parameters.Add("@DateCreated", System.Data.SqlDbType.DateTime);
command.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Text);
command.Parameters.Add("@DateModified", System.Data.SqlDbType.DateTime);
command.Parameters.Add("@DateCompleted", System.Data.SqlDbType.DateTime);
command.Parameters.Add("@DueDate", System.Data.SqlDbType.DateTime);
command.Connection = connection;
connection.Open();
for (int i = 0; i < items.Count; i++)
{
command.Parameters["@CustomerID"].Value = items[i].CustomerID;
command.Parameters["@QuestionID"].Value = items[i].QuestionID;
command.Parameters["@Selected"].Value = items[i].selected;
command.Parameters["@CreatedBy"].Value = items[i].Username;
command.Parameters["@DateCreated"].Value = DateTime.Now;
command.Parameters["@ModifiedBy"].Value = items[i].Username;
command.Parameters["@DateModified"].Value = DateTime.Now;
command.Parameters["@DateCompleted"].Value = items[i].DateCompleted.Equals(DateTime.MinValue) ? (DateTime?)null : items[i].DateCompleted;
command.Parameters["@DueDate"].Value = items[i].DueDate.Equals(DateTime.MinValue) ? (DateTime?)null : items[i].DueDate;
command.ExecuteNonQuery();
}
connection.Close();
return items;
}
它正在尝试插入。但我一直收到这个错误:
参数化查询"(@CustomerID int,@QuestionID int,@Selected bit,@CreatedBy text,@"需要参数"@DateCompleted",但未提供该参数。
当我删除所有与DateCompleted
有关的内容时,查询会运行并插入数据,但对于DateCompleted
,它不起作用,我会得到上面的错误。DateCompleted
的值为null。
您必须显式传递DBNull.Value
作为参数值,而不是null
:
command.Parameters["@DateCompleted"].Value =
items[i].DateCompleted.Equals(DateTime.MinValue) ? (object)DBNull.Value : items[i].DateCompleted;
转换为object
是必要的,以便可以解析表达式的类型。