Allow Null values in parameterised SQL query?

Question

I have the following SQL query that inserts the record into a database based on the parameters. It works well but does not allow null values, returning an error if there is a Null.

The table is set up to allow Nulls.

SqlConnection dbconnection = new SqlConnection(@"Data Source=LEWIS;Initial Catalog=CustomPC;Integrated Security=True");


SqlCommand InsertPC = new SqlCommand("INSERT INTO [Custom_PC] ([CPU_ID], [Motherboard_ID],[Graphics_Card_ID],[PSU_ID],[RAM_ID],[Case_ID],[Primary_Storage_Drive_ID],[Secondary_Storage_Drive_ID],[Primary_Optical_Drive_ID],[Secondary_Optical_Drive_ID]) VALUES (@CPU_ID, @Motherboard_ID, @Graphics_Card_ID, @PSU_ID, @RAM_ID, @Case_ID, @Primary_Storage_Drive_ID, @Secondary_Storage_Drive_ID, @Primary_Optical_Drive_ID, @Secondary_Optical_Drive_ID) SET @ReturnedID = Scope_Identity();", dbconnection);

SqlParameter CPU_IDParam = InsertPC.Parameters.Add("@CPU_ID", SqlDbType.Int);
CPU_IDParam.Value = Session["DATA"];

SqlParameter Motherboard_IDParam = InsertPC.Parameters.Add("@Motherboard_ID", SqlDbType.Int);
Motherboard_IDParam.Value = Session["MotherboardID"];

SqlParameter Graphics_Card_IDParam = InsertPC.Parameters.Add("@Graphics_Card_ID", SqlDbType.Int);
Graphics_Card_IDParam.Value = Session["GraphicsID"];

SqlParameter PSU_IDParam = InsertPC.Parameters.Add("@PSU_ID", SqlDbType.Int);
PSU_IDParam.Value = Session["PSUID"];

SqlParameter RAM_IDParam = InsertPC.Parameters.Add("@RAM_ID", SqlDbType.Int);
RAM_IDParam.Value = Session["RAMID"];

SqlParameter Case_IDParam = InsertPC.Parameters.Add("@Case_ID", SqlDbType.Int);
Case_IDParam.Value = Session["CaseID"];

SqlParameter Primary_Storage_Drive_IDParam = InsertPC.Parameters.Add("@Primary_Storage_Drive_ID", SqlDbType.Int);
Primary_Storage_Drive_IDParam.Value = Session["HDD1ID"];

SqlParameter Secondary_Storage_Drive_IDParam = InsertPC.Parameters.Add("@Secondary_Storage_Drive_ID", SqlDbType.Int);
Secondary_Storage_Drive_IDParam.Value = Session["HDD2ID"];

SqlParameter Primary_Optical_Drive_IDParam = InsertPC.Parameters.Add("@Primary_Optical_Drive_ID", SqlDbType.Int);
Primary_Optical_Drive_IDParam.Value = Session["OpticalDrive1ID"];

SqlParameter Secondary_Optical_Drive_IDParam = InsertPC.Parameters.Add("@Secondary_Optical_Drive_ID", SqlDbType.Int);
Secondary_Optical_Drive_IDParam.Value = Session["OpticalDrive2ID"];

SqlParameter ReturnedIDParam = InsertPC.Parameters.Add("@ReturnedID", SqlDbType.Int);
ReturnedIDParam.Direction = ParameterDirection.Output;


dbconnection.Open();

InsertPC.ExecuteNonQuery();

Session["PCNum"] = (ReturnedIDParam.Value);

Response.Redirect("~/CustomPC_Details.aspx");

By the way, you're not imagining it - the standard behavior here is very silly in my opinion: stackoverflow.com/a/9632050/23354 — Marc Gravell
– Marc Gravell, Commented Apr 29, 2013 at 11:27

Thorsten Dittmar · Accepted Answer · 2013-04-29 11:16:32Z

6

You need to check whether the object is null and set your parameter to DBNull.Value.

SqlParameter Secondary_Optical_Drive_IDParam = InsertPC.Parameters.Add("@Secondary_Optical_Drive_ID", SqlDbType.Int);
Secondary_Optical_Drive_IDParam.Value = (object)Session["OpticalDrive2ID"] ?? (object)DBNull.Value;

Note that you need to cast to object because with the ?? operator both possible values must have the same type.

answered Apr 29, 2013 at 11:16

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Allow Null values in parameterised SQL query?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related