Connection between Dropdownlist and Datalist SQL Parameters 1

[satchi]

Hi, I'm having some trouble understanding how to connect a parameter gathered from a dropdownlist web control value and use it to query up w/ a datalist(sql db). Basically I have a drop down list that is filled with CategoryIDs w/ integer type (eg. cid = 1, 2, 3 in database). I then choose a value in this list and then on the bottom of the screen, a series of products is listed via datalist in respect to which category was chosen.

I have been researching on applying parameters but became stump when the value is from another web control. The red text below is what i have so far... I set the @cid to 1 for testing purpose, but want it to be set to the current selected value of the drop down list.

Anyone have any suggestions? Thanks in advance..

protected void BindDataList()
{
        SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString:Products"].ConnectionString);
        string strSql = "SELECT * FROM [Product] WHERE ([cid] = @cid)";
        SqlDataAdapter dataAdapter = new SqlDataAdapter(strSql, sqlConn);
        DataSet dataset = new DataSet();
[COLOR=red]
        //SqlParameter sqlParam = new SqlParameter("@cid", SqlDbType.Int, 1);
[/color]
        dataAdapter.Fill(dataset, "Product");

        dtlProducts.DataSource = dataset.Tables["Product"].DefaultView;
        dtlProducts.DataBind();
        sqlConn.Close();
    }

    protected void BindDropDownList()
    {
        SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString:Products"].ConnectionString);
        string strSql = "SELECT * FROM [Category]";
        SqlDataAdapter dataAdapter = new SqlDataAdapter(strSql, sqlConn);
        DataSet dataset = new DataSet();

        dataAdapter.Fill(dataset, "Category");
        ddlCategory.DataSource = dataset.Tables["Category"];
        ddlCategory.DataTextField = dataset.Tables["Category"].Columns["name"].ColumnName.ToString();
        ddlCategory.DataValueField = dataset.Tables["Category"].Columns["cid"].ColumnName.ToString();
        ddlCategory.DataBind();
        sqlConn.Close();
}

 

[whatsthehampton]

Hi,

You just set the adapter's parameter value to the selected item of the dropdown.

I'm sorry this is VB...

dataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@cid", SqlDbType.Int, 4))
dataAdapter.SelectCommand.Parameters("@cid").Value = ddlCategory.SelectedItem.Value

hth

j

http://www.bst.ac.jp

 

[satchi]

Ah this works great. Thanks!