Drop down list that allows blank or null to be selected

Question

My dropdown list is bound to a database, I want it to allow the selection of nulls. Therefore, when it is selected nothing goes into the table. Right now, it works if I were to set the value to an integer, but I don't want it to work this way. How can I do this programmatically in the easiest way possible in ASP.NET?

I was able to insert the blank into the drop down however when selected it's not inserting the null value into the table when I click my insert button. I keep getting error messages either saying error converting Nvarchar to int or an FK constraint message I believe which is due to the database not allowing things to be inserted that aren't in that table in the database.

Here is the code I used to create the blank:

<asp:DropDownList ID="DDlContract" runat="server" DataSourceID="SqlDataSource1" DataTextField="ContractName" DataValueField="ContractID" Width="200px" AppendDataBoundItems="True">
    <Items>
        <asp:ListItem Text="" value =""></asp:ListItem>
    </Items>

I tried:

<asp:DropDownList ID="DDlContract" runat="server" DataSourceID="SqlDataSource1" DataTextField="ContractName" DataValueField="ContractID" Width="200px" AppendDataBoundItems="True">
    <Items>
        <asp:ListItem Text="" value =""></asp:ListItem>
    </Items>

I was expecting it to allow me to insert a null value.

Here is the insertion code:

protected void Page_Load(object sender, EventArgs e)
{
      Databinder();
}

protected void BtnNew_Click(object sender, EventArgs e)
{
    string GPOEmailID = TxtGPO.Text;
    string LastName = TxtLname.Text;
    string FirstName = TxtFname.Text;
    string WorkPhone = TxtWork.Text;
    string ContractID = DDlContract.SelectedValue.ToString();
    string JobSeriesID = DDlJob.SelectedValue.ToString();
    string PositionID = DDlPosition.SelectedValue.ToString();
    string PersonalPhone = TxtPersonal.Text;
    string SupervisorGPOEmailID = DDlSupervisor.SelectedValue.ToString();
    string EmpCatID = DDlCat.SelectedValue.ToString();
    string EmpStatusID = DDlStatus.SelectedValue.ToString();
    string EmployeeRoleID = DDlRole.SelectedValue.ToString();

    con.Open();
    SqlCommand cmd = new SqlCommand("dbo.spNewEmployeeList ", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@GPOEmailID ", GPOEmailID);

    cmd.Parameters.AddWithValue("@LastName ", LastName);
    cmd.Parameters.AddWithValue("@FirstName ", FirstName);
    cmd.Parameters.AddWithValue("@WorkPhone ", WorkPhone);
    cmd.Parameters.AddWithValue("@ContractID ", ContractID);
    cmd.Parameters.AddWithValue("@JobSeriesID ", JobSeriesID);
    cmd.Parameters.AddWithValue("@PositionID  ", PositionID);
    cmd.Parameters.AddWithValue("@PersonalPhone ", PersonalPhone);
    cmd.Parameters.AddWithValue("@SupervisorGPOEmailID ", SupervisorGPOEmailID);
    cmd.Parameters.AddWithValue("@EmpCatID ", EmpCatID);
    cmd.Parameters.AddWithValue("@EmpStatusID ", EmpStatusID);
    cmd.Parameters.AddWithValue("@EmployeeRoleID ", EmployeeRoleID);

    cmd.ExecuteNonQuery();

    con.Close();
    GridView1.DataBind();
    LoadRecord();
}

For my Update button code how would I go about this same process, here is the code. I am trying to allow nulls values for the following Data center ID, Environment ID, Designation ID, and Computer status ID:

 protected void BtnUpdate_Click(object sender, EventArgs e)
 {
     string ComputerID = DDlUpdate.SelectedValue.ToString();
     int CPUType =  Convert.ToInt32(DDlType.SelectedValue);
     string VIP_ID = DDlVIP.SelectedValue.ToString();
     string IPAddress = TxtIpaddress.Text;
     string PhysicalIDAddress = TxtPhysical.Text;
     string OS = TxtOS.Text;
     string Patchgroup = TxtPatchGroup.Text;
     string Location = TxtLocation.Text;
     string MacAddress = TxtMac.Text;
     string ComputerURL = TxtUrl.Text;
     string ComputerName = TxtName.Text;
     string DataCenterID = DDlDatacenter.SelectedValue.ToString();
     string EnvironmentID = DDlEnvironment.SelectedValue.ToString();
     string DesignationID = DDlDesignationID.SelectedValue.ToString();
     string ComputerStatusID = DDlStatus.SelectedValue.ToString();
     
     int Valid;
     if (IsValid.Checked == true)
     {
         Valid = 1;
     }
     else
     {
         Valid = 0;
     }

     con.Open();
     SqlCommand co = new SqlCommand("exec spModifyComputersList '" + ComputerID + "','" + VIP_ID + "','" +  CPUType + "','" + PhysicalIDAddress + "','" + IPAddress + "','" + OS + "','" + Patchgroup + "','" + Location + "','" + MacAddress + "','" + ComputerURL + "','" + ComputerName + "','" + DataCenterID + "','" + EnvironmentID + "','" + DesignationID + "','" + ComputerStatusID + "','" +  + Valid + "'", con);
     co.ExecuteNonQuery();
     con.Close();
     ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Sucessfully Updated.');", true);
     Label1.Text = "Record has been Updated";
     GridView1.DataBind();
     DDlUpdate.DataBind();
     LoadRecord();

Answer 1

You are passing the value which is string type (NVARCHAR type in SQL) to the stored procedure for @ContractID and the SQL parameter expects the value should be the INT type.

Thus, you need to handle with the scenario:

1. When the ContractID is null/empty string, you need to pass the value as DBNull.Value.

2. When the ContractID has value, you need to cast it into an integer.

cmd.Parameters.AddWithValue("@ContractID ", String.IsNullOrEmpty(ContractID)
    ? (object)DBNull.Value
    : Convert.ToInt32(ContractID));

In this community, we recommend providing the parameter with value and type (length) instead of just providing the value via AddWithValue(). You may read the article: AddWithValue is Evil

cmd.Parameters.Add("@ContractID", SqlDbType.Int).Value = String.IsNullOrEmpty(ContractID)
    ? (object)DBNull.Value
    : Convert.ToInt32(ContractID);

Also, do take note that your stored procedure should be modified with the ContractID parameter to allow null. As it is nullable and optional, the parameter needs to be placed at the end (mandatory/not nullable parameter(s) comes first).

CREATE PROCEDURE dbo.spNewEmployeeList
  -- Other Parameters,
  ContractID INT = NULL