vpit

asked on 12/27/2012

SPGridView with Distinct values showing multiple columns

I am trying to pull distinct rows from a SharePoint list, but can only successfully pull a single column. I am using an SPGridView to display my data inside a user control. I am not using web parts. The 2 columns I am targeting are text and a hyperlink column. I am also using SPQuery to grab the distinct values of a list column, but need to display multiple columns.

My goal is to create a single-column grid that a user can click a row to select the item and pass it into a querystring. If not possible, then my second hope is to create a 2-column grid containing the name (text field) and a link (hyperlink field) which would link to a new page holding the row data in the link.

My successful, single-column query and SPGridView is coded as follows:

ASPX:

<SharePoint:SPGridView ID="grdPropertyValues" runat="server" AlternatingRowStyle-BackColor="#99CCFF" AutoGenerateColumns="false" 
    RowStyle-BackColor="#66CCFF" AllowFiltering="true" AllowPaging="false" AllowSorting="false" BorderColor="Black" >
   
</SharePoint:SPGridView>

Open in new window

Code-behind:

SPSite site = new SPSite("http://abcdev");
using (SPWeb web = site.OpenWeb())
{
SPList clientlist = web.Lists.TryGetList("clientmatter");
if(clientlist == null)
{
return;
}          

SPListItemCollection results = clientlist.Items;
SPListItemCollection spListItem;
SPQuery query = new SPQuery();
query.Query = "<OrderBy><FieldRef Name='clientid' /></OrderBy>";
DataTable dt = new DataTable();
dt = clientlist.GetItems(query).GetDataTable();
DataView dv = new DataView(dt);
DataTable tbl = dv.ToTable(true, "clientid");

BoundField colTitle = new BoundField();
this.grdPropertyValues.DataSource = tbl;
colTitle.DataField = "clientid";
colTitle.HeaderText = "clientid";
this.grdPropertyValues.Columns.Add(colTitle);
this.Controls.Add(grdPropertyValues);
this.grdPropertyValues.DataBind();
}

Open in new window

Although the above code works, it does not meet my requirements.

There is no way for a user to select a list item or row

SPGridView of Distinct clientid field

When I attempt to add a second column I get errors depending, I believe, on the query or the table structure:

cannot load page. cannot find XML file - which tells me that the query was written poorly and didn't create the XML file
Object not set to the instance of an object - which tells me I am trying to use something not instantiated yet ... but what?

