Asked by:
ListbOX

Question
-
User-1499457942 posted
hI
How to select Multiple items in a ListBox in a Gridview column & save in Database and retreive all values and show saved fields as selected
Thanks
Monday, August 27, 2018 5:07 AM
All replies
-
User-369506445 posted
hi
here I created a sample to store the selected value of ListBox in database
protected void btnStore_Click(object sender, EventArgs e) { string selected = string.Empty; foreach (GridViewRow row in gd.Rows) { if (row.RowType == DataControlRowType.DataRow) { var listBoxes = row.FindControl("lstBox") as ListBox; foreach (ListItem li in listBoxes.Items) { if (li.Selected) { selected + = li.Text + ","; } } } } if (!string.IsNullOrEmpty(selected)) { using (var con = new SqlConnection("connection String")) { using (var com = new SqlCommand("insert into yourTable (yourFieldName) values('" + selected + "')", con)) { con.Open(); com.ExecuteNonQuery(); } } } }
also, you can fetch the selected value from the database and show on the ListBox with below code
protected void btnFetchFormDB_Click(object sender, EventArgs e) { string selected = string.Empty; using (var con = new SqlConnection("connection String")) { using (var com = new SqlCommand("select yourFieldName from yourTable", con)) { con.Open(); using (var reader = com.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { selected = reader.GetString(0); } } else { Response.Write("No rows found."); } } } } if (string.IsNullOrEmpty(selected)) return; var arr = selected.Split(','); foreach (GridViewRow row in gd.Rows) { if (row.RowType == DataControlRowType.DataRow) { var listBoxes = row.FindControl("lstBox") as ListBox; foreach (ListItem li in listBoxes.Items) { if (arr.Any(x => x.Equals(li.Text))) { li.Selected = true; } } } } }
Monday, August 27, 2018 6:12 AM -
User-1171043462 posted
Do you have any Table design for this? How are you saving multiple ListBox values for one GridView record?
Monday, August 27, 2018 8:50 AM -
User-1499457942 posted
Hi
I have listbox in 1 column in gridview as Template field. I have set multiselect = true
Thanks
Monday, August 27, 2018 8:58 AM -
User-1171043462 posted
Database?
Monday, August 27, 2018 9:13 AM -
User-1499457942 posted
Sql
Monday, August 27, 2018 9:23 AM -
User-1171043462 posted
I mean database table design? What tables you have?
Monday, August 27, 2018 3:36 PM -
User283571144 posted
Hi JagjitSingh,
According to your description, I’ve suggest you could set the listbox's selected value in the RowDataBound event.
For more details, you could refer to the code below.
Notice:In my database,”1” means selected, “0” means not selected.
Database Format:
Aspx:
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView_RowDataBound"> <Columns> <asp:TemplateField HeaderText="Name"> <ItemTemplate> <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="ShowList"> <ItemTemplate> <asp:ListBox ID="listItemBox" runat="server" SelectionMode="Multiple"> <asp:ListItem Value="1">Info1</asp:ListItem> <asp:ListItem Value="2">Info2</asp:ListItem> <asp:ListItem Value="3">Info3</asp:ListItem> </asp:ListBox> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" /> </div> </form> </body> </html>
Code behind.
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.Configuration; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { BindData(); } } string constr = ConfigurationManager.ConnectionStrings["EmployeeManagementConnectionString"].ConnectionString; public void BindData() { DataTable dt = new DataTable(); dt.Columns.Add("Name"); using(SqlConnection con=new SqlConnection(constr)) { using(SqlCommand cmd=new SqlCommand("SELECT * FROM tb_ListBoxInfo", con)) { con.Open(); using(SqlDataReader sdr = cmd.ExecuteReader()) { while (sdr.Read()) { DataRow row = dt.NewRow(); row["Name"] = sdr["Name"]; dt.Rows.Add(row); } } } con.Close(); GridView1.DataSource = dt; GridView1.DataBind(); } } protected void GridView_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { string queryname = ((Label)e.Row.Cells[0].FindControl("lblName")).Text; using (SqlConnection con = new SqlConnection(constr)) { string myQuery = "SELECT * FROM tb_ListBoxInfo WHERE Name=@Name"; SqlCommand cmd = new SqlCommand(); cmd.CommandText = myQuery; cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("Name", queryname); cmd.Connection = con; con.Open(); SqlDataReader sdr = cmd.ExecuteReader(); if (sdr.HasRows) { while (sdr.Read()) { var listboxes = e.Row.FindControl("listItemBox") as ListBox; List<string> selectedValue = new List<string>(); if (sdr["info1"].ToString() == "1") { selectedValue.Add("1"); } if (sdr["info2"].ToString() == "1") { selectedValue.Add("2"); } if (sdr["info3"].ToString() == "1") { selectedValue.Add("3"); } foreach (ListItem item in listboxes.Items) { item.Selected = selectedValue.Contains(item.Value); } } } con.Close(); } } } protected void btnSave_Click(object sender, EventArgs e) { foreach(GridViewRow row in GridView1.Rows) { if (row.RowType == DataControlRowType.DataRow) { string queryname = ((Label)row.FindControl("lblName")).Text; var listboxes = row.FindControl("listItemBox") as ListBox; string info1 = ""; string info2 = ""; string info3 = ""; foreach (ListItem item in listboxes.Items) { if (item.Value == "1") { info1 = item.Selected == true ? "1" : "0"; } if (item.Value == "2") { info2 = item.Selected == true ? "1" : "0"; } if (item.Value == "3") { info3 = item.Selected == true ? "1" : "0"; } } using (SqlConnection con = new SqlConnection(constr)) { string myQuery = "UPDATE tb_ListBoxInfo SET Info1=@Info1,Info2=@Info2,Info3=@Info3 WHERE Name=@Name"; SqlCommand cmd = new SqlCommand(); cmd.CommandText = myQuery; cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("Name", queryname); cmd.Parameters.AddWithValue("Info1", info1); cmd.Parameters.AddWithValue("Info2", info2); cmd.Parameters.AddWithValue("Info3", info3); cmd.Connection = con; try { con.Open(); cmd.ExecuteNonQuery(); con.Close(); Response.Write("Success"); } catch(Exception ex) { Response.Write(ex.Message); } } } } }
Result:
Best Regards,
Brando
Friday, August 31, 2018 9:06 AM -
User-1499457942 posted
Hi Brando
It is possible that Listbox should look like Multiselect Bootstrap dropdown.
Thanks
Friday, August 31, 2018 11:27 AM -
User283571144 posted
Hi JagjitSingh,
It is possible that Listbox should look like Multiselect Bootstrap dropdown.Yes, you could.
You could use bootstrap-multiselect plugin to achieve your requirement.
More details, you could refer to below codes:
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.8.3.min.js"></script> <link href="http://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.0.3/css/bootstrap.min.css" rel="stylesheet" type="text/css" /> <script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.0.3/js/bootstrap.min.js"></script> <link href="http://cdn.rawgit.com/davidstutz/bootstrap-multiselect/master/dist/css/bootstrap-multiselect.css" rel="stylesheet" type="text/css" /> <script src="http://cdn.rawgit.com/davidstutz/bootstrap-multiselect/master/dist/js/bootstrap-multiselect.js" type="text/javascript"></script> <script type="text/javascript"> $(function () { $('[id*=listItemBox]').multiselect({ includeSelectAllOption: true }); }); </script> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView_RowDataBound"> <Columns> <asp:TemplateField HeaderText="Name"> <ItemTemplate> <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="ShowList"> <ItemTemplate> <asp:ListBox ID="listItemBox" runat="server" SelectionMode="Multiple"> <asp:ListItem Value="1">Info1</asp:ListItem> <asp:ListItem Value="2">Info2</asp:ListItem> <asp:ListItem Value="3">Info3</asp:ListItem> </asp:ListBox> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" /> </div> </form> </body> </html>
Best Regards,
Brando
Tuesday, September 4, 2018 6:58 AM