Asp.net 3.5 class

are you trying to return all of the information from a specific column or filter based on a column
you can just do a select
ds.Tables[0].Select(columnname "= some value");

this might help depending on the datasource
http://blogs.msdn.com/adonet/archive/2007/01/26/querying-datasets-introduction-to-linq-to-dataset.aspx

DataTable filteredCCenter = (from d in ((DataSet)_MyDataset).???????

        DSToReturn.Tables.Add(filteredCostCenter);
        return DSToReturn;

I have to use linq query.  what wil be the syntax in ???? - such that distinct values from dataset column name "business" is extracted to the datatable.  Please provide the linq query code.

Actually the column name is passed to the class method as parameter like this :

project mypj = new project((DataSet)(Session["Client_ProjectDS"]));
                DataSet DS4DDL1 = mypj.DDLPopulate("Business");
So what should be the linq query :
public DataSet DDLPopulate(String columnName)
    {
        DataSet DSToReturn = new DataSet();
        DataTable filteredCCenter = (from d in ((DataSet)_MyDataset).???????

        DSToReturn.Tables.Add(filteredCostCenter);
   
        return DSToReturn;
    }

 IEnumerable<string> query = (IEnumerable<string>)(from d in dt.AsEnumerable()
                                select d["business"]
                         ).Distinct();
            foreach (string s in query)
            {
                //add to a datatable
            }

            //then return

Ok so I got the linq query done and its working except one small problem, I am hardcoding the columnName.  How do I pass the parameter colunName to the linq query instead of  hardcoding "Business"???

DataTable filteredCostCenter = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                                        where (d.Field<string>("Business") != null )
                                        select d).Distinct().CopyToDataTable();

this would also work for the query
 var query =(from d in dt.AsEnumerable()
                                select d.Field<string>("business")).Distinct();


            var query1 =(from d in dt.AsEnumerable()
                                select d["business"]).Distinct();

just put the variable name instead of the hardcoded column

yes, this work as expected: Thx

DataTable filteredCostCenter = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                                        where (d.Field<string>(columnName) != null)
                                        select d).Distinct().CopyToDataTable();

with your query, i think you will get distinct rows, but not distinct column values

yes, which mean that if business column has 3 distinct data/values - Bus1, bus2, bus3, then all those values will show up in DDL.  How can I add in CS code behind in ProjDDL1.DataValueField = "Business";
number that increases 1, 2, 3,  dynamically with the number of business values?

ClientPJ mypj = new ClientPJ((DataSet)(Session["Client_ProjectDS"]));
                DataSet DS4DDL1 = mypj.DDLPopulate("Business");

                ProjDDL1.DataSource = DS4DDL1;
                ProjDDL1.DataTextField = "Business";
                ProjDDL1.DataValueField = "Business";
                ProjDDL1.DataBind();
                ProjDDL1.Items.Insert(0, "--");

but if say you have 3 columns
and you have values (1,2,bus1) (2,3,bus2) (3,4,bus3) (5,6,bus1)
you will get back bus1,bus2,bus3,bus1

add this after the where but before the select
 orderby d[columnName] ascending

After some testing I found out that the values are not distinct  - what is wrong with the linq query, distinct()?
DataTable filteredCostCenter = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                                        where (d.Field<string>(columnName) != null)
                                        select d).Distinct().CopyToDataTable();

Nothing. Like I said u will get distinct rows not column values unless u use one of the queries I gave u I saw an example that did it with a group by lll find it

this might work

DataTable results = (from d in dt.AsEnumerable()
                                    orderby  d[columnName] ascending
                                    group d by d[columnName] 
                                    into g 
                                    select g.First()).CopyToDataTable();

                                              

1:
2:
3:
4:
5:

Yes, initially it gave me error but I added the where clause and its working now, I am getting unique values from column

DataTable filteredCostCenter = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                                        where (d.Field<string>(columnName) != null)
                                        orderby d[columnName] ascending
                                        group d by d[columnName]
                                            into g
                                            select g.First()).CopyToDataTable();

I have to add another method in this class to search for matching text selected in any of the DDL or text box in another field in same dataset.  Have to use ling query:
 public DataSet searchPJ(String ProjectName)
    {
        DataSet DSToReturn = new DataSet();
        //linq query to filter Project column based on search string selected in DDL or textbox
     
        return DSToReturn;
    }

can Jquery help?  Any idea? also should I open another question for this search?  Thanks a lot for helping with the distinct linq Query.  

do you need distinct values again? if not
DataTable results = from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                                 orderby d.Field<string>("Project") ascending
                                 where d["Project"].ToString().Equals(ProjectName)
                                 select d).CopyToDataTable();

if you dont care about case

 DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                                orderby d.Field<string>("Project") ascending
                                 where d["Project"].ToString().Equals(ProjectName, StringComparison.CurrentCultureIgnoreCase )
                                 select d).CopyToDataTable();

not sure about jquery

onselect change event for dropdownlist, the selected text is passed to search method.  But nothing is happening.  - something wrong with the string that I am passing?
DataSet DS4DDL1 = mypj.searchPJ("ProjectDDL1");
protected void ProjDDL1_SelectedIndexChanged(object sender, EventArgs e)
    {
        String ProjectDDL1 = ProjDDL1.SelectedItem.Text;
        ClientPJ mypj = new ClientPJ((DataSet)(Session["Client_ProjectDS"]));
        DataSet DS4DDL1 = mypj.searchPJ("ProjectDDL1");
        ProjList.Items.Clear();
        foreach (DataRow dr in DS4DDL1.Tables[0].Rows)
        {
           
            if (dr[0].ToString().Trim().Length > 0)
               
                ProjList.Items.Add(new ListItem(string.Format("{0}", dr[0].ToString())));
        }

    }

Can this query seek partial search string in dataset?  DDL selection has substring not full project name.  So user selects teh substring in DDL and the listbox gets filtered with matching proj name.  

just change the Equals in my query to one of these :Containts,StartsWith, or EndsWith or IndexOf >-1

I am using this one and its giving syntax error when I tried replacing equlas to contains.
DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                                orderby d.Field<string>("Project") ascending
                                 where d["Project"].ToString().Contains(ProjectName, StringComparison.CurrentCultureIgnoreCase )
                                 select d).CopyToDataTable();

contains only takes 1 argument remove the ,StringComparison.CurrentCultureIgnoreCase

If the serach does not return any result I am getting a compilation error.  How can I check for empty dataset?
DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                             orderby d.Field<string>("Name") ascending
                             where d["Name"].ToString().Contains(ProjectName)
                             select d).CopyToDataTable();

also I have to add the ignorecase in contains.  

How can I pass listbox datasource to the method:


        DataSet DS4DDL1 = mypj.searchPJ(listboxdatasource, searchText);

just check if the dataset==null or the dataset.table["TableName"]==null

2 ways to do the ignore case
DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                             orderby d.Field<string>("Name") ascending
                             where d["Name"].ToString().ToLower().Contains(ProjectName.ToLower())
                             select d).CopyToDataTable();

or

DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                             orderby d.Field<string>("Name") ascending
                             where d["Name"].ToString().IndexOf("", ProjectName.CurrentCultureIgnoreCase)>-1
                             select d).CopyToDataTable();

3rd if you know the type you can just type it in the method like

public DataSet searchPJ(DataSet ds,String ProjectName)
or
public DataSet searchPJ(DataTable dt,String ProjectName)

if not
public DataSet searchPJ(object source,String ProjectName)
and then you would have to check its type and then do something with it

I tried it this way and the datasource that I am passing is null, even though it not null:  How can I pass datasource to this method?
public DataSet searchPJ(DataSet listb, String ProjectName)
    {
        DataSet DSToReturn = new DataSet();
        //logic to filter based on the column NAme
        DataTable results = (from d in ((DataSet)listb).Tables["Records"].AsEnumerable()
                             orderby d.Field<string>("Name") ascending
                             where d["Name"].ToString().Contains(ProjectName)
                             select d).CopyToDataTable();
           
        DSToReturn.Tables.Add(results);
        return DSToReturn;
    }
Method--
 protected void ProjDDL1_SelectedIndexChanged(object sender, EventArgs e)
    {
        String ProjectDDL1 = ProjDDL1.SelectedItem.Text;
        ClientPJ mypj = new ClientPJ((DataSet)(Session["Client_ProjectDS"]));
        //DataSet DS4DDL1 = mypj.searchPJ("chemistry");
        ListBox listBox = new ListBox();


        DataSet DS4DDL1 = mypj.searchPJ((DataSet)(DDLProjectAssociation.DataSource), "chemistry");
        DataSet DS4DDL1 = mypj.searchPJ((DataSet)(ProjList.DataSource), "chemistry");
        ProjList.Items.Clear();
        foreach (DataRow dr in DS4DDL1.Tables[0].Rows)
        {
                if (dr[0].ToString().Trim().Length > 0)
               ProjList.Items.Add(new ListItem(string.Format("{0}", dr[0].ToString())));
        }

    }

