locked
MultiSelect ListBox Troubles... RRS feed

  • Question

  • User1106506176 posted

    I am attempting to bind a Listbow whose values and Names come from a table: Flavors so the Stored Proc returns

    1 Fruity

    2 Tangy

    3 Sour

    I also have a product Table that lists all products and a ProductFlavor Table that simple holds the ProductID and the Flavor ID.

    I need the easiest way to bind the ListBox SelectedValue to that table.  I had tried to Fire a single Stroed proc within a loop, but that seemed unneccesary since only them items changes should be updated, Either deleted or added in the table if absent.  (I have a uniqueness constraint spanning the FlavorID and the PRoduct ID so only uniqe combinations can be added.

     

    Any help would be appreciated, since I can add one at a time, but not several at once, nor do the existing values in the table appear PRE-selected as they should when the details view goes into Edit mode.

     

    Thank you!

     

    Dave

    Friday, February 15, 2008 4:58 PM

Answers

  • User-2115483147 posted

    The Story so far...  (perhaps my stored proc is bad? since if the item was already in the table first: I don't see it already highlighten in my list box, and second it doesn't prevent the loop from trying to isert the value a second time (which causes a primary key violation) 

    The real heart of the question I think is what is the best approach for doing this? 

     

    protected
    void FlavorList(object sender, EventArgs e)

    {

    Label ProductNo = dvProduct.FindControl("lblProductID") as Label;

    ListBox listProducts = dvProduct.FindControl("lstProducts") as ListBox;

    int itemCount = listFlavors.Items.Count;

     

    for (int i = 0; i < itemCount; i++)

    {

    if ((listFlavors.Items[i].Selected))

    {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyString"].ConnectionString))

    {

    using (SqlCommand command = new SqlCommand("InsertFlavor", connection))

    {

    command.CommandType =
    CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@ProductNo", int.Parse(ProductNo.Text)));

    command.Parameters.Add(new SqlParameter("@FlavorNo", int.Parse(listFlavors.SelectedValue)));

    connection.Close();

    int value = -1;

    try

    {

    connection.Open();

    object result = command.ExecuteScalar();

    try

    {

    value =
    Convert.ToInt32(result);

    }

    catch (Exception ex)

    {

    saveStatusLabel.Text =
    "Save Failed: The above Flavor failed to save" + ex.Message;

    }

    }

    finally

    {

    saveStatusLabel.Text =
    "Save Successfull: The above Flavor has been saved";

    connection.Close();

    }

    }

    }

    }

    }

    }

     

    Hi iwebcodms,

    So far, I can not find any errors from your code.  So I think that the problem maybe cause of your procedure, too.

    But as solution, I suggest you try to split a SQL statement to solve your problem, see the following code snippet: 

     

        public static string SqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["TakeawayConnectionString"].ToString();
    
        public static void ExecuteNonQuery(string queryString)
        {
            using (SqlConnection connection = new SqlConnection(SqlConnectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
    
        }
        
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            StringBuilder sqlInsert = new StringBuilder();
            sqlInsert.Append("INSERT INTO [News]([title],[news],[imageurl],[detail])VALUES('");        
            sqlInsert.Append(txtTitle.Text);
            sqlInsert.Append("','");
            sqlInsert.Append(txtNews.Text);
            sqlInsert.Append("','");
            sqlInsert.Append(txtImageURL.Text);
            sqlInsert.Append("','");
            sqlInsert.Append(txtDetail.Text);
            sqlInsert.Append("')");
            ExecuteNonQuery(sqlInsert.ToString());
            GridView1.DataBind();
            
        }
    

    And here is the original link:http://forums.asp.net/p/1217613/2176577.aspx

    Hope it helps,

    Hong Gang 

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 20, 2008 2:30 AM

All replies

  • User-1040643152 posted

    Have you tried to create a sql data source and then to the sql datasource add a control parameter to your other list box? 

    Friday, February 15, 2008 5:04 PM
  • User719508094 posted
    Can do this ........ddl is the listbox control ID here .......Do

    ddl.DataSource = //yourDataSource (can be Datatable)

    ddl.DataTextField = "productsColumnHere";

    ddl.DataValueField = "productsIDsColumnHere";

    ddl.DataBind();

    Friday, February 15, 2008 5:20 PM
  • User1106506176 posted

    I guess I wasn't too clear.  It is a MULTISELECT list box. 

    The values for the list box come from a Flavors Table ( FlavorID, Description) 

    The IsSelected state comes from ProductFlavor Table (Select ProductID, FlavorID from ProductFlavor where ProductID is @productid

    After the user changes his selections or adds to them, the values would get either deleted (if the item was deselected), Added (if a new Flavor was chosen in addition to the already selected item in the collection), or Nothing (if selection didn;t change) 

    I had a loop through the count of items in the collection, but am unsure the best way to handle existing (so I don't throw a constraint violation (already exists etc)  

    Hope this helps clarify what I am trying to do.

    Thanks again!

    Dave

    Friday, February 15, 2008 9:22 PM
  • User-1748672519 posted

    Hi iwebcodms,

          To my understanding you are trying to change an table according to a LIstBox's selection.

          My suggest is to check the Selected property of all the listItems in the ListBox. For example:

        foreach (ListItem li in ListBox1.Items )
            {
                if (li.Selected == true)
                {
                    //Response.Write(li.Value.ToString());
                    //Response.Write("</br>");

                    //You can do anything you like here, such as database updating.
                }
            }

    Regards,

    Ivan.

    Sunday, February 17, 2008 10:40 PM
  • User1106506176 posted

    The Story so far...  (perhaps my stored proc is bad? since if the item was already in the table first: I don't see it already highlighten in my list box, and second it doesn't prevent the loop from trying to isert the value a second time (which causes a primary key violation) 

    The real heart of the question I think is what is the best approach for doing this? 

     

    protected
    void FlavorList(object sender, EventArgs e)

    {

    Label ProductNo = dvProduct.FindControl("lblProductID") as Label;

    ListBox listProducts = dvProduct.FindControl("lstProducts") as ListBox;

    int itemCount = listFlavors.Items.Count;

     

    for (int i = 0; i < itemCount; i++)

    {

    if ((listFlavors.Items[i].Selected))

    {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyString"].ConnectionString))

    {

    using (SqlCommand command = new SqlCommand("InsertFlavor", connection))

    {

    command.CommandType =
    CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@ProductNo", int.Parse(ProductNo.Text)));

    command.Parameters.Add(new SqlParameter("@FlavorNo", int.Parse(listFlavors.SelectedValue)));

    connection.Close();

    int value = -1;

    try

    {

    connection.Open();

    object result = command.ExecuteScalar();

    try

    {

    value =
    Convert.ToInt32(result);

    }

    catch (Exception ex)

    {

    saveStatusLabel.Text =
    "Save Failed: The above Flavor failed to save" + ex.Message;

    }

    }

    finally

    {

    saveStatusLabel.Text =
    "Save Successfull: The above Flavor has been saved";

    connection.Close();

    }

    }

    }

    }

    }

    }

    Monday, February 18, 2008 9:07 AM
  • User-2115483147 posted

    The Story so far...  (perhaps my stored proc is bad? since if the item was already in the table first: I don't see it already highlighten in my list box, and second it doesn't prevent the loop from trying to isert the value a second time (which causes a primary key violation) 

    The real heart of the question I think is what is the best approach for doing this? 

     

    protected
    void FlavorList(object sender, EventArgs e)

    {

    Label ProductNo = dvProduct.FindControl("lblProductID") as Label;

    ListBox listProducts = dvProduct.FindControl("lstProducts") as ListBox;

    int itemCount = listFlavors.Items.Count;

     

    for (int i = 0; i < itemCount; i++)

    {

    if ((listFlavors.Items[i].Selected))

    {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyString"].ConnectionString))

    {

    using (SqlCommand command = new SqlCommand("InsertFlavor", connection))

    {

    command.CommandType =
    CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@ProductNo", int.Parse(ProductNo.Text)));

    command.Parameters.Add(new SqlParameter("@FlavorNo", int.Parse(listFlavors.SelectedValue)));

    connection.Close();

    int value = -1;

    try

    {

    connection.Open();

    object result = command.ExecuteScalar();

    try

    {

    value =
    Convert.ToInt32(result);

    }

    catch (Exception ex)

    {

    saveStatusLabel.Text =
    "Save Failed: The above Flavor failed to save" + ex.Message;

    }

    }

    finally

    {

    saveStatusLabel.Text =
    "Save Successfull: The above Flavor has been saved";

    connection.Close();

    }

    }

    }

    }

    }

    }

     

    Hi iwebcodms,

    So far, I can not find any errors from your code.  So I think that the problem maybe cause of your procedure, too.

    But as solution, I suggest you try to split a SQL statement to solve your problem, see the following code snippet: 

     

        public static string SqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["TakeawayConnectionString"].ToString();
    
        public static void ExecuteNonQuery(string queryString)
        {
            using (SqlConnection connection = new SqlConnection(SqlConnectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
    
        }
        
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            StringBuilder sqlInsert = new StringBuilder();
            sqlInsert.Append("INSERT INTO [News]([title],[news],[imageurl],[detail])VALUES('");        
            sqlInsert.Append(txtTitle.Text);
            sqlInsert.Append("','");
            sqlInsert.Append(txtNews.Text);
            sqlInsert.Append("','");
            sqlInsert.Append(txtImageURL.Text);
            sqlInsert.Append("','");
            sqlInsert.Append(txtDetail.Text);
            sqlInsert.Append("')");
            ExecuteNonQuery(sqlInsert.ToString());
            GridView1.DataBind();
            
        }
    

    And here is the original link:http://forums.asp.net/p/1217613/2176577.aspx

    Hope it helps,

    Hong Gang 

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 20, 2008 2:30 AM