Adding a column to bound datagrid

[Robert Schuldenfrei]

Dear NG,

After being away from C# programming for a spell, I am trying my hand at
what should be a simple task. I have been hitting my head against the wall
this morning. I have a simple order entry application. The code below gets
line items from a SQL Server database and returns them to a datagrid by way
of a DataTable called lineTable. As long as I am just displaying columns in
the SQL database everything works well. I now want to display a 12th column
computed as extended price. That column is NOT in the table but is computed
by the line: extPrice = Convert.ToDecimal(dr[3]) * Convert.ToDecimal(dr[7]);
As long as I display it in a MessageBox all is well. I have defined a
column 12 using the Collection Editor. I had hoped that by setting the
MappingName property to extPrice I would be home free, but no such luck.
Any help would be appreciated.



Sincerely,

Robert Schuldenfrei ([email protected])



private void btnGet_Click(object sender, System.EventArgs e)

{

decimal extPrice;

coHeader = CustomerOrderTbl.GetCoHeader(txtOrderNo.Text);

if (coHeader == null)

{

MessageBox.Show("Invalid customer order number.", "Entry error");

}

else

{

ShowData(); //display header

//display line items in datagrid

DataTable lineTable = CustomerOrderTbl.GetLines(txtOrderNo.Text);

// Instead of a MessageBox display, I want the extended price to

// appear in column 12 of the data grid. Column [3] is quantity.

// Column [7] is unit price.


foreach (DataRow dr in lineTable.Rows)

{

extPrice = Convert.ToDecimal(dr[3]) * Convert.ToDecimal(dr[7]);

MessageBox.Show(extPrice.ToString());

}

grdLine.DataSource = lineTable;

}

} //end btnGet_Click()

 

[Cor Ligthert [MVP]]

Robert,

You cannot use C# code in a datatable column.
However you can add a column with an expression. I did not check your
expression however maybe can you try that yourself.

http://msdn.microsoft.com/library/d...fsystemdatadatacolumnclassexpressiontopic.asp

I hope this helps,

Cor

 

[Kav]

Dear NG,

After being away from C# programming for a spell, I am trying my hand at
what should be a simple task. I have been hitting my head against the
wall this morning. I have a simple order entry application. The code
below gets line items from a SQL Server database and returns them to a
datagrid by way of a DataTable called lineTable. As long as I am just
displaying columns in the SQL database everything works well. I now want
to display a 12th column computed as extended price. That column is NOT
in the table but is computed by the line: extPrice =
Convert.ToDecimal(dr[3]) * Convert.ToDecimal(dr[7]); As long as I display
it in a MessageBox all is well. I have defined a column 12 using the
Collection Editor. I had hoped that by setting the MappingName property
to extPrice I would be home free, but no such luck. Any help would be
appreciated.



Sincerely,

Robert Schuldenfrei ([email protected])



private void btnGet_Click(object sender, System.EventArgs e)

{

decimal extPrice;

coHeader = CustomerOrderTbl.GetCoHeader(txtOrderNo.Text);

if (coHeader == null)

{

MessageBox.Show("Invalid customer order number.", "Entry error");

}

else

{

ShowData(); //display header

//display line items in datagrid

DataTable lineTable = CustomerOrderTbl.GetLines(txtOrderNo.Text);

// Instead of a MessageBox display, I want the extended price to

// appear in column 12 of the data grid. Column [3] is quantity.

// Column [7] is unit price.


foreach (DataRow dr in lineTable.Rows)

{

extPrice = Convert.ToDecimal(dr[3]) * Convert.ToDecimal(dr[7]);

MessageBox.Show(extPrice.ToString());

}

grdLine.DataSource = lineTable;

}

} //end btnGet_Click()

Hi Robert,

Wouldn't it be easier to add the column to the Table calculate the values
and then bind it?

public void addColumn(string name, string type)
{
DataColumn dc = new DataColumn();
dc.DataType = Type.GetType(type);
dc.ColumnName = name;
this.datatable.Columns.Add(dc);
}

Then foreach row in the table calculate the total and bind it?

Rich.

 

[Robert Schuldenfrei]

Hi Robert,

Wouldn't it be easier to add the column to the Table calculate the values
and then bind it?

public void addColumn(string name, string type)
{
DataColumn dc = new DataColumn();
dc.DataType = Type.GetType(type);
dc.ColumnName = name;
this.datatable.Columns.Add(dc);
}

Then foreach row in the table calculate the total and bind it?

