locked
ListbOX RRS feed

  • 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