C# populating dropdownlist from database using selected item

Question

there is probably a simple answer and I'm being dumb, but i need help and an outsider perspective. So I have 3 tables in a database: Category (CategoryID, Description), Manufacturer(ManufacturerID, Name), and Products(ProductID, CategoryID, ManufactureID, Model, and other crap). I need to populate Manufacturer DropDownList based on selection of Category DropDownList, but my code throws the "Column 'ManufacturerID' does not belong to table Table.", the spelling is correct, so I must be doing something wrong, but I cant figure out what it is. Any suggestions would be welcome, cause this is killing me. Here is my code.

public DataSet Manufacturers_Load_by_Category(int category)
    {
        dwas = new SqlDataAdapter("Manufacturers_Load_By_Category", conn); // stored procedure created in database
        ds = new DataSet();
        dwas.SelectCommand.CommandType = CommandType.StoredProcedure;
        dwas.SelectCommand.Parameters.AddWithValue("@category", category); 
        try
        {
            dwas.SelectCommand.Prepare();
            dwas.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds;
            }
            else
            {
                return null;
            }
        }
        catch (Exception ex)
        {
            ex.ToString();
            return null;
        }
        finally
        {
            conn.Close();
        }
    }   

this is stored procedure im using (it works, i checked it a 100 times)

ALTER procedure [dbo].[Manufacturers_Load_By_Category]
@category int
as
SELECT        Manufacturers.Name
FROM            Manufacturers INNER JOIN
              Products ON Manufacturers.ManufacturerID =      Products.ManufacturerID
WHERE        (Products.CategoryID = @category)

and finally my point of failure :(

protected void dlCategory_SelectedIndexChanged(object sender, EventArgs e)
    {
        string cat = dlCategory.SelectedValue.ToString(); // get the value the user selected from the manufactuers drop down list
        Datalayer DL = new Datalayer(); // get access to the data layer
        DataSet ds = new DataSet();
        int x = 0;
        //lblError.Visible = false;
        dlManufacturer.Items.Clear(); // clear all the values in the models drop down list
        dlManufacturer.Items.Add(new ListItem("Select", "-1")); // reload the select option

        if (cat != "-1")
        {
            ds = DL.Manufacturers_Load_by_Category(Convert.ToInt32(cat));
            if (ds != null)
            {

                while (x < ds.Tables[0].Rows.Count)
                {
                    //exception is thrown here
                    // "Column 'ManufacturerID' does not belong to table Table."
                    dlManufacturer.Items.Add(new ListItem(ds.Tables[0].Rows[x]["Name"].ToString(), ds.Tables[0].Rows[x]["ManufacturerID"].ToString()));

                    x++;
                }
            }
            else
            {
                //failed to load models
            }
        }
        else
        {
            //invalid manufacturer
        }
    }

the exception is thrown at dlManufacturer.Items.Add(new ListItem(ds.Tables[0].Rows[x]["Name"].ToString(), ds.Tables[0].Rows[x]["ManufacturerID"].ToString()));

I also tried

  //dlManufacturer.DataSource = ds;
                    //dlManufacturer.DataTextField = "Name";
                    //dlManufacturer.DataValueField = "ManufacturerID";
                    //dlManufacturer.DataBind();

but it too did not work, and like I mentioned before the spelling matches the column in Manufacturers Table.

Thank you for any help

Answer 1

Your data table which you are binding with dropdownlist, doesn't contains the ManufacturerID column. Include this field into your select query and then try

SELECT Manufacturers.Name, Manufacturers.ManufacturerID 
FROM   Manufacturers INNER JOIN Products ON Manufacturers.ManufacturerID = Products.ManufacturerID
WHERE  Products.CategoryID = @category