Here is my attempt to add 2 columns: a text column ("cliendid") and a hyperlink column ("Client") (I would use a button also, but I don't know how):

SPSite site = new SPSite("http://abcdev");
using (SPWeb web = site.OpenWeb())
{
SPList clientlist = web.Lists.TryGetList("clientmatter");
if(clientlist == null)
{
return;
}          

SPListItemCollection results = clientlist.Items;
SPListItemCollection spListItem;
SPQuery query = new SPQuery();
query.Query = "<OrderBy><FieldRef Name='clientid' /></OrderBy><Where><Eq><FieldRef Name='clientid' /><FieldRef Name='Client' /></Eq></Where>";
DataTable dt = new DataTable();
dt = clientlist.GetItems(query).GetDataTable();
DataView dv = new DataView(dt);
DataTable tbl = dv.ToTable(true, "clientid");

BoundField colTitle = new BoundField();
this.grdPropertyValues.DataSource = tbl;
colTitle.DataField = "clientid";
colTitle.HeaderText = "clientid";
this.grdPropertyValues.Columns.Add(colTitle);

HyperLinkField hlf = new HyperLinkField();
hlf.HeaderText = "Header Text";
hlf.DataNavigateUrlFields = new string[] { "Client" };
hlf.DataNavigateUrlFormatString = "http://google.com?q={0}";
hlf.DataTextField = "Client";
this.grdPropertyValues.Columns.Add(hlf);

// I tried creating a 'colLink' field but it failed too
//BoundField colLink = new BoundField();
//this.grdPropertyValues.DataSource = tbl;
//colLink.DataField = "Client";
//colLink.HeaderText = "Client";   
//this.grdPropertyValues.Columns.Add(colLink);

this.Controls.Add(grdPropertyValues);
this.grdPropertyValues.DataBind();
}

Open in new window

Microsoft SharePointC#Web Languages and StandardsASP.NET

Last Comment

vpit

8/22/2022 - Mon

vpit

1/2/2013

ASKER

Is there a better way to go about this problem? Should I not use an SPGridView and try another type of grid? How I actually get to the solution is not as important as the solution itself. All I need is a grid that allows me to select a row. A value from a field in that row will act as the ID of the row to send data to a querystring. So whether the row itself is click-able or if there is a button or hyperlink text is okay. I am currently using jQuery to filter the list of rows (see image).

So, as a user types in the textbox the SPGridView will automatically filter the grid and reduce the rows. I want to be able to choose a row to see the details of the passed in as a querystring.

Any ideas?

vpit

1/8/2013

ASKER

I had some help from Kit Menke who assisted me with some great code, however, it was targeting Visual Studio 2007 and not version 2010 (which I am using). Kit's original post on SPGridViews is at his blog http://kitmenke.com/blog/2010/02/18/editing-in-sharepoints-spgridview/

how can I fix the code to work correctly? I will include some screenshots of my Solution Explorer and of the errors I am getting.

Here is my class file "SimpleSPGrid.cs"

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.UI.WebControls;
using Microsoft.SharePoint.WebControls;
using System.Collections;
using SimpleLogic;
 
namespace SimpleLogic.ControlTemplates.SimpleLogic
{
    class SimpleSPGrid
    {
        public SimpleLogic _logic;
        public ObjectDataSource _gridDS;
        public SPGridView _grid;

        public SimpleSPGrid(SimpleLogic logic)
        {
            _logic = logic;
        }

        protected sealed override void CreateChildControls()
        {

            const string GRIDID = "grid";
            const string DATASOURCEID = "gridDS";

            _gridDS = new ObjectDataSource();
            _gridDS.ID = DATASOURCEID;
            _gridDS.TypeName = typeof(SimpleLogic).AssemblyQualifiedName;
            _gridDS.ObjectCreating += new ObjectDataSourceObjectEventHandler(gridDS_ObjectCreating);
            _gridDS.SelectMethod = "Select";
            _gridDS.UpdateMethod = "Update";
            _gridDS.Updating += new ObjectDataSourceMethodEventHandler(gridDS_Updating);

            Panel1.Controls.Add(_gridDS);

            _grid = new SPGridView();
            _grid.ID = GRIDID;
            _grid.DataSourceID = _gridDS.ID;
            _grid.AutoGenerateColumns = false;
            _grid.DataKeyNames = _logic.GetDataKeyNames();

            CommandField command = new CommandField();
            command.ShowEditButton = true;
            _grid.Columns.Add(command);

            foreach (BoundField column in _logic.GetColumns())
            {
                _grid.Columns.Add(column);
            }
            
            Panel1.Controls.Add(_grid);
        }

        void gridDS_Updating(object sender, ObjectDataSourceMethodEventArgs e)
        {
            Dictionary<string, string> data = new Dictionary<string, string>();

            foreach (DictionaryEntry entry in e.InputParameters)
            {
                string value = entry.Value == null ? null : entry.Value.ToString();
                data.Add(entry.Key.ToString(), value);
            }

            e.InputParameters.Clear();
            e.InputParameters.Add("data", data);
        }

        private void gridDS_ObjectCreating(object sender, ObjectDataSourceDisposingEventArgs e)
        {
            e.ObjectInstance = _logic;
        }
    }
}

Open in new window

Here is the code-behind for my "SimpleLogic.ascx.cs" file:

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Xml.Serialization;


namespace SimpleLogic.ControlTemplates.SimpleLogic
{
    [ToolboxData("<{0}:SimpleLogic runat=server></{0}:SimpleLogic>")]
    [XmlRoot(Namespace = "SimpleLogic")]
    public partial class SimpleLogic : UserControl
    {
        const string LIST_NAME = "clientmatter";
        string[] LIST_COLUMNS = null;
        string VIEW_FIELDS = string.Empty;
        string[] DATA_KEY_NAMES = null;
        private SPSite site = new SPSite("http://abcdev");
        private SPWeb _currentweb;
        private SPWeb sPWeb;
        
        protected void Page_Load(object sender, EventArgs e)
        {
            SimpleLogic logic = new SimpleLogic();
            SimpleSPGrid grid = new SimpleSPGrid(logic);
            Panel1.Controls.Add(grid);
        }

        protected override void CreateChildControls()
        {
            try
            {
                // Loads a user control
                SimpleLogic myUserControl = (SimpleLogic)Page.LoadControl("~/_controltemplates/SimpleLogic/SimpleLogic.ascx");

                // Adds it to the controls collection of the Web Part 
                this.Controls.Add(myUserControl);
            }
            catch (Exception e)
            {
                this.Controls.Add(new Label()
                {
                    Text = e.ToString()
                });
            }
        }

        public string[] GetDataKeyNames()
        {
            return DATA_KEY_NAMES;
        }

        public List<BoundField> GetColumns()
        {
            List<BoundField> fields = new List<BoundField>();
            fields.Add(GetBoundField("ID"));
            fields[0].ReadOnly = true;
            fields.Add(GetBoundField("Title"));
            fields.Add(GetBoundField("clientid"));
            fields.Add(GetBoundField("Client"));
            return fields;
        }

        private BoundField GetBoundField(string name)
        {
            BoundField bf = new BoundField();
            bf.HeaderText = name;
            bf.DataField = name;
            return bf;
        }

        public DataTable Select()
        {
            SPQuery q = new SPQuery();
            q.ViewFields = VIEW_FIELDS;
            q.Query = "<Where><IsNotNull><FieldRef Name='clientid' /></IsNotNull></Where>";
            SPList list = _currentweb.Lists[LIST_NAME];
            SPListItemCollection items = list.GetItems(q);
            return items.GetDataTable();
        }

        private string GetViewFields(string[] columns)
        {
            StringBuilder sb = new StringBuilder();
            foreach (string field in columns)
            {
                sb.AppendFormat("<FieldRef Name='{0}' />", field);
            }
            return sb.ToString();
        }

        public void Update(Dictionary<string, string> data)
        {
            SPList list = _currentweb.Lists[LIST_NAME];
            int id = Int32.Parse(data["ID"]);
            SPListItem item = list.GetItemById(id);
            item["Title"] = data["Title"];
            item["clientid"] = data["clientid"];
            item["Client"] = data["Client"];
            item.Update();
        }
    }
}

Open in new window

Here is the code to the "SimpleLogic.aspx" file:

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
<%@ Import Namespace="Microsoft.SharePoint" %> 
<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="SimpleLogic.ascx.cs" Inherits="SimpleLogic.ControlTemplates.SimpleLogic.SimpleLogic" %>


<asp:Label ID="Label1" runat="server">SPGridView Test</asp:Label>
<asp:Panel ID="Panel1" runat="server">    
</asp:Panel>

Open in new window

Here is a screenshot of my error output:

Here is a screenshot of my "Solution Explorer":

I look forward to any and all help.
Thanks.

Bob Learned

1/8/2013

The one thing that I see is that SimpleSPGrid doesn't have a base class, so you can't override this line:

protected sealed override void CreateChildControls()

Open in new window

Should that have inherited from another control (or UserControl)?

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

vpit

1/9/2013

ASKER

I believe I need to inherit from a UserControl since I am ultimately deploying this as a .NET UserControl in SharePoint. How do I fix the code so that it does inherit from a UserControl?

Bob Learned

1/9/2013

There is a SimpleLogic class that provides the example:

    [ToolboxData("<{0}:SimpleLogic runat=server></{0}:SimpleLogic>")]
    [XmlRoot(Namespace = "SimpleLogic")]
    public partial class SimpleLogic : UserControl

Open in new window

vpit

1/9/2013

ASKER

Yes, I do see that code, however, I don't know how to implement it so that it is referenced or inherited. Can you explain the steps for me?

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Bob Learned

1/9/2013

You need to open the class file for SimpleSPGrid.cs, and change the class definition:

    class SimpleSPGrid
    {

Open in new window

to this:

     [ToolboxData("<{0}:SimpleSPGrid runat=server></{0}:SimpleSPGrid>")]
    public partial class SimpleSPGrid : UserControl

Open in new window

vpit

1/9/2013

ASKER

Ok,, I have done that. Here is what I've added/changed on the "SimpleSPGrid.cs" file:

I have added this using statement

using System.Web.UI;

Open in new window

I have changed the class definition

[ToolboxData("<{0}:SimpleSPGrid runat=server></{0}:SimpleSPGrid>")]
    public partial class SimpleSPGrid : UserControl

Open in new window

But I still get 4 errors:
1. No overload for 'gridDS_ObjectCreating' matches delegate ...
2. Property or indexer 'System.Web.UI.WebControls.ObjectDataSourceDisposingEvent ...'
3. The name 'Panel1' does not exist in the current context
4. same as 3

I have also removed the following code from the "SimpleLogic.ascx.cs" page

[ToolboxData("<{0}:SimpleLogic runat=server></{0}:SimpleLogic>")]
    [XmlRoot(Namespace = "SimpleLogic")]

Open in new window

Bob Learned

1/9/2013

I am not sure what gridDS is, but there is the ObjectDataSource.ObjectCreating event handler. Here is one possibility:

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.objectdatasource.objectcreating%28v=vs.100%29.aspx

The delegate is ObjectDataSourceObjectEventHandler. The signature for the event handler is:

public delegate void ObjectDataSourceObjectEventHandler(
	Object sender,
	ObjectDataSourceEventArgs e
)

Open in new window

You need to determine what the object type is for gridDS, and then determine the event handler delegate signature. Then, you need to change the event args type to match the delegate.

Your help has saved me hundreds of hours of internet surfing.

fblack61

vpit

1/11/2013

ASKER

I don't think I am doing this right. Here is what I have now, but still getting same error...
"SimpleSPGrid.cs" file:
1. I added to the main constructor

public delegate void ObjectDataSourceObjectEventHandler(Object sender, ObjectDataSourceEventArgs e);

Open in new window

2. and added to the main constructor

public event ObjectDataSourceObjectEventHandler evHandler;

Open in new window

I don't understand what I need to do next ... (sigh)

Here is the full page of code:

namespace SimpleLogic.ControlTemplates.SimpleLogic
{
    [ToolboxData("<{0}:SimpleSPGrid runat=server></{0}:SimpleSPGrid>")]
    [XmlRoot(Namespace = "SimpleLogic")]
    public partial class SimpleSPGrid : UserControl  
    {
        public SimpleLogic _logic;
        public ObjectDataSource _gridDS;
        public SPGridView _grid;
        public delegate void ObjectDataSourceObjectEventHandler(Object sender, ObjectDataSourceEventArgs e);
        
public event ObjectDataSourceObjectEventHandler evHandler;

        public SimpleSPGrid(SimpleLogic logic)
        {
            _logic = logic;
        }

        protected sealed override void CreateChildControls(object sender, EventArgs e)
        {

            const string GRIDID = "grid";
            const string DATASOURCEID = "gridDS";

            _gridDS.ObjectCreating += new ObjectDataSourceObjectEventHandler(gridDS_ObjectCreating);

            _gridDS = new ObjectDataSource();
            _gridDS.ID = DATASOURCEID;
            _gridDS.TypeName = typeof(SimpleLogic).AssemblyQualifiedName;
            _gridDS.ObjectCreating += new ObjectDataSourceObjectEventHandler(gridDS_ObjectCreating);
            _gridDS.SelectMethod = "Select";
            _gridDS.UpdateMethod = "Update";
            _gridDS.Updating += new ObjectDataSourceMethodEventHandler(gridDS_Updating);

            Panel1.Controls.Add(_gridDS);

            _grid = new SPGridView();
            _grid.ID = GRIDID;
            _grid.DataSourceID = _gridDS.ID;
            _grid.AutoGenerateColumns = false;
            _grid.DataKeyNames = _logic.GetDataKeyNames();

            CommandField command = new CommandField();
            command.ShowEditButton = true;
            _grid.Columns.Add(command);

            foreach (BoundField column in _logic.GetColumns())
            {
                _grid.Columns.Add(column);
            }
            
            Panel1.Controls.Add(_grid);
        }

        void gridDS_Updating(object sender, ObjectDataSourceMethodEventArgs e)
        {
            Dictionary<string, string> data = new Dictionary<string, string>();

            foreach (DictionaryEntry entry in e.InputParameters)
            {
                string value = entry.Value == null ? null : entry.Value.ToString();
                data.Add(entry.Key.ToString(), value);
            }

            e.InputParameters.Clear();
            e.InputParameters.Add("data", data);
        }

        private void gridDS_ObjectCreating(object sender, ObjectDataSourceDisposingEventArgs e)
        {
            e.ObjectInstance = _logic;
        }
    }
}

Open in new window

Bob Learned

1/11/2013

1) You don't need these, since they are already defined, and you are not trying to create an event.

public delegate void ObjectDataSourceObjectEventHandler(Object sender, ObjectDataSourceEventArgs e);

public event ObjectDataSourceObjectEventHandler evHandler;

2) You need to look at this line, and see how the parameter types match up to the delegate.

