database and asp.net
My goal is to connect to my database either manually or using an sqladapater, and get information from two of my databases on sql server 2005. Then I want to take this information and on run-time begin to add/subtract/divide/multiply certain columns and place the information into other columns. I can do this in queries, however, I want to do it on run-time what is the best way to achieve this. I had some of this working, but I just want to start fresh and see how you would go about doing this.
Partial Class _Default Inherits System.Web.UI.Page Public cmd As New SqlCommand() Public da As New SqlDataAdapter(cmd) conn string here..... Dim ds As New DataSet Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load cmd.Connection = conn conn.Open() ' Dim ds As New DataSet Dim x As New DataTable cmd.CommandText = "SELECT B603SalesAsOFMASTER.DIVISION, B603SalesAsOFMASTER.SDESCR, B603SalesAsOFMASTER.DYYYY, B603SalesAsOFMASTER.AsOFSales, B603SalesAsOFMASTER.ASOFPAX, B603SalesAsOFMASTER.YESales, B603SalesAsOFMASTER.YEPAX, B603SalesAsOFMASTER.PCTofSales, B603SalesAsOFMASTER.PCTofPAX, B601SalesAsOF.Sales AS [Current Sales], B601SalesAsOF.PAX AS [Current PAX], B601SalesAsOF.Sales / B601SalesAsOF.PAX AS [AVG PAX], B601SalesAsOF.Sales / B603SalesAsOFMASTER.PCTofSales AS [Projected Year End] FROM B603SalesAsOFMASTER INNER JOIN B601SalesAsOF ON B603SalesAsOFMASTER.SDESCR = B601SalesAsOF.SDESCR WHERE (B603SalesAsOFMASTER.DYYYY = '2008') AND (B601SalesAsOF.DYYYY = '2010')" 'da.Fill(ds) da.Fill(x) GridView1.DataSource = x GridView1.DataBind() da.FillSchema(ds, SchemaType.Mapped) conn.Close() End Sub End Class
That is what i am playing around with right now, some of my calculations are done directly in the sql command, but i want to change this.
It sounds like you're asking how to move a calculated field out of a SQL query and into code between the query and the datagrid binding. (If I'm incorrect in that interpretation, let me know.) If that's the case, this short tutorial should have exactly what you're looking for.
On a side note, those public data access members on your class make me uneasy. Those might be better as private. Or, better still, encapsulated within the single method that uses them. Throw in some try/catch/finally for good measure and resource cleanup of course :)