How can you add a column in DataGridView using calculations with database values?

Question

My friend and I are working on a final project. He has a problem and does not have enough reputation to post a picture, so I am helping him to ask you for help.

He has a database named db_TMS (using Microsoft Access 2007) that contains a table named tbl_order with the following columns:

Order_ID, Customer_Name, Dress_Type, Dress_Price, Quantity, Date_Of_Pickup, Payment_Status

In the form, he has created a datagridview named dgvReportShow

So far he is able to display the required data using the code below:

Private Sub dgvReportShow()

        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\annonymous\Documents\Visual Studio 2012\Projects\TMS Final\TMS Final\db\db_TMS.accdb"

        If Not con.State = ConnectionState.Open Then
            con.Open()
        End If

        Dim ds As New DataSet
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        Dim da As New OleDb.OleDbDataAdapter

        da = New OleDb.OleDbDataAdapter("SELECT Order_ID, Customer_Name, Dress_Type, Dress_Price, Quantity,     Date_Of_Pickup, Payment_Status " & _
                                        "FROM tbl_order " & _
                                        "WHERE (Payment_Status = 'paid')", con)

        da.Fill(dt)     
        dgvReport.DataSource = dt.DefaultView     
        dgvReport.SelectionMode = DataGridViewSelectionMode.FullRowSelect     
        con.Close()

    End Sub

Data Displayed:

Now he wants to know if he can add a column named Total for DressPrice * Quantity using a code instead of manually adding in the table, and then add a VARIABLE named Sum to display the Total value.

Something like this:

Any tips? If you may, show us the way...

bjnr · Accepted Answer · 2013-12-15 12:01:43Z

1

For "Total", add a new column in your SELECT statement:

SELECT Order_ID, Customer_Name, Dress_Type, Dress_Price, Quantity, Date_Of_Pickup, Payment_Status, Dress_Price* Quantity as Total"

For "Total Sales", you have to iterate over all grid rows and sum the value in Total column.

answered Dec 15, 2013 at 12:01

bjnr

3,4371 gold badge24 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

How can you add a column in DataGridView using calculations with database values?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related