Last post Apr 29, 2011 09:22 PM by Decker Dong - MSFT
Apr 27, 2011 03:00 AM|mthakershi|LINK
Custom paging with ObjectDataSouce & GridView uses @sortExpression parameter in the stored procedure. I have some columns as calculated columns (T.Col AS XYZ)
XYZ is being bound to the gridview. How can I sort on this column? When I say sort by XYZ it throws "invalid column name" exception.
Apr 27, 2011 03:56 AM|V.Karthikeyan|LINK
You have two options..
1. Add this calculated field into your datatable and then bind it to the grid
2. Or get the calculated field added from SQL.
DataTable dt = new DataTable(); //datatable from SQL
foreach (DataRow row in dt.Rows)
gridview.DataSource = dt;
Apr 27, 2011 11:26 AM|mthakershi|LINK
Thanks for response.
If calculated field was in DB then I would not have to make it "Calculated" in first place, right?
In the code you've given,
Is there a way I can capture the returned table before it is being bound to the GridView and add a column & assign it value from inside the returned database results?
Apr 27, 2011 11:43 AM|mthakershi|LINK
I tried this code but same problem:
public void ObjectDataSource1_Selected(object source, ObjectDataSourceStatusEventArgs e)
if (e.ReturnValue != null && e.ReturnValue.GetType().Equals(Type.GetType("System.Int32")))
mLngGvTabTotalCount = Convert.ToInt64(e.ReturnValue);
else if (e.ReturnValue != null && !e.ReturnValue.GetType().Equals(Type.GetType("System.Int32")))
//want to add a column here
DataTable dt = (DataTable)e.ReturnValue;
dt.Columns.Add(new DataColumn("TestAdd", Type.GetType("System.String")));
for (int i = 0; i < dt.Rows.Count; i++)
dt.Rows[i]["TestAdd"] = dt.Rows[i]["NewString"].ToString();
dt = null;
mLngGvTabTotalCount = 0;
<asp:BoundField HeaderStyle-HorizontalAlign="Left" HtmlEncode="false" DataField="NewString"
But when I sort this column: I get "Invalid column name: TestAdd" error.
Apr 28, 2011 02:55 PM|mthakershi|LINK
Anyone, is it possible to sort a computed field when I am using:
GridView bound to ObjectDataSource that uses custom paging (thus the stored procedure has @sortExpression) parameter.
What is happening is: when name of a computed field is passed as sort expression, the query throws error because computed fields can't be used in Order By.
Please help / guide / share your views.
Apr 29, 2011 02:19 AM|Decker Dong - MSFT|LINK
In my mind, I think you should write your sql statement like this:
select a,b,c,(a+b-c) as xyz from xxx
And then use SqlDataAdapter and Fill method, you can get the whole DataTable.
And then bind the DataTable to the GridView with ObjectDataSource, And then AllowPaging = True.
Apr 29, 2011 02:19 PM|mthakershi|LINK
I don't think I explaied the issue well.
Here is what the stored procedure has:
DISTINCT ROW_NUMBER() OVER(Order BY ' + @sortExpression + ') AS RowNum ,
a,b,c,(a+b-c) as xyz
So when I specified in the GridView for a column SortExpression="xyz", custom paging mechanism passes "xyz" as @sortExpression parameter. Which substituted in the query throws error because calculated fields can't be used in the order by clause.
Apr 29, 2011 09:22 PM|Decker Dong - MSFT|LINK
For my view, I think becaus "xyz" is a generated class that isn't really exist in the db. So you cannot do that.
I suggest you doing my way——
1) First, write my mentioned Sql statement above.
2) Then use DataAdapter to Fill and use this as a datasource for ObjectDataSource.
3) Then bind the result (DataTable) to the GridView. Just make AllowSorting = True. Everything will be OK. Quite easy.