Add data From Database to Listbox

Question

In database table i have 3 columns like Text | Values | Order i want to add these columns data to listbox. how to do it.

Sathya Narayan · Answer

PrivateSub List() Dim connection AsNew SqlConnection connection.ConnectionString = " Data Source=SATHYA-PC\SQLEXPRESS;Initial Catalog=Sathya_SQl;Integrated Security=True" connection.Open() Dim da AsNew SqlDataAdapter("Select slno,Text,values,order from Table_name", connection) Dim dt AsNew DataTable() da.Fill(dt) Dim dr As DataRow dr = dt.NewRow() dr.ItemArray = NewObject() {0, " "} dt.Rows.InsertAt(dr, 0) ListBox1.DisplayMember = "Table_name" ListBox1.ValueMember = "slno" ListBox1.DataSource = dt connection.Close() EndSub

Sathya Narayan · Answer

PrivateSub List() Dim connection AsNew SqlConnection connection.ConnectionString = " Data Source=SATHYA-PC\SQLEXPRESS;Initial Catalog=Sathya_SQl;Integrated Security=True" connection.Open() Dim da AsNew SqlDataAdapter("Select slno,Text,values,order from Table_name", connection) Dim dt AsNew DataTable() da.Fill(dt) Dim dr As DataRow dr = dt.NewRow() dr.ItemArray = NewObject() {0, " "} dt.Rows.InsertAt(dr, 0) ListBox1.DisplayMember = "Table_name" ListBox1.ValueMember = "slno" ListBox1.DataSource = dt connection.Close() EndSub

Vulpes · Answer

OK, first of all, change the code which loads the listbox from the database as follows: while (objRs.Read()) { ListItem objList = new ListItem(); objList.Text = objRs["cat_Category"].ToString(); objList.Value = objRs["cat_ID"].ToString() + "," + objRs["cat_OrderBy"].ToString(); listbox1.Items.Add(objList); } Now, change your method which sorts the listbox to this: protected void SortListBox(ListBox lb) { var sorted = from ListItem li in lb.Items let ordBy = li.Value.Split(',')[1] orderby ordBy select li; lb.Items.Clear(); lb.Items.AddRange(sorted.ToArray()); } If cat_OrderBy is a numeric type in the database (say INT), then change the 'sorted' line to: var sorted = from ListItem li in lb.Items let ordBy = int.Parse(li.Value.Split(',')[1] ) orderby ordBy select li; Hopefully, it should now work as you want it to.

samanta · Answer

objCommand.CommandText = "SELECT [cat_ID] " + " ,[cat_Category]" + " ,[cat_OrderBy]" + " FROM [itdatahouse].[dbo].[tblCategory]" + " ORDER BY [cat_OrderBy]"; this is my query

Vulpes · Answer

When you say 'order' do you mean cat_Category (because your query is only returning cat_ID and cat_Category in the latter order) or do you mean some other column in the database? If you do mean cat_Category, then I'd have thought that the remaining items in listBox1 should maintain their original order when the selected items are removed but, if they don't, then you can always call SortListBox(listBox1) to ensure they do. If by 'order' you mean some other column in the database then your query will, of course, need to be changed to return that and to order the returned items accordingly. The only way I can think of to store the additional column in the ListItems is to set their Value property to "Cat_ID,order" (i.e. to combine cat_ID and order using some separator such as comma). You'll then need to parse out the 'order' item when resorting is needed.

samanta · Answer

how can hold order in listitem? because i already added like this while (objRs.Read()) { ListItem objList = new ListItem(); objList.Text = objRs["cat_Category"].ToString(); objList.Value = objRs["cat_ID"].ToString(); listbox1.Items.Add(objList); }

Vulpes · Answer

You need a method to sort the listbox after you've added the new items. Try this which uses LINQ: protected void SortListBox(ListBox lb) { var sorted = from ListItem li in lb.Items orderby li.Text select li; lb.Items.Clear(); lb.Items.AddRange(sorted.ToArray()); } So, to sort the items in listBox2, you need to call this method with: SortListBox(listBox2);

samanta · Answer