Rich.

Hi Rich,

I think your suggestion was right on target. The code below was written as
per your idea. It executes correctly and the MessageBox reports the correct
values for dr[11]. However, my datagrid only shows the columns from the
unexpanded table (columns 0-10). I used the Collection Editor to give
column 11 a name, but I had no idea what to place in the MappingName so I
left it blank. Is that my problem? In all of the other columns I gave the
MappingName the name of the original SQL column name. For example column 4
(datagrid columns start at 1, not 0) has a MappingName of co_li_QtyOrdered.
This is the DataTable column dr[3].

Thanks in advance,

Bob ([email protected])

private void btnGet_Click(object sender, System.EventArgs e)

{

coHeader = CustomerOrderTbl.GetCoHeader(txtOrderNo.Text);

if (coHeader == null)

{

MessageBox.Show("Invalid customer order number.", "Entry error");

}

else

{

ShowData(); //display header

//display line items in datagrid

DataTable lineTable = CustomerOrderTbl.GetLines(txtOrderNo.Text);

// Instead of a MessageBox display, I want the extended price to

// appear in column 12 of the data grid. Column [3] is quantity.

// Column [7] is unit price.


DataColumn dc = new DataColumn();

dc.DataType = Type.GetType("System.Decimal");

lineTable.Columns.Add(dc);

dc.ColumnName = "Ext. Price";

foreach (DataRow dr in lineTable.Rows)

{

dr[11] = Convert.ToDecimal(dr[3]) * Convert.ToDecimal(dr[7]);

MessageBox.Show(dr[11].ToString());

}

grdLine.DataSource = lineTable;

}

} //end btnGet_Click()

 

[Pete Davis]

Yes. You'll need to give the column a name and add a DataGridTextBoxColumn
with a mapping name that matches to the DataGridTableStyles object your grid
is using.

If you create DataGridColumnStyles for the columns, then the grid will only
display the columns that have matching styles.

Pete

Hi Robert,

Wouldn't it be easier to add the column to the Table calculate the values
and then bind it?

public void addColumn(string name, string type)
{
DataColumn dc = new DataColumn();
dc.DataType = Type.GetType(type);
dc.ColumnName = name;
this.datatable.Columns.Add(dc);
}

Then foreach row in the table calculate the total and bind it?

Rich.

Hi Rich,

I think your suggestion was right on target. The code below was written
as per your idea. It executes correctly and the MessageBox reports the
correct values for dr[11]. However, my datagrid only shows the columns
from the unexpanded table (columns 0-10). I used the Collection Editor to
give column 11 a name, but I had no idea what to place in the MappingName
so I left it blank. Is that my problem? In all of the other columns I
gave the MappingName the name of the original SQL column name. For
example column 4 (datagrid columns start at 1, not 0) has a MappingName of
co_li_QtyOrdered. This is the DataTable column dr[3].

Thanks in advance,

Bob ([email protected])

private void btnGet_Click(object sender, System.EventArgs e)

{

coHeader = CustomerOrderTbl.GetCoHeader(txtOrderNo.Text);

if (coHeader == null)

{

MessageBox.Show("Invalid customer order number.", "Entry error");

}

else

{

ShowData(); //display header

//display line items in datagrid

DataTable lineTable = CustomerOrderTbl.GetLines(txtOrderNo.Text);

// Instead of a MessageBox display, I want the extended price to

// appear in column 12 of the data grid. Column [3] is quantity.

// Column [7] is unit price.


DataColumn dc = new DataColumn();

dc.DataType = Type.GetType("System.Decimal");

lineTable.Columns.Add(dc);

dc.ColumnName = "Ext. Price";

foreach (DataRow dr in lineTable.Rows)

{

dr[11] = Convert.ToDecimal(dr[3]) * Convert.ToDecimal(dr[7]);

MessageBox.Show(dr[11].ToString());

}

grdLine.DataSource = lineTable;

}

} //end btnGet_Click()

 

[Robert Schuldenfrei]

Hi Pete,

Thanks for the information. I am having trouble following your
instructions. Exactly WHAT column name goes into into the MappingName
property? For all of the columns that I see correctly, I have used the SQL
column names in the MappingName property. For example: column 4 (datagrid
columns start at 1, not 0) has a MappingName of co_li_QtyOrdered. This is
the DataTable column dr[3]. Since the new column does not exist in SQL I do
not know what goes in this field. If I leave it blank, as you point out, no
column appears in the datagrid.