private void gridDS_ObjectCreating(object sender, ObjectDataSourceDisposingEventArgs e)

You have a type mismatch for the 2nd argument (ObjectDataSourceDisposingEventArgs instead of ObjectDataSourceEventArgs). You can tell that by looking at the parameter types that I posted earlier (Object sender, ObjectDataSourceEventArgs e).

vpit

1/11/2013

ASKER

Alright, I have changed the code for the method as shown:

private void gridDS_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
        {
            e.ObjectInstance = _logic;
        }

Open in new window

Some of the errors have gone away, but the remaining 2 include:
1. SimpleLogic.ControlTemplates ..... : no suitable method found to override
2. The name Panel1 does not exist in the current context.

It appears I am getting these 2 errors because the two files are not communicating. The class file "SimpleSPGrid.cs" is supposed to be talking with the "SimpleLogic.ascx.cs" file, but by the errors the class file cannot find the "CreateChildControls" method on the "SimpleLogic.ascx.cs" page nor the "Panel1" object. If the class file has a method named CreateChildControls which is supposed to override the main code-behind file CreateChildControls method, the question then is why are they not communicating?
Am I understanding the basic principles of the error?

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Bob Learned

1/11/2013

This is where the problems are not so straight-forward. Where is Panel1 defined?

vpit

1/11/2013

ASKER

I have three files:
1. SimpleLogic.ascx
2. SimpleLogic.ascx.cs
3. SimpleSPGrid.cs

The Panel1 is in the SimpleLogic.ascx file.
Here is a screenshot of my Visual Studio Solution Explorer:

Bob Learned

1/11/2013

You can reference Panel1 within the SimpleLogic user control, but not outside that control. You would need to use a FindControl call from the containing control, in order to get a reference to Panel1.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

ASKER CERTIFIED SOLUTION

vpit

2/14/2013

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts