database and asp.net

Question

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.

This is extremely vague. Basically you're asking "How do I write a program that uses data?" Can you be more specific? Show us what you've done so far? If there's a specific problem, give us just enough code to reproduce the problem? — David
– David, Commented Nov 30, 2010 at 15:15
The code helps, but it's still kind of vague. Especially when you use the word "columns" which could refer to your database or to your grid control. Based on the SQL query, am I to assume that what you're really asking is: "I have a DataGrid which binds to a SQL query, and the query includes calculated fields. How can I move those field calculations out of the query and into code and still bind the DataGrid to the final result?" — David
– David, Commented Nov 30, 2010 at 15:25
I was hoping after binding the data to a dataset or datatable, i could do calculations, then rebind to another dataset and then bind the datagrid to that. my question is vague, mainly because i am that clueless on how to approach this. i appreciate your help — MyHeadHurts
– MyHeadHurts, Commented Nov 30, 2010 at 15:29

David · Accepted Answer · 2010-11-30 15:29:03Z

2

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 :)

answered Nov 30, 2010 at 15:29

David

221k42 gold badges245 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MyHeadHurts Over a year ago

one last thing, if i want to put the calculated column between two columns how could i do this ? thanks

David Over a year ago

@NEWprogrammer: The ordering of the columns on the page shouldn't matter. The asp:BoundColumn elements can be in any order, as long as they have the correct DataField property on them they'll bind correctly.

MyHeadHurts Over a year ago

hmmm on my asp side there are no column names or anything. I want to use the method in the tutorial and place the column i created in .net in between two of the sql columns

JeffO · Accepted Answer · 2010-11-30 15:20:12Z

1

Create a Data Access Layer (DAL) 2 - Sqlconnections, one for each db Use a sqlreader if you are just reading data and an adapter if you need to make changes. Use either a sql string or stored procedures to retrieve your 'uncalculated' data, but make sure they are parametarized if you plan on allowing the user interface to do any filtering.

*Study up on avoiding SQL Injection

Create a Business Logic Layer This is where you can take the data from the DAL and do your calculations.

Presentation Layer Looks like this is going to be a web page(s) with a grid viewer or maybe a repeater control based on your needs. Here are some ideas to get you going: http://msdn.microsoft.com/en-us/library/bb498211.aspx

answered Nov 30, 2010 at 15:20

JeffO

8,1233 gold badges46 silver badges55 bronze badges

Collectives™ on Stack Overflow

database and asp.net

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related