Drop Down list null value not working for selected text c#?

Question

Hi guys,

Basically I have a drop down list which is populated with text and values depending on a selected index of a radio button list. The code is as follows:

protected void RBLGender_SelectedIndexChanged(object sender, EventArgs e)
    {
        DDLTrous.Items.Clear();
        DDLShoes.Items.Clear();
        if (RBLGender.SelectedValue.Equals("Male"))
        {

            String[] MaleTrouserText = {"[N/A]", "28\"", "30\"", "32\"", "34\"", "36\"", "38\"", "40\"", "42\"", "44\"", "48\""};
            String[] MaleTrouserValue = { null, "28\"", "30\"", "32\"", "34\"", "36\"", "38\"", "40\"", "42\"", "44\"", "48\"" };
            String[] MaleShoeText = { "[N/A]", "38M", "39M", "40M", "41M", "42M", "43M", "44M", "45M", "46M", "47M" };
            String[] MaleShoeValue = { null, "38M", "39M", "40M", "41M", "42M", "43M", "44M", "45M", "46M", "47M" };
            for (int i = 0; i < MaleTrouserText.Length; i++)
            {
                ListItem MaleList = new ListItem(MaleTrouserText[i], MaleTrouserValue[i]);
                DDLTrous.Items.Add(MaleList);

            }
            for (int i = 0; i < MaleShoeText.Length; i++)
            {
                ListItem MaleShoeList = new ListItem(MaleShoeText[i], MaleShoeValue[i]);
                DDLShoes.Items.Add(MaleShoeList);

            }

        }

            else if (RBLGender.SelectedValue.Equals("Female"))`

When the text '[N/A]' is selected I want the value NULL to be inserted into the database. At present when '[N/A]' is selected the value entered into the database is [N/A] does anyone know why?

This is my DB insertion code:

protected void UpdateWorkWear()
    {



        try
        {

            string connectionString = Common.GetConnectionString("w34to4tmConnectionString");

            SqlConnection conn = new SqlConnection(connectionString);

            StringBuilder sbSQL = new StringBuilder();


            string str = DDLSurname.SelectedValue;

            if (str.Contains("'"))
            {
                str = str.Replace("'", " ");

            }

            sbSQL.Append("IF EXISTS (select * from TO4_WORKWEAR_DISTRIBUTION where EmpID = @EmpID)");
            sbSQL.Append(" UPDATE TO4_WORKWEAR_DISTRIBUTION SET ");
            sbSQL.Append("costCentre = coalesce(@Cost_Centre, CostCentre), EmployeeType = coalesce(@EmployeeType, EmployeeType), Initials = coalesce(@Initials, Initials), Surname = coalesce(@Surname, Surname), ");
            sbSQL.Append("IssueQuarter = coalesce(@IssueQuarter, IssueQuarter), TrouserSize = coalesce(@Trouser_S, TrouserSize), TrouserLength = coalesce(@Trouser_L, TrouserLength), ");
            sbSQL.Append("PoloSize = coalesce(@Polo_S, PoloSize),  SweatSize = coalesce(@Sweat_S,SweatSize), ShortSize = coalesce(@Short_S, ShortSize), ShoeSize = coalesce(@Shoe_S, ShoeSize) WHERE EmpID = @EmpID ");
            sbSQL.Append(" else INSERT INTO TO4_WORKWEAR_DISTRIBUTION (EmpID,CostCentre, EmployeeType, Initials, Surname, IssueQuarter, TrouserSize, TrouserLength, PoloSize, SweatSize, ShortSize, ShoeSize) ");
            sbSQL.Append("VALUES (@EmpID, @Cost_Centre, @EmployeeType, @Initials, @Surname, @IssueQuarter, @Trouser_S, @Trouser_L, @Polo_S, @Sweat_S, @Short_S, @Shoe_S)");




            sbSQL.Replace("@EmpID", "'" + DDLEmpID.SelectedValue + "'");
            sbSQL.Replace("@Cost_Centre", "'" + DDLCostCentre.SelectedValue + "'");
            sbSQL.Replace("@EmployeeType", "'" + RBLAssociate.SelectedValue + "'");
            sbSQL.Replace("@Initials", "'" + DDLInitials.SelectedValue + "'");
            sbSQL.Replace("@Surname", "'" + str + "'");
            sbSQL.Replace("@Trouser_S", "'" + DDLTrous.SelectedValue + "'");
            sbSQL.Replace("@Trouser_L", "'" + DDLTrouserLnh.SelectedValue + "'");
            sbSQL.Replace("@Trouser_Q", "'" + DDLTrouserQty.SelectedValue + "'");
            sbSQL.Replace("@Polo_S", "'" + DDLPolo.SelectedValue + "'");
            sbSQL.Replace("@Polo_Q", "'" + DDLPoloQty.SelectedValue + "'");
            sbSQL.Replace("@Sweat_S", "'" + DDLSweat.SelectedValue + "'");
            sbSQL.Replace("@Sweat_Q", "'" + DDLSweatQty.SelectedValue + "'");
            sbSQL.Replace("@Shoe_S", "'" + DDLShoes.SelectedValue + "'");
            sbSQL.Replace("@Short_S", "'" + DDLShor.SelectedValue + "'");
            sbSQL.Replace("@InputDate", "'" + System.DateTime.Now.Date.ToString("MM/dd/yyyy") + "'");
            sbSQL.Replace("@IssueQuarter", "'" + RBLQuarter.SelectedValue + "'");

            SqlCommand cmd = new SqlCommand(sbSQL.ToString(), conn);
            cmd.CommandType = CommandType.Text;

            conn.Open();

            int result = cmd.ExecuteNonQuery(); //execute the SQL command on the database   
            LiteralControl lit = new LiteralControl();
            lit.Text = String.Format("Data Uploaded Successfully.");

Thanks for all your help guys. I have decided to insert blank strings "" into the datbase instead of null now as it makes my life slightly easier when i run reports from it.

Answer 1

A C# null is not the same as a database NULL.

In your insertion code you probably need to pick up that the value is "N/A"/null and then insert a DBNull instead. What does your DB insertion code look like?

Answer 2

From my interpretation of C# ListItems, if the Value is not defined, then when you look up the Value, you'll get the Text. It's probably a simple if (Value == null) return Text; check, so naturally if you deliberately set the Value as null, it will then return the Text instead of your null value.

Another way to look at it is that you're calling the new ListItem(string, string) constructor, but passing a null value for the second argument. This basically calls new ListItem(string), which likewise sets the Value to the Text. Mayhaps not literally, but the end result is the same.

EDIT

I did some testing of this in a simple situation. If you initialize a ListItem object with a null value for its Value, then both ListItem.Value and DropDownList.SelectedValue (when pointing at that list item) will get you the same value as your Text. As such, it can be inferred that initializing a ListItem with a null Value sets the Value to the same as the Text. Even setting it later to null via ListItem.Value = null; will still set it to the Text.

You will have to set the Value to some value, and instead of directly inputting the SelectedValue, run a check for if the SelectedValue equals this preset value. I personally use _|NULL|_.

Code Snippet

ListItem li;
li = new ListItem("Test", null);
Console.WriteLine(li.Value.ToString());
li.Value = null;
Console.WriteLine(li.Value.ToString());
DropDownList ddl = new DropDownList();
ddl.Items.Add(li);
ddl.SelectedIndex = 0;
Console.WriteLine(ddl.SelectedValue);
Console.ReadLine();

Output

Test
Test
Test

Answer 3

Use DBNull.Value instead of null.

Answer 4

You need to show how you are pulling the data off the drop down list and putting it into the db. Use the SelectedItem property to get the ListItem ( you may have to cast it ) and then explicitly call the ListItem.Value for the db insert/update.

edit... doing for code

instead of

 sbSQL.Replace("@Trouser_S", "'" + DDLTrous.SelectedValue + "'");

do

ItemList il = (ItemList)DDLTrous.SelectedItem;
string s = il.Value;
if ( s != null )
  s = "'" + s + "'";
else
  s = "null";

sbSQL.Replace("@Trouser_S", s);

you might also try replacing null with "null" in the value. But the key thing is that you cannot have the "'" + x + "'" around the null value otherwise it goes in as a string.

edit again..

if ItemList.Value cannot be null, then have the value be "null" and change the if statement.

ItemList il = (ItemList)DDLTrous.SelectedItem;
string s = il.Value;
if ( s != "null" )
  s = "'" + s + "'";

sbSQL.Replace("@Trouser_S", s);

Answer 5

The problem is that if a ListItem has its value set to null, it will use the Text property as the value. For example this code:

ListItem l = new ListItem("Cats", null);

string s = l.Value;

The value of s is "Cats".

The solution is not to store null, but store an empty string, or -1, or something similar, and then check for this in your code.

Answer 6

You could simple do this:

sbSQL.Replace("@Trouser_S", (DDLTrous.SelectedValue == "[N/A]" ? "NULL" : "'" + DDLTrous.SelectedValue + "'"));