Sharepoint field not found exception

Question

I am querying a SPList in sharepoint 2010 and populating the corresponding grid view:

<asp:DataGrid runat="server" ID="gridView" AutoGenerateColumns="false" AlternatingRowStyle-BackColor="ActiveBorder" Width="95%" GridLines="Vertical" AllowPaging="true" PageSize="30" CssClass="mGrid" PagerStyle-CssClass="pgr" AlternatingRowStyle-CssClass="alt" >
    <Columns>
<asp:BoundColumn DataField="ID"></asp:BoundColumn>
<asp:BoundColumn DataField="Title"></asp:BoundColumn>
<asp:HyperLinkColumn DataTextField="Name" ></asp:HyperLinkColumn>
<asp:BoundColumn DataField="Surname"></asp:BoundColumn>
<asp:BoundColumn DataField="email"></asp:BoundColumn>
</columns>
</gridview>

If i use this line of code it would imply that when using this along with a grid view it would allow me to view all items found in the SPlist . But I need to be able to show only specific data from the List, for this reason i am using an SPQuery to filter the list with the following code :

            Guid webId = web.ID;
            SPListItemCollection items = list.Items;

            SPList currentTestList = web.Lists[listGuid];

            // query 
            SPQuery oQuery = new SPQuery();
            oQuery.ExpandRecurrence = true;

            oQuery.Query = @"
                <Where>
                    <Eq>
                        <FieldRef Name='Name' />
                        <Value Type='Text'>Adrian</Value>
                    </Eq>
                </Where>
                ";
            gridView.DataSource = list.GetItems(oQuery);
            gridView.DataBind();

yet upon execution it raises the following issue "A field or property with the name 'Surname' was not found on the selected data source". Can someone highlight what is wrong with my code? or am i missing something out? since when I use "getDataTable()" it works fine but on the other hand upon filtering the list the listed error is raised.

UPDATE

---

Updated the ViewFields as instructed yet the same error still persists Current Code:

SPQuery oQuery = new SPQuery();
                oQuery.ExpandRecurrence = true;

                oQuery.Query = @"
                    <Where>
                        <Eq>
                            <FieldRef Name='Name' />
                            <Value Type='Text'>Adrian</Value>
                        </Eq>
                    </Where>
                    ";

                oQuery.ViewFields = @"<FieldRef Name='Name' /><FieldRef Name='Surname' /><FieldRef Name='email' /><FieldRef Name='ID' /><FieldRef Name='Title' />";
                oQuery.ViewFieldsOnly = true;
                //binding it to grid view

                SPListItemCollection filteredList = list.GetItems(oQuery);
                gridView.DataSource = filteredList;
                //gridView.DataSource = list.GetItems(oQuery);
                gridView.DataBind();

Answer 1

Try using SPListItemCollection.GetDataTable like this:

var dt = list.GetItems(oQuery).GetDataTable();
gridView.DataSource = dt;

When you just use list.GetItems(oQuery) as the DataSource what the GridView gets is a SPListItemCollection which it doesn't understand, but it's an IEnumerable which it understands. So now the GridView can enumerate the datasource and get its items back, these are of type SPListItem. The GridView then tries to get each column value as either a field or property of the item it processes. The is OK for ID, Title and Name as these all exists as properties on SPListItem, but there is no property SPListItem.SurName you have to use SPListItem["SurName"] but the GridView doesn't know that.

When you use list.GetItems(oQuery).GetDataTable() as the DataSource what the GridView gets is a DataTable and the GridView knows how to access the Rows and Columns of that.

Answer 2

Are you certain that 'Surname' is the proper internal name of the field? The internal name is what needs to be used during queries.

To quickly find out, you can easily inspect the list using powershell:

$url = "http://www.yoursharepointsite.com/mysite"
$listname = "YourListName"

$site= SPSite ($url) 
$web=$site.OpenWeb() 
$list=$web.Lists[$listname] 
$list.Fields |select ID, title, internalname