SPQuery for Selecting/Excluding Hidden columns from a SPlist

Question

I am trying to take a DataTable containing ID and other columns which are not hidden.

I am doing this for a generic list which contains any number/type of columns. so I don't know the field names which are in the list.

This is the code I am trying to do..

SPWeb myWeb = SPContext.Current.Web;
                    SPList myList = myWeb.Lists[ListName];
                    if (myList != null)
                    { 
                        SPQuery query = new SPQuery();
                        query.Query = "<Where><Or><Eq><FieldRef Name='Hidden' /><Value Type='Bool'>FALSE</Value></Eq><Eq><FieldRef Name='Title' /><Value Type='Text'>ID</Value></Eq><Or></Where>";
                        DataTable dtListSource = myList.GetItems(query).GetDataTable();

I am getting this error now..

One or more field types are not installed properly. Go to the list settings page to delete these fields.

I am working on SharePoint 2007(WSS3) with .NET Framework 2.0, so Linq does not seems available.

Can you tell me what I was doing wrong here? I'm pretty new to SharePoint and I hope I could caml query to SPListItem's properties like this..

Answer 1

The query in your code is searching for items where field named Hidden has value set to TRUE. My guess is that your list doesn't have field named Hidden.

I will answer your question with some code:

//Always make sure you dispose SPSite and SPWeb objects
using (SPSite site = new SPSite("http://mycoolsite"))
{
    using(SPWeb web = site.OpenWeb())
    {
        const string tmpField = "<FieldRef Name='{0}'/>";
        string viewFields = string.Empty;

        SPList myList = web.Lists["myCoolListName"];

        //Get all list fields 
        var listFields = myList.Fields;
        //Build View Fields
        foreach (SPField listField in listFields)
        {
            //Only add if field is not hidden
            if(!listField.Hidden)
                viewFields += string.Format(tmpField, listField.InternalName);
        }

        SPQuery query = new SPQuery();
        query.Query = ""; // You can add some condition. Items returned by query
        query.ViewFields = viewFields; //Fields returned by query

        DataTable dtListSource = myList.GetItems(query).GetDataTable();
    }
}

Some more info on MSDN: SPQuery.ViewFields

Answer 2

I've to add some check boxes for each field in myList.Fields via a ToolPart (CustomPropertyToolPart) in my WebPart and let the user select whichever columns he wants to have in the DataTable.