GetDataTable Object Reference Error

Calling the GetDataTable method of a SPListItemCollection object that was retrieved using a join with projected fields will throw the following error:

Object reference not set to an instance of an object

For example:

SPQuery query = new SPQuery();
query.Query = "<Where><Eq><FieldRef Name="Title"/><Value Type="Text">Search Criteria</Value></Eq></Where>";
query.Joins = "<Join Type="Left" ListAlias="Name of join list"><Eq><FieldRef Name="Title" RefType="Id" /><FieldRef List="Name of join list" Name="Id" /></Eq></Join>";
query.ProjectedFields = "<Field Name='Field name' Type='Lookup' List='Name of join list' ShowField='Field name'/>";
query.ViewFields = "<FieldRef Name="Field Name" /><FieldRef Name="Title" />";
 
// Next line will cause a "Object reference not set to an instance of an object" exception.
DataTable table = listItems.GetDataTable();

Simply access the Fields property prior to calling GetDataTable to resolve this issue:

SPFieldCollection fields = listItems.Fields;
DataTable table = listItems.GetDataTable();

Accessing the Fields property instantiates an object that is used by the GetDataTable method when there are projected fields in the join and the exception is resolved.

2 comments

  1. kesav says:

    awesome fix.. but when we include orderby a projected field in CAML, no error but the projected column on which I am trying to sort does not have any data in the output. any idea?

    • Stu says:

      You could always do the sorting within the data table itself:

      table.DefaultView.Sort = columnToSort + " desc";
      DataTable sortedTable = table.DefaultView.ToTable();

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the maths problem shown below before posting: *

Follow

Get every new post delivered to your Inbox

Join other followers: