Add a calulated column to a Datagrid

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a bound datagrid in C# based on an an SQL query.
The results are displayed in a Sharepoint 2003 Webpart.
I would like to add a new column based on the results of two
columns in the datagrid.
It is not possible for me to do this within the SQL query as
the data provider I am using does not support column alias's.

e.g. Put the results of these columns into a new column.

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc1";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc2";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);
 
In addition, there is a good topic on DataColumn.Expression in .net help
files.
 
Hi Miha,
Sorry but I'm very new to this can you give me an example?

Miha Markic said:
You should add the column to datasource, in your case to DataTable.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

GML said:
Hi Sijin,

Thanks for that but how do i add the new colum to my grid.

I get an error if i use :-


DataColumn resultColumn = new DataColumn();
resultColumn.Expression = "pt_dec1+pt_desc2";
resultColumn.ColumnName = "Part Description";
OrdersGrid.Columns.Add(resultColumn);
Click to expand...
Click to expand...
 
Hi,

If I understood your example properly, you were adding a columng to
DataGrid.
Instead, you should be adding a column to the datatable you are using as a
datasource for grid.
I guess somewhere you have something like:
OrdersGrid.Datasource = myDataset;
OrdersGrid.DataMember = "myTableName";

If this is so, then you should do something like:
myDataSet.Tables["myTableName"].Columns.Add(...)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

GML said:
Hi Miha,
Sorry but I'm very new to this can you give me an example?

Miha Markic said:
You should add the column to datasource, in your case to DataTable.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

GML said:
Hi Sijin,

Thanks for that but how do i add the new colum to my grid.

I get an error if i use :-


DataColumn resultColumn = new DataColumn();
resultColumn.Expression = "pt_dec1+pt_desc2";
resultColumn.ColumnName = "Part Description";
OrdersGrid.Columns.Add(resultColumn);


:

You can use the DataColumn.Expression property to add a calculated
property to the DataGrid.

For eg. you can use
DataColumn resultColumn = new DataColumn();
resultColumn.Expression = "pt_dec1+pt_desc2";

Sijin Joseph
http://www.indiangeek.net
http://weblogs.asp.net/sjoseph



GML wrote:
I have a bound datagrid in C# based on an an SQL query.
The results are displayed in a Sharepoint 2003 Webpart.
I would like to add a new column based on the results of two
columns in the datagrid.
It is not possible for me to do this within the SQL query as
the data provider I am using does not support column alias's.

e.g. Put the results of these columns into a new column.

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc1";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc2";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);
Click to expand...
Click to expand...
Click to expand...
 
Hi Miha,

This is what I do :-

OdbcCommand Mfgcmd = new OdbcCommand(MfgSQLString , Mfgconn);
OdbcDataReader Mfgreader = Mfgcmd.ExecuteReader();

OrdersGrid.Enabled = true;
OrdersGrid.DataSource = Mfgreader;
OrdersGrid.DataBind();

George

Miha Markic said:
Hi,

If I understood your example properly, you were adding a columng to
DataGrid.
Instead, you should be adding a column to the datatable you are using as a
datasource for grid.
I guess somewhere you have something like:
OrdersGrid.Datasource = myDataset;
OrdersGrid.DataMember = "myTableName";

If this is so, then you should do something like:
myDataSet.Tables["myTableName"].Columns.Add(...)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

GML said:
Hi Miha,
Sorry but I'm very new to this can you give me an example?

Miha Markic said:
You should add the column to datasource, in your case to DataTable.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi Sijin,

Thanks for that but how do i add the new colum to my grid.

I get an error if i use :-


DataColumn resultColumn = new DataColumn();
resultColumn.Expression = "pt_dec1+pt_desc2";
resultColumn.ColumnName = "Part Description";
OrdersGrid.Columns.Add(resultColumn);


:

You can use the DataColumn.Expression property to add a calculated
property to the DataGrid.

For eg. you can use
DataColumn resultColumn = new DataColumn();
resultColumn.Expression = "pt_dec1+pt_desc2";

Sijin Joseph
http://www.indiangeek.net
http://weblogs.asp.net/sjoseph



GML wrote:
I have a bound datagrid in C# based on an an SQL query.
The results are displayed in a Sharepoint 2003 Webpart.
I would like to add a new column based on the results of two
columns in the datagrid.
It is not possible for me to do this within the SQL query as
the data provider I am using does not support column alias's.

e.g. Put the results of these columns into a new column.

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc1";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc2";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);
Click to expand...
Click to expand...
Click to expand...
 
Hi GML,

Aaaa, asp.net application.
I guess you could add your "calculated column expression" to sql select
statement - it would be the easiest way.
like: select qty, price, qty*price as amount ...
Is this feasible for you?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com


GML said:
Hi Miha,

This is what I do :-

OdbcCommand Mfgcmd = new OdbcCommand(MfgSQLString , Mfgconn);
OdbcDataReader Mfgreader = Mfgcmd.ExecuteReader();

OrdersGrid.Enabled = true;
OrdersGrid.DataSource = Mfgreader;
OrdersGrid.DataBind();

George

Miha Markic said:
Hi,

If I understood your example properly, you were adding a columng to
DataGrid.
Instead, you should be adding a column to the datatable you are using as
a
datasource for grid.
I guess somewhere you have something like:
OrdersGrid.Datasource = myDataset;
OrdersGrid.DataMember = "myTableName";

If this is so, then you should do something like:
myDataSet.Tables["myTableName"].Columns.Add(...)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

GML said:
Hi Miha,
Sorry but I'm very new to this can you give me an example?

:

You should add the column to datasource, in your case to DataTable.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi Sijin,

Thanks for that but how do i add the new colum to my grid.

I get an error if i use :-


DataColumn resultColumn = new DataColumn();
resultColumn.Expression = "pt_dec1+pt_desc2";
resultColumn.ColumnName = "Part Description";
OrdersGrid.Columns.Add(resultColumn);


:

You can use the DataColumn.Expression property to add a calculated
property to the DataGrid.

For eg. you can use
DataColumn resultColumn = new DataColumn();
resultColumn.Expression = "pt_dec1+pt_desc2";

Sijin Joseph
http://www.indiangeek.net
http://weblogs.asp.net/sjoseph



GML wrote:
I have a bound datagrid in C# based on an an SQL query.
The results are displayed in a Sharepoint 2003 Webpart.
I would like to add a new column based on the results of two
columns in the datagrid.
It is not possible for me to do this within the SQL query as
the data provider I am using does not support column alias's.

e.g. Put the results of these columns into a new column.

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc1";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc2";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);
Click to expand...
Click to expand...
Click to expand...
 
Hi Miha,
Thanks for you help so far much appreciated.

No it's not I'm afraid. I'm using an Openlink provider which does not
support alias columns in the SQL select statement.

George

Miha Markic said:
Hi GML,

Aaaa, asp.net application.
I guess you could add your "calculated column expression" to sql select
statement - it would be the easiest way.
like: select qty, price, qty*price as amount ...
Is this feasible for you?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com


GML said:
Hi Miha,

This is what I do :-

OdbcCommand Mfgcmd = new OdbcCommand(MfgSQLString , Mfgconn);
OdbcDataReader Mfgreader = Mfgcmd.ExecuteReader();

OrdersGrid.Enabled = true;
OrdersGrid.DataSource = Mfgreader;
OrdersGrid.DataBind();

George

Miha Markic said:
Hi,

If I understood your example properly, you were adding a columng to
DataGrid.
Instead, you should be adding a column to the datatable you are using as
a
datasource for grid.
I guess somewhere you have something like:
OrdersGrid.Datasource = myDataset;
OrdersGrid.DataMember = "myTableName";

If this is so, then you should do something like:
myDataSet.Tables["myTableName"].Columns.Add(...)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi Miha,
Sorry but I'm very new to this can you give me an example?

:

You should add the column to datasource, in your case to DataTable.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi Sijin,

Thanks for that but how do i add the new colum to my grid.

