Add data From Database to Listbox

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

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

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 < count; 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 < count2; 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.

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

how to do that sir?

once submit form listbox2 items ID stored in database.

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.

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 

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.

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

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




       

share your query  what you used.

or try like this query

select * from employe order by orderid desc

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.

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.

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

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? 

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

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#)  

Default.aspx code

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>





   


   

   

   
   

            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