Stack Exchange
Get Sub category according to selected category Asp.net C#
I have a autocomplete text filed for brands and a dropDown for category which is populating through database(SQL Server 2008). here is the code for category dropDown
SqlCommand cmd = new SqlCommand("SELECT * FROM category", con);
cmd.Connection.Open();

SqlDataReader ddlValues;
ddlValues = cmd.ExecuteReader();

getCategoryOfBrand.DataSource = ddlValues;
getCategoryOfBrand.DataValueField = "category";
getCategoryOfBrand.DataTextField = "category";
getCategoryOfBrand.DataBind();

cmd.Connection.Close();
cmd.Connection.Dispose();

Now I want that when I select brand it automatically set the selected value of category dropDown = selected Brand's category.
this is brands table.

Category Table

code for textChange event of brand text field
public void getFallingCategory(object sender, EventArgs e)
{

    brandNameUpdated.Text = getBrandForUpdate.Text;
    SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ravissantCon"].ConnectionString);
    con.Open();
    string getUser = "SELECT category FROM brands WHERE brandName = '" + getBrandForUpdate.Text + "'";

    SqlCommand cmd = new SqlCommand(getUser, con);
    SqlDataReader sdr = cmd.ExecuteReader();

    if (sdr.HasRows)
    {
        while (sdr.Read())
        {
            getCategoryOfBrand.SelectedValue = sdr["category"].ToString();
        }
    }

    sdr.Close();
    con.Close();
}


I got it fixed by myslef :) the problem was that the first letter is capital in category table and small in brand's category column

I noticed the capitol P as well. You should really be thinking of parameterized queries. Injecting SQL into a query is very risky, especially if you're are not limiting the input. You are just taking the text, not the SelectedItem.ToString();

public String getCategoryByBrand(String brand){ String sql = "SELECT category FROM brands WHERE brandName = @Brand";
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@Brand", SqlDbType.VarChar).Value = brand;
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ddlBrandCategory.SelectedValue = reader.GetValue(0).ToString();
}
reader.Close();
con.Close();
}
catch (SqlException ex)
{
con.Close();
Console.WriteLine(ex.Message);
}

Thanks Andrew Grinder, Actually this is for testing purpose I am gonna make it through parameters now :), Thank you guys.

I have a autocomplete text filed for brands and a dropDown for category which is populating through database(SQL Server 2008). here is the code for category dropDown
SqlCommand cmd = new SqlCommand("SELECT * FROM category", con);
cmd.Connection.Open();

SqlDataReader ddlValues;
ddlValues = cmd.ExecuteReader();

getCategoryOfBrand.DataSource = ddlValues;
getCategoryOfBrand.DataValueField = "category";
getCategoryOfBrand.DataTextField = "category";
getCategoryOfBrand.DataBind();

cmd.Connection.Close();
cmd.Connection.Dispose();

Now I want that when I select brand it automatically set the selected value of category dropDown = selected Brand's category.
this is brands table.

Category Table

code for textChange event of brand text field
public void getFallingCategory(object sender, EventArgs e)
{

    brandNameUpdated.Text = getBrandForUpdate.Text;
    SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ravissantCon"].ConnectionString);
    con.Open();
    string getUser = "SELECT category FROM brands WHERE brandName = '" + getBrandForUpdate.Text + "'";

    SqlCommand cmd = new SqlCommand(getUser, con);
    SqlDataReader sdr = cmd.ExecuteReader();

    if (sdr.HasRows)
    {
        while (sdr.Read())
        {
            getCategoryOfBrand.SelectedValue = sdr["category"].ToString();
        }
    }

    sdr.Close();
    con.Close();
}

