在日期时间列和整列插入空值 [英] inserting null values in datetime column and integer column
问题描述
我使用C＃和开发WinForm应用程序。我有有项目一期工程类属性。
I am using C# and developing a winform application. I have a project class which has the project attributes.
该项目类的构造函数如下：
the constructor of the project class is as follows:
newProject = new Project(GCD_ID.IsNull() ? (int?)null : Convert.ToInt32(GCD_ID), txt_Proj_Desc.Text, txt_Prop_Name.Text, ST.ID.ToString().IsNull() ? null: ST.ID.ToString(), cmbCentre.Text,
SEC.ID.ToString().IsNull() ? null : SEC.ID.ToString(), cmbZone.Text,
FD.ID.ToString().IsNull() ? null : FD.ID.ToString(), DT.ID.ToString().IsNull() ? null : DT.ID.ToString(), OP.ID.ToString().IsNull() ? null : OP.ID.ToString(), T.ID.ToString().IsNull() ? null : T.ID.ToString(),
CKV.ID.ToString().IsNull() ? null : CKV.ID.ToString(), STAT.ID.ToString().IsNull() ? null : STAT.ID.ToString(), MW.IsNull() ? (Double?)null : Convert.ToDouble(MW),
txt_Subject.Text, Ip_Num.IsNull() ? (int?)null : Convert.ToInt32(Ip_Num), H1N_ID.IsNull() ? (int?)null : Convert.ToInt32(H1N_ID),
NOMS_Slip_Num.IsNull() ? (int?)null : Convert.ToInt32(NOMS_Slip_Num), NMS_Updated.IsNull() ? (DateTime?)null : Convert.ToDateTime(NMS_Updated),
Received_Date.IsNull() ? (DateTime?)null : Convert.ToDateTime(Received_Date), Actual_IS_Date.IsNull() ? (DateTime?)null : Convert.ToDateTime(Actual_IS_Date),
Scheduled_IS_Date.IsNull() ? (DateTime?)null : Convert.ToDateTime(Scheduled_IS_Date), UpSt.ID.ToString().IsNull() ? null : UpSt.ID.ToString(),
UpFd.ID.ToString().IsNull() ? null : UpFd.ID.ToString(), txtHVCircuit.Text, cmbbxSIA.Text);
我的问题是，我不能将值插入数据库时的日期时间变量和整型变量为空。所有这些数据都从文本框赋值给变量的形式。
My problem is that i cannot insert values into the database when the datetime variables and the integer variables are null. all this data are assigned to the variable from textboxes on the form..
下面是数据库函数，它接受在所有的变量并把它们插入到数据库中。
bELOW is the database function which takes in all the variables and insert them into the database.
public static void createNewProject(int? GCD_ID, string Project_Desc, string Proponent_Name, int Station_ID, string OpCentre, int Sector_ID, string PLZone, int Feeder, int DxTx_ID, int OpControl_ID, int Type_ID, int ConnKV_ID, int Status_ID, double? MW, string Subject, int? Ip_Num, int? H1N_ID, int? NOMS_Slip_Num, DateTime? NMS_Updated, DateTime? Received_Date, DateTime? Actual_IS_Date, DateTime? Scheduled_IS_Date, int UP_Station_ID, int UP_Feeder_ID, string @HV_Circuit, string SIA_Required)
{
SqlConnection conn = null;
try
{
//Takes in all the employee details to be added to the database.
conn = new SqlConnection(databaseConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("createNewProject", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("GCD_ID", GCD_ID));
cmd.Parameters.Add(new SqlParameter("Project_Desc", MiscFunctions.Capitalize(Project_Desc)));
cmd.Parameters.Add(new SqlParameter("Proponent_Name", MiscFunctions.Capitalize(Proponent_Name)));
cmd.Parameters.Add(new SqlParameter("Station_ID", Station_ID));
cmd.Parameters.Add(new SqlParameter("OpCentre", OpCentre));
cmd.Parameters.Add(new SqlParameter("Sector_ID", Sector_ID));
cmd.Parameters.Add(new SqlParameter("PLZone", PLZone));
cmd.Parameters.Add(new SqlParameter("Feeder", Feeder));
cmd.Parameters.Add(new SqlParameter("DxTx_ID", DxTx_ID));
cmd.Parameters.Add(new SqlParameter("OpControl_ID", OpControl_ID));
cmd.Parameters.Add(new SqlParameter("Type_ID", Type_ID));
cmd.Parameters.Add(new SqlParameter("ConnKV_ID", ConnKV_ID));
cmd.Parameters.Add(new SqlParameter("Status_ID", Status_ID));
cmd.Parameters.Add(new SqlParameter("MW", MW));
cmd.Parameters.Add(new SqlParameter("Subject", Subject));
cmd.Parameters.Add(new SqlParameter("Ip_Num", Ip_Num));
cmd.Parameters.Add(new SqlParameter("H1N_ID", H1N_ID));
cmd.Parameters.Add(new SqlParameter("NOMS_Slip_Num", NOMS_Slip_Num));
cmd.Parameters.Add(new SqlParameter("NMS_Updated", NMS_Updated));
cmd.Parameters.Add(new SqlParameter("Received_Date", Received_Date));
cmd.Parameters.Add(new SqlParameter("Actual_IS_Date", Actual_IS_Date));
cmd.Parameters.Add(new SqlParameter("Scheduled_IS_Date", Scheduled_IS_Date));
cmd.Parameters.Add(new SqlParameter("UP_Station_ID", UP_Station_ID));
cmd.Parameters.Add(new SqlParameter("UP_Feeder_ID", UP_Feeder_ID));
cmd.Parameters.Add(new SqlParameter("HV_Circuit", HV_Circuit));
cmd.Parameters.Add(new SqlParameter("SIA_Required", SIA_Required));
cmd.ExecuteNonQuery();
}
catch (Exception e) //returns if error incurred.
{
MessageBox.Show("Error occured in createNewProject" + Environment.NewLine + e.ToString());
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
我的问题是，如何插入值到数据库中。 请请帮助
My question is, how do i insert values into the database. please please help
推荐答案
您得到一个错误，指出该参数丢失？如果是这样，对于每一个有一个空值的paramaters的，使用替代的DBNull.Value
Are you getting an error saying that the parameter is missing? If so, for each of the paramaters that have a null value, use DBNull.Value instead.
所以做到这一点，或者它的一些变化：
So do this, or some variation of it:
if(H1N_ID != null)
{
cmd.Parameters.Add(new SqlParameter("H1N_ID",HIN_ID);
}
else
{
cmd.Parameters.Add(new SqlParameter("H1N_ID",DBNull.Value);
}
此外，任何时候你正在阅读这些领域从数据库到你的对象，您应检查值的DBNull.Value和其转换成空。
Also, anytime you are reading those fields out of the database into your objects, you should check for the DBNull.Value value and convert that to null.
这篇关于在日期时间列和整列插入空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
