Bound datatable + unbound column in datagrid

[Gary Frank]

Is is possible to create an ASP.NET datagrid that mixes bound data
pulled from a database table with an unbound column created by the
program?

I would like to display several columns from an SQL Server database
table (table 1) in an ASP.NET datagrid control. I would also like to
display an additional column in the datagrid. The value for the
additional column would be generated by plugging values from a couple
of columns in the row in table 1 into an SQL SELECT query that selects
rows from a different table (table 2), and then performs a calculation
that produces the value for the additional column. (The value for the
additional column in each row is based on values in other columns in
that row that came from table 1.) I want the user to be able to use
the edit, update and delete buttons in the datagrid to process the
columns that came from table 1, but not table 2.

Is this possible? Can you add an unbound column to a table that is
bound to a database table? And if you do so, can you use the edit,
update, delete buttons in the datagrid to update the datasource (the
database table) for the columns that came from table 1?

What would be the best way to go about this? Would it be best to
generate a dataset from a data adapter, then add a column to the
datatable in the dataset? I think you have to do this programatically.
Would you then bind the dataset/datatable to the datagrid?

Or can you bind a datatable to a datagrid, then add an additional
column to the datagrid? If you did this, could you do a SELECT on
table 2 in order to create the value for the additional column?

 

[Elton Wang]

Hi Gary,

Normally you can use JOIN query get records from multi-tables.



If the logic to get the additional column is very complicated, you can query
table1 and have an empty 'reserved' column first, like follows:



Select column_1, column_2, ., '' As additional_column_name From table1 Where
Condition



After fill the query result to a DataTable, you can either



Loop thru rows in the DataTable to perform calculation for the column, then
bind the datatable to datagrid



Or bind the datatable to datagrid, then to perform calculation for the
column in datagrid_ItemDataBound event.



HTH

 

[Gary Frank]

Elton,

Thank you Elton! I didn't realize you could do an SQL query that
returns an empty column. It looks like a very clean solution. I'm
wondering how this will work if I add the edit, update and delete
buttons to the Datagrid. Will the empty column cause an error when
these datagrid buttons attempt to update the datasource table?

Gary Frank

 

[Elton Wang]

Hi Gary,

I don't think there are any troubles in Update/Delete logic, (Edit only
works on web controls, no direct connection with DB). You can easily control
to select data from proper columns for updating/deleting data in DB.



HTH