I get an error if i use :-


DataColumn resultColumn = new DataColumn();
resultColumn.Expression = "pt_dec1+pt_desc2";
resultColumn.ColumnName = "Part Description";
OrdersGrid.Columns.Add(resultColumn);


:

You can use the DataColumn.Expression property to add a calculated
property to the DataGrid.

For eg. you can use
DataColumn resultColumn = new DataColumn();
resultColumn.Expression = "pt_dec1+pt_desc2";

Sijin Joseph
http://www.indiangeek.net
http://weblogs.asp.net/sjoseph



GML wrote:
I have a bound datagrid in C# based on an an SQL query.
The results are displayed in a Sharepoint 2003 Webpart.
I would like to add a new column based on the results of two
columns in the datagrid.
It is not possible for me to do this within the SQL query as
the data provider I am using does not support column alias's.

e.g. Put the results of these columns into a new column.

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc1";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc2";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);
Click to expand...
Click to expand...
Click to expand...
 
GML said:
Hi Miha,
Thanks for you help so far much appreciated.

No it's not I'm afraid. I'm using an Openlink provider which does not
support alias columns in the SQL select statement.
Click to expand...

Ah. As a workaround, you might fill a DataTable (using OdbcDataAdapter.Fill
method), then add the calculated column as suggested at beginning of the
thread and link the table to grid instead of linking reader.
What do you say?
 
Or just create a calculated field within sql statament (as suggested)
without an alias and set the grid's column header directly via:
OrdersGrid.Columns[insert column number].HeaderText.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

GML said:
Hi Miha,
Thanks for you help so far much appreciated.

No it's not I'm afraid. I'm using an Openlink provider which does not
support alias columns in the SQL select statement.

George

Miha Markic said:
Hi GML,

Aaaa, asp.net application.
I guess you could add your "calculated column expression" to sql select
statement - it would be the easiest way.
like: select qty, price, qty*price as amount ...
Is this feasible for you?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com


GML said:
Hi Miha,

This is what I do :-

OdbcCommand Mfgcmd = new OdbcCommand(MfgSQLString , Mfgconn);
OdbcDataReader Mfgreader = Mfgcmd.ExecuteReader();

OrdersGrid.Enabled = true;
OrdersGrid.DataSource = Mfgreader;
OrdersGrid.DataBind();

George

:

Hi,

If I understood your example properly, you were adding a columng to
DataGrid.
Instead, you should be adding a column to the datatable you are using
as
a
datasource for grid.
I guess somewhere you have something like:
OrdersGrid.Datasource = myDataset;
OrdersGrid.DataMember = "myTableName";

If this is so, then you should do something like:
myDataSet.Tables["myTableName"].Columns.Add(...)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi Miha,
Sorry but I'm very new to this can you give me an example?

:

You should add the column to datasource, in your case to DataTable.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi Sijin,

Thanks for that but how do i add the new colum to my grid.

I get an error if i use :-


DataColumn resultColumn = new DataColumn();
resultColumn.Expression = "pt_dec1+pt_desc2";
resultColumn.ColumnName = "Part Description";
OrdersGrid.Columns.Add(resultColumn);


:

You can use the DataColumn.Expression property to add a
calculated
property to the DataGrid.

For eg. you can use
DataColumn resultColumn = new DataColumn();
resultColumn.Expression = "pt_dec1+pt_desc2";

Sijin Joseph
http://www.indiangeek.net
http://weblogs.asp.net/sjoseph



GML wrote:
I have a bound datagrid in C# based on an an SQL query.
The results are displayed in a Sharepoint 2003 Webpart.
I would like to add a new column based on the results of two
columns in the datagrid.
It is not possible for me to do this within the SQL query as
the data provider I am using does not support column alias's.

e.g. Put the results of these columns into a new column.

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc1";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);

newColumn = new BoundColumn();
newColumn.DataField = "pt_desc2";
newColumn.HeaderText = "Description";
OrdersGrid.Columns.Add(newColumn);
Click to expand...
Click to expand...
Click to expand...
 
Back
Top