if the listbox's databind is not called before you try this
public DataSet searchPJ(DataSet listb, String ProjectName)
it will show up as null, since it doesnt have a datasource anymore, it is just reading the values from viewstate

also it looks like in your method you are just adding the items and not actually binding them, so there is no datasource

Method should do this I don't know what the code wil be- Please help

protected void ProjDDL1_SelectedIndexChanged(object sender, EventArgs e)
    {
        String SearchStr = ProjDDL1.SelectedItem.Text;
        // Code to get the listbox ProjList datasource?

        ClientPJ mypj = new ClientPJ((DataSet)(Session["Client_ProjectDS"]));
       
        DataSet DS4DDL1 = mypj.searchPJ((DataSet)(ProjListDataSource???), SearchStr);
       
        ProjList.Items.Clear();
       // Code to check if not Null then update the listbox or databind the list box with the search results?
        foreach (DataRow dr in DS4DDL1.Tables[0].Rows)
        {
                       if (dr[0].ToString().Trim().Length > 0)
                        ProjList.Items.Add(new ListItem(string.Format("{0}", dr[0].ToString())));
        }

    }

wouldnt the source be the items from this ?
((DataSet)(Session["Client_ProjectDS"])

yes, only on page load, but after that when 1st dropdown selects the listbox will filter when the 2nd DDL will select the 1st DDL's filtered listbox datasource will be passed. there are 5 DDLs and there is no sequence on DDL selection, i.e. user can select from 4th or 2nd and then select from 3 or 5th, the listbox item will remember the previous filter.  So that's why I am trying to pass the listbox datasource to the method for searching.

you could also store the filtered dataset in session

Sadly, that is not accepted, becaz it wil slow the performance, and this prosal of mine was turned down.

So coming back to the same old problem,  I am passing list items instead of source now.
DataSet DS4DDL1 = mypj.searchPJ(ProjList.Items, "*Prefunded");

But in method getting stucked on linq query to search listcollection litems, any idea how to search list colltection? :
 public DataSet searchPJ(ListItemCollection projList, String ProjectName)
    {
        DataSet DSToReturn = new DataSet();
               DataTable results = (from d in (projList.????
                             orderby d.Field<string>("Name") ascending
                             where d["Name"].ToString().Contains(ProjectName)
                             select d).CopyToDataTable();
        select d).CopyToDataTable();
        DSToReturn.Tables.Add(results);
        return DSToReturn;
    }

Is it possible to convert ListItemCollection to dataset?

all you will get back is a list item
but you can do
 ListItem item = projList.FindByText("ProjectName");
or
 ListItem item = projList.FindByValue("ProjectName");

So if there are more than 1 match the listItem Item will have all of them.  If that works then how can I databind the listbox with his set of filtered items?

This won't work, becaz it does not allow partial search.  This method does not do partial searches or wildcard searches

OK< so coming back again - is there any way i can store listitemcollection to dataset or datatable?

On return returnItems;  I am getting error - cannot convert to dataset.

you would have to change the return type to ListItemCollection
dataset or tables are for data so you would have to loop through the items and create the rows and values

I changed it to
public ListItemCollection searchPJ(ListItemCollection projList, String ProjectName)

But how to I display or bind the returned collection to listbox?
ListItemCollection DS4DDL1 = mypj.searchPJ(ProjList.Items, "*Prefunded");
ProjList.Items.Clear();
ProjList.bind??

ProjList.Datasource=DS4DDL1;
ProjList.Databind()
or loop throught the collection and add them

Maybe it just something small I am missing:
error
  Message="DataBinding: 'System.Web.UI.WebControls.ListItem' does not contain a property with the name 'Name'."

Listbox has name and value.
 

Text and Value for the items

When I am bind listbox in pagload
ProjList.DataSource = DS4DDL1;
        ProjList.DataTextField = "Name";
        ProjList.DataValueField = "Value";
        ProjList.DataBind();

When I am rebinding it again with seacrh results -
ProjList.DataSource = DS4DDL1;
        ProjList.DataTextField = "Name";
        ProjList.DataValueField = "Value";
        ProjList.DataBind(); <---- giving error - does not contain property with name "Name"
What is missing here?

Does that mean in function search, something needs to be added to include the name and value properties in Listbox collection being returned?

istItemCollection returnItems= new ListItemCollection();
             foreach (ListItem item in projList)
             {
                 if (item.Text.IndexOf(ProjectName, StringComparison.CurrentCultureIgnoreCase)>-1)
                 {
                     returnItems.Add(item);
                 }
             }

            return returnItems;

ProjList.DataTextField = "Text";
        ProjList.DataValueField = "Value";

if you are using the same bind, you will need to create a datatable with name and value columns and create the rows.  you can also create and extension method for the listitem called name, but those would ahve to be in a new question

I think that will be fair to open a new question.  Please reply to the new thread because you have help me all thru this question and are familiar with this it.  

Before I open a new questu=ion, need some help in understanding what is diffrence between
ProjList.DataTextField = "Text";
        ProjList.DataTextField = "Name";
Name is a field in dataset that binds the list box in pageload.  Does this mean that when it rebinded after search listbox does not have Name and Valueids in it?  

that is because you datatable most likely has columns named name and value.

a listitem only has properties that are Text and Value so you cant bind to Name if it has no Name property

post the link in this question

Just trying to see if we don't have to go thru datatable route:

if listitem has text and value, then is it possible to assign value to the rebinded listbox with original value coming from session variable dataset?  if that is possible, then I only need this value id.  While text wil display the filtered matching item in listbox, values of these items will be the value id passed in original dataset.  the selected value in listbox will be needed on page submit.  Is it possible to reatin the value at least in listbox?

Can teh original valueid can be added to listbox item being returned after search?
if (item.Text.IndexOf(ProjectName, StringComparison.CurrentCultureIgnoreCase)>-1)
                 {
                     returnItems.Add(item);
                    // add valueid to listitem
                 }

try this for the method i dont have much time left for today
 public DataTable searchPJ(ListItemCollection projList, String ProjectName)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("Value");

            foreach (ListItem item in projList)
            {
                if (item.Text.IndexOf(ProjectName, StringComparison.CurrentCultureIgnoreCase) > -1)
                {
                    DataRow dr = dt.NewRow();
                    dr["Name"] = item.Text;
                    dr["Value"] = item.Value;
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

I checked the view source and looks like its retaining the values.  That great!!!  
This not the end though, I wil post another qustion and provide link in this one.  Thx :)

I have posted a question at
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_24814965.html
I will keep this question open for a while, just incase something needs to change.  But overall it was a very helpful solution.  Thx.

Ran into a serious problem today with the listbox search.  there are 2 dropdown out of 5 that will not search for matching text in listbox but in the dataset behind it, when matching text is found it will populate listbox with dataset.  this filtered dataset wil be ssearched by other DDL as well.  Should I open another question, becaz this solution will not work :(.  Need help ASAP.

sounds like you need more than one search method since not all of them are searching on the same criteria go ahead and open a new question because that is something different

Please help, I am in seroius time crunch now, I thought the problem was solve, now coming back to zero again. http://www.experts-exchange.com/Programming/Languages/.NET/Q_24816360.html

The problem would have been solved if listbox datasource can be passed and searced in webmethod.  Is it possible?

Is this possible to get valueid from this matching list items and then search data from original dataset? For example in the code :
 public DataSet searchPJ(ListItemCollection projList, String ProjectName)
    {
        DataSet DSToReturn = new DataSet();
        //ListItem item = projList.FindByText("ProjectName");
        ListItemCollection returnItems = new ListItemCollection();
        foreach (ListItem item in projList)
        {
            if (item.Text.IndexOf(ProjectName, StringComparison.CurrentCultureIgnoreCase) > -1)
            {
                returnItems.Add(item); <-----------how can I get valueIDs from listbox with the matching selection instead of text?
// then linq query to search these valueids in dataset _mydataset, extract field name Client_owner and return text value to listbox from dataset field "Name".
dataset structure is :
//Name       Client_owner      Business_owner

           }
        }

        return returnItems;
               
    }

Please help asap. Thx.