protected void btnAddCatagory_Click(object sender, ImageClickEventArgs e) { ListItem objLIst = new ListItem(); int count = listbox1.Items.Count; string[,] lstSource = new string[2, count]; for (int i = 0; i { lstSource[0, i] = listbox1.Items[i].Value; lstSource[1, i] = listbox1.Items[i].Text; } int count2 = lstSource.GetLength(1); ListItem li = null; for (int i = 0; i { if (listbox1.Items[i].Selected) { li = new ListItem(lstSource[1, i], lstSource[0, i]); listbox2.Items.Add(li); } } } this is how i added listbox1 to listbox2. but i want to sort this listbox2 items based on order not id.

Sanjeeb Lenka · Answer

try something like this DataTable table = new DataTable(); table.Columns.Add("Catid", typeof(int)); table.Columns.Add("Cat", typeof(string)); for add a new row DataRow dr = dt.NewRow(); dr["Catid"] = catidfromlistbox1; dr["Cat"] = catgoryfromlistbox1; dt.Rows.Add(dr); to sort it dt.DefaultView.Sort = "Catid desc"; then bind it to listbox2

samanta · Answer

how to do that sir? once submit form listbox2 items ID stored in database.

Sanjeeb Lenka · Answer

then what i am suggesting you is don't add item directly to listbox2. take a List collection add the item to that 1st then sort it and bind it to listbox2.

samanta · Answer

i already got listbox1 items in sorted order by query. but when i add selected listbox1 items to listbox2, listbox2 items must be sorted based on order

Sanjeeb Lenka · Answer

hi, if you want oder by cat_id descending use this: objCommand.CommandText = "SELECT [cat_ID],[cat_Category] FROM [itdatahouse].[dbo].[tblCategory] ORDER BY [cat_ID] desc"; by default its ascending.

samanta · Answer

when i add listbox1 selected items to listbox2, listbox2 items must be sorted based on order only

samanta · Answer

protected void Page_Load(object sender, EventArgs e) { Initalize(); if (!IsPostBack) { LoadCatagory(); } void LoadCatagory() { SqlCommand objCommand = new SqlCommand(); objCommand.CommandType = CommandType.Text; objCommand.Connection = _databaseConnection; objCommand.CommandText = "SELECT [cat_ID] " + " ,[cat_Category]" + " FROM [itdatahouse].[dbo].[tblCategory]" + " ORDER BY [cat_Category]"; SqlDataReader objRs = objCommand.ExecuteReader(); while (objRs.Read()) { ListItem objList = new ListItem(); objList.Text = objRs["cat_Category"].ToString(); objList.Value = objRs["cat_ID"].ToString(); lstcom_Category.Items.Add(objList); } objRs.Close(); objCommand.Dispose();

Sanjeeb Lenka · Answer

share your query what you used. or try like this query select * from employe order by orderid desc

sha ik · Answer

Hi, You can send qurey/in Stored procedure with order by clause,Then you can get your result set in sorting order. Try using list to bind, before adding and deleting items to list box do order in linq query.

samanta · Answer

i am facing problem in sorting by order. when i add selected listbox1 items to listbox2, listbox2 items must be sorted based on order and when i submit my form listbox2 items value must be stored in database. once i removed selected listbox2 items, all selected items must be added back to listbox1 with sorted by order.

Sanjeeb Lenka · Answer

hi you can 1st bind the data to list box like this: partial class _Default : System.Web.UI.Page { string strConnString = "connection string"; SqlConnection con = new SqlConnection(strConnString); string str; SqlCommand com; SqlDataAdapter sqlda; DataSet ds; protected void Page_Load(object sender, System.EventArgs e) { con.Open(); str = "select * from employee"; com = new SqlCommand(str, con); sqlda = new SqlDataAdapter(com); ds = new DataSet(); sqlda.Fill(ds, "employee"); ListBox1.DataValueField = "empid"; ListBox1.DataTextField = "empname"; ListBox1.DataSource = ds; ListBox1.DataMember = "employee"; ListBox1.DataBind(); con.Close(); } } to copy item from one list box to other refer to this link http://www.aspdotnet-suresh.com/2010/12/how-to-move-listitems-from-one-listbox.html

samanta · Answer

no sir, actually i want to add items from listbox1 to listbox2. so i want to add database data to listbox1 which is sorted based on order and value must be preserved. how can i do this?

Sanjeeb Lenka · Answer

instead of listbox better to go for gridview if you want to display multiple columns

samanta · Answer

i want to add only name to listbox and listbox items must be sorted based on order that i have specified and each items value must be preserved. i am using asp.net (c#)

Satyapriya Nayak · Answer

Default.aspx code Untitled Page SelectionMode="Multiple" Width="337px"> Default.aspx.vb code Imports System.Data Imports System.Data.SqlClient Partial Class _Default Inherits System.Web.UI.Page Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString() Dim con As New SqlConnection(strConnString) Dim str As String Dim com As SqlCommand Dim sqlda As SqlDataAdapter Dim ds As DataSet Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load con.Open() str = "select * from employee" com = New SqlCommand(str, con) sqlda = New SqlDataAdapter(com) ds = New DataSet sqlda.Fill(ds, "employee") ListBox1.DataValueField = "empid" ListBox1.DataTextField = "empname" ListBox1.DataSource = ds ListBox1.DataMember = "employee" ListBox1.DataBind() con.Close() End Sub End Class

Add data From Database to Listbox

Answers (23)