MultiSelect ListBox Troubles...

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

Answer 1

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? 

Answer 2

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();

Answer 3

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

Answer 4

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.

Answer 5

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();

}

}

}

}

}

}

Answer 6

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