troubleshooting Question

SPGridView with Distinct values showing multiple columns

Avatar of vpit
vpit asked on
Web Languages and StandardsC#ASP.NETMicrosoft SharePoint
17 Comments1 Solution1490 ViewsLast Modified:
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>

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

SPGridView with distinct numbers
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();
}
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 17 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros