Getting ID of the Selected Value in dropdown list

Question

I have two dropdown list for getting Category and Subcategory. The scenario is that both values are coming from a single table. I am able to get the Categories in the first dropdownlist. What I want is on Selection of any Category, The CategoryID of the CategoryName should also become the ParentID of the SubCategory. Please see the code of calling the Categories in DDL.:-

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("Select * from dbo.CategoriesForMerchant where ParentId is null", conn);
        SqlDataReader dr = cmd.ExecuteReader();
        ddlCategories.DataSource = dr;
        ddlCategories.Items.Clear();
        ddlCategories.DataTextField = "CategoryName";
        ddlCategories.DataValueField = "CategoryId";
        ddlCategories.DataBind();
        ddlCategories.Items.Insert(0, new ListItem("--Select Category--", "0"));
        conn.Close();
    }
}
protected void ddlCategories_SelectedIndexChanged(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultSQLConnectionString"].ConnectionString);
    conn.Open();
    string value = "";
    if (!String.IsNullOrEmpty(ddlCategories.SelectedValue.ToString()))
    {
        value = ddlCategories.SelectedValue.ToString();
    }
    SqlCommand cmd = new SqlCommand("Select * from CategoriesForMerchant where ParentId ='" + value + "'", conn);
    SqlDataReader dr = cmd.ExecuteReader();
    ddlSubCategories.DataSource = dr;
    ddlSubCategories.Items.Clear();
    ddlSubCategories.DataTextField = "CategoryName";
    ddlSubCategories.DataValueField = "CategoryName";
    ddlSubCategories.DataBind();
    ddlSubCategories.Items.Insert(0, new ListItem("--Select Sub Category--", "NA"));
    conn.Close();
}

protected void btnSubmit_Click(object sender, EventArgs e)
{
    SqlCommand cmd = new SqlCommand("Insert into CategoriesForMerchant (CategoryName) values (@CategoryName)", conn);
    cmd.Parameters.AddWithValue("@CategoryName", txtCategory.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    Response.Write("<script>alert('File uploaded successfully');</script>");
}

Also see the HTML code of both the dropdown:-

<asp:TextBox ID="txtCategory" runat="server" placeholder="Add category"></asp:TextBox>
                        <asp:Button ID="btnSubmit" runat="server" Text="Submit" Width="110" OnClick="btnSubmit_Click" />

                        <br />

                        <asp:TextBox ID="txtSubCategory" runat="server" placeholder="Add subcategory"></asp:TextBox>
                        <asp:Button ID="btnSubCategory" runat="server" Text="Submit" Width="110" OnClick="btnSubCategory_Click" />

Also, see my table structure for your idea:-

CREATE TABLE [dbo].[categoriesformerchant] 
( 
 id     INT IDENTITY(1, 1) NOT NULL, 
 NAME   NVARCHAR(50) NOT NULL, 
 ParentId INT NULL, 
 CONSTRAINT [pk_CategoriesForMerchant ] PRIMARY KEY CLUSTERED (id ASC) 
) 

go 

ALTER TABLE [dbo].[categoriesformerchant] 
  WITH CHECK ADD CONSTRAINT [fk_subcategories] FOREIGN KEY(ParentId) REFERENCES 
  [dbo].[categoriesformerchant] ([id]) 

go 

ALTER TABLE [dbo].[categoriesformerchant] 
  CHECK CONSTRAINT [fk_subcategories] 

go 

Answer 1

For Adding the Id which has been referenced the foreign key, I need to add

cmd.Parameters.Add("@ParentId",  ddlCategories.SelectedValue);

Also see the whole code for your reference:-

 <asp:TextBox ID="txtSubCategory" runat="server" placeholder="Add sub category"></asp:TextBox>

Button code:-

 <asp:Button ID="btnSubmit" runat="server" Text="Submit" Width="110" OnClick="btnSubmit_Click" />

Also see the code-behind, the Main thing in resloving:-

protected void btnSubmit_Click(object sender, EventArgs e)
{   
    SqlCommand cmd = new SqlCommand("Insert into CategoriesForMerchant (CategoryName, ParentId) values (@CategoryName, @ParentId)", conn);
    cmd.Parameters.AddWithValue("@CategoryName", txtSubCategory.Text);
    cmd.Parameters.Add("@ParentId", ddlCategories.SelectedValue);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    Response.Write("<script>alert('File uploaded successfully');</script>");
    txtSubCategory.Text = "";
}

Answer 2

ParentId is Int value but Your passing at String so you got error convert to int and then you try


protected void ddlCategories_SelectedIndexChanged(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultSQLConnectionString"].ConnectionString);
    conn.Open();
    SqlCommand cmd = new SqlCommand("Select * from CategoriesForMerchant where ParentId =" + Convert.ToInt32(ddlcategories.SelectedValue) + "", conn);
    SqlDataReader dr = cmd.ExecuteReader();

    if (dr.HasRows())
    {
    ddlSubCategories.DataSource = dr;
    ddlSubCategories.Items.Clear();
    ddlSubCategories.DataTextField = "CategoryName";
    ddlSubCategories.DataValueField = "CategoryName";
    ddlSubCategories.DataBind();
    ddlSubCategories.Items.Insert(0, new ListItem("--Select Sub Category--", "NA"));
    conn.Close();
    dr.Close();
    }
}