Thanks in advance,

Bob ([email protected])

Yes. You'll need to give the column a name and add a DataGridTextBoxColumn
with a mapping name that matches to the DataGridTableStyles object your
grid is using.

If you create DataGridColumnStyles for the columns, then the grid will
only display the columns that have matching styles.

Pete

Hi Robert,

Wouldn't it be easier to add the column to the Table calculate the
values and then bind it?

public void addColumn(string name, string type)
{
DataColumn dc = new DataColumn();
dc.DataType = Type.GetType(type);
dc.ColumnName = name;
this.datatable.Columns.Add(dc);
}

Then foreach row in the table calculate the total and bind it?

Rich.

Hi Rich,

I think your suggestion was right on target. The code below was written
as per your idea. It executes correctly and the MessageBox reports the
correct values for dr[11]. However, my datagrid only shows the columns
from the unexpanded table (columns 0-10). I used the Collection Editor
to give column 11 a name, but I had no idea what to place in the
MappingName so I left it blank. Is that my problem? In all of the other
columns I gave the MappingName the name of the original SQL column name.
For example column 4 (datagrid columns start at 1, not 0) has a
MappingName of co_li_QtyOrdered. This is the DataTable column dr[3].

Thanks in advance,

Bob ([email protected])

private void btnGet_Click(object sender, System.EventArgs e)

{

coHeader = CustomerOrderTbl.GetCoHeader(txtOrderNo.Text);

if (coHeader == null)

{

MessageBox.Show("Invalid customer order number.", "Entry error");

}

else

{

ShowData(); //display header

//display line items in datagrid

DataTable lineTable = CustomerOrderTbl.GetLines(txtOrderNo.Text);

// Instead of a MessageBox display, I want the extended price to

// appear in column 12 of the data grid. Column [3] is quantity.

// Column [7] is unit price.


DataColumn dc = new DataColumn();

dc.DataType = Type.GetType("System.Decimal");

lineTable.Columns.Add(dc);

dc.ColumnName = "Ext. Price";

foreach (DataRow dr in lineTable.Rows)

{

dr[11] = Convert.ToDecimal(dr[3]) * Convert.ToDecimal(dr[7]);

MessageBox.Show(dr[11].ToString());

}

grdLine.DataSource = lineTable;

}

} //end btnGet_Click()

 

[Robert Schuldenfrei]

Dear NG Readers,

I have no idea if anyone follows up on a thread this old, but thanks to
Pete, Cor, Kav, and my own poking around I have solved this problem. The
key concept that I failed to grasp is that I should do all of the work in
code and stay away from the Collections Editor. Establish the DataGrid in
the form's design and do as little as possible in the design's property
sheet. Here is the code that worked:

private void btnGet_Click(object sender, System.EventArgs e)

{

decimal extPrice;

coHeader = CustomerOrderTbl.GetCoHeader(txtOrderNo.Text); //Load header

if (coHeader == null)

{

MessageBox.Show("Invalid customer order number.", "Entry error");

}

else

{

ShowData(); //display header

//Load line items into DataTable from SQL database

DataTable lineTable = CustomerOrderTbl.GetLines(txtOrderNo.Text);

//Label up the column headings

lineTable.Columns[0].ColumnName = "Order";

lineTable.Columns[1].ColumnName = "Seq.";

lineTable.Columns[2].ColumnName = "Part No.";

lineTable.Columns[3].ColumnName = "Ordered";

lineTable.Columns[4].ColumnName = "To Ship";

lineTable.Columns[5].ColumnName = "Shipped";

lineTable.Columns[6].ColumnName = "P/C";

lineTable.Columns[7].ColumnName = "Price";

lineTable.Columns[8].ColumnName = "Due Date";

lineTable.Columns[9].ColumnName = "Note";

lineTable.Columns[10].ColumnName = "Serial No.";

DataColumn dc = new DataColumn(); //New column for ext. price

dc.DataType = Type.GetType("System.Decimal");

lineTable.Columns.Add(dc);

dc.ColumnName = "Ext. Price";

foreach (DataRow dr in lineTable.Rows) //Form new column with ext. price

{

extPrice = Convert.ToDecimal(dr[3]) * Convert.ToDecimal(dr[7]);

dr[11] = extPrice;

}

grdLine.SetDataBinding(lineTable, ""); //Bind DataTable to DataGrid

}

} //end btnGet_Click()



Robert Schuldenfrei ([email protected])