add query items in datagrid columns

I have a sql query that has gotten out of hand, see file for query.  Instead of adding the fields in the query I have them and think I should add them in the datagrid to speed up query, becuase they are sub-select fields and then to do the math I ahve to sub-select them agian which makes the query take longer.  How can I add this in my datagrid instead of here.  Teh field I would like to eliminate in query is the VAC column in query and then just add teh corresponding columns to get value in datagrid.
help.txt
kdeutschAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ITHelper80Commented:
This will show you how.

http://authors.aspalliance.com/aspxtreme/webforms/controls/addingboundfieldstogridview.aspx
http://aspnet101.com/aspnet101/tutorials.aspx?id=56

However I would personally use a DataTable first , perform all the processing and then bind to a datagrid
0
kdeutschAuthor Commented:
Hi,
I already have my fields boung, what I need to do is create a dynamic column that adds the rest of the columns together.  Here is my DG, what I need to do is take the AUTH_STR column + ACN column - ASGN_STR to get a new column called VAC

<Columns>
<asp:BoundColumn DataField="UIC" HeaderText="Uic"></asp:BoundColumn>
<asp:BoundColumn DataField="City" HeaderText="City"></asp:BoundColumn>
<asp:BoundColumn DataField="Para" HeaderText="Para"></asp:BoundColumn>
<asp:BoundColumn DataField="Line" HeaderText="Line"></asp:BoundColumn>
<asp:BoundColumn DataField="Grade" HeaderText="Grade"></asp:BoundColumn>
<asp:BoundColumn DataField="DMOS" HeaderText="DMOS"></asp:BoundColumn>
<asp:BoundColumn DataField="Gender" HeaderText="Gender"></asp:BoundColumn>
<asp:BoundColumn DataField="AUTH_STR" HeaderText="Auth_Str"></asp:BoundColumn>
<asp:BoundColumn DataField="OS" HeaderText="Over_Str"></asp:BoundColumn>
<asp:BoundColumn DataField="ASGN_STR" HeaderText="Asgn_Str"></asp:BoundColumn>
<asp:BoundColumn DataField="ACN" HeaderText="ACN_ASGN"></asp:BoundColumn>
<asp:BoundColumn DataField="Unit_Type" HeaderText="Unit Type"></asp:BoundColumn>
<asp:BoundColumn DataField="Unit_Stat" HeaderText="Unit Stat"></asp:BoundColumn>
<asp:TemplateColumn HeaderText="Request">
<ItemTemplate>
<asp:Button ID="btnRequest" runat="server" Text="Request" ForeColor="blue" Width="60" CommandName="Request"></asp:Button>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
0
ITHelper80Commented:
Are you trying to add/subtract values from the columns or combine them?

If you are trying to combine
Your going to have to fill a DataTable first and then bind to the DG

Here is an example.
dt.Columns.Add(new DataColumn("VAC",
System.Type.GetType("System.String"), "[AUTH_STR] [ACN]"));

Open in new window

0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

kdeutschAuthor Commented:
Hi, here is what i tried but I get a blue underline under New datacolumn to the end

value of type system.data.datacolumn cannot be converted to system.web.ui.webcontrols.datacolumn


SQl = "Select * from tables"

myDataTable = New DataTable
myDataTable = getData(sql)

 myDataGrid.Columns.Add(New DataColumn("VAC2", System.Type.GetType("system.string"), "[AUTH_STR] + [ACN] - [ASGN_STR]"))

myDataGrid.DataSource = myDataTable
 myDataGrid.DataBind()
0
ITHelper80Commented:
myDataGrid.Columns.Add should be myDataTable.Columns.Add
0
kdeutschAuthor Commented:
Ok changed, but i get the following error. which point tot he new line.  I tried putting it after databind and in middle and before, then i tired this. but still got the error below.

 myDataTable.Columns.Add(New DataColumn("VAC2", System.Type.GetType("system.string"), "[myDataTable.Rows(0)(7)] + [myDataTable.Rows(0)(8)] - [myDataTable.Rows(0)(9)]"))

Error
'dataType' argument cannot be null. Parameter name: dataType
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentNullException: 'dataType' argument cannot be null. Parameter name: dataType
0
ITHelper80Commented:
Are the values in the rows strings or integers?
0
kdeutschAuthor Commented:
They should be all integers, because I can add them in my sql code. but then it takes to long.
0
ITHelper80Commented:
try changing ("system.string") to ("system.integer")
0
kdeutschAuthor Commented:
Hi,
get he same error as before no matter if its.

 myDataTable = New DataTable
myDataTable = getData(sql)

myDataTable.Columns.Add(New DataColumn("VAC2", System.Type.GetType("system.integer"), "[myDataTable.Rows(0)(7)] + [myDataTable.Rows(0)(8)] - [myDataTable.Rows(0)(9)]"))

 myDataGrid.DataSource = myDataTable
myDataGrid.DataBind()

or

myDataTable = New DataTable
myDataTable = getData(sql)

myDataTable.Columns.Add(New DataColumn("VAC2", System.Type.GetType("system.integer"), "[AUTH_STR] + [ACN] - [ASGN_STR]"))

myDataGrid.DataSource = myDataTable
myDataGrid.DataBind()


0
ITHelper80Commented:
My bad....

Use this format instead

dt.Columns.Add("Name", Type.GetType("System.String"))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kdeutschAuthor Commented:
HI,
I tired this from anthoer web site but get a totally different error.

 myDataTable.Columns.Add("VAR2", myDataTable.Rows(0)(7))

Here is how they have for DataGridView instead of asp.net 1.1 datagrid
'---adding columns---
        DataGridView1.Columns.Add("ID", "Product ID")
        DataGridView1.Columns.Add("Name", "Product Name")

But the error I get is the follwoing
Specified cast is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Specified cast is not valid.

0
kdeutschAuthor Commented:
Closing out old questions, works
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.