VB.Net Web Application how to add a column below a column on a GridView

Question

How do i add one column under another column so when i make a query output i have two output values in the same row? In my case i want to display Emp ID and under that column i want to display Cust ID so the GridView table can look like this

Emp ID, Rgst ID, Line #, Disc CD, Auth Emp ID, Orig Prc, Disc Amt, Disc Pct, GL Acc ID

Cust ID

Another Question is how can i add a column or header at the end of the "Orig Prc" output as Total and sum all of the "Orig Prc" to Total so it can output the result in the same column as "Orig Prc"? So it can look like this:

ORIG PRC

orig prc output

TOTAL

output is sum of all orig prc output

Can this be done with current method or do i have change my approach on how i output the data on the ViewGrid?

This is my current code:

Protected Sub ExecuteButton_Click(sender As Object, e As EventArgs) Handles ExecuteButton.Click
    Dim StoreID As Integer
    Dim TransID As Integer
    Dim RgstID As Integer
    Dim dt As DataTable

    If Not Integer.TryParse(StoreIDTextbox.Text, StoreID) Then
        MsgBox("Invalid input. Please enter both Store ID and Transaction ID.")
        Exit Sub
    End If

    If Not Integer.TryParse(TransactionIDTextbox.Text, TransID) Then
        MsgBox("Invalid input. Please enter both Store ID and Transaction ID.")
        Exit Sub
    End If

    Sql.AddParam("@Str_ID", StoreID)
    Sql.AddParam("@Tran_ID", TransID)

    'Rgst_ID Validation
    If RegisterIDTextbox.Text.Length = 0 Then
        SQL.AddParam("@Rgst_ID", "")
    ElseIf RegisterIDTextbox.Text.Length > 0 Then
        RgstID = Integer.Parse(RegisterIDTextbox.Text)
        Sql.AddParam("@Rgst_ID", RgstID)
    End If

    Try
        dt = SQL.ExecQuery("Select H.Emp_ID, H.Cust_ID, H.Rgst_ID, D.TRAN_LN_NUM, D.DISC_CD, D.AUTH_EMP_ID, D.ORIG_PRC, D.DISC_AMT, D.DISC_PCT, D.GL_ACCT_ID 
                             From Transaction_Header H 
                             INNER Join LN_Detail L On (H.Str_ID = L.Str_ID And H.Rgst_ID = L.Rgst_ID And H.Tran_ID = L.Tran_ID)
                             INNER Join LN_Discount D ON (L.Str_ID = D.Str_ID And L.Rgst_ID = D.Rgst_ID And L.Tran_ID = D.Tran_ID And L.Tran_LN_Num = D.Tran_LN_Num)  
                             WHERE(H.Str_ID = @Str_ID)
                                And (H.Tran_ID = @Tran_ID)
                                And ((H.Rgst_ID = @Rgst_ID) Or (@Rgst_ID Is NULL Or @Rgst_ID = ''))")

    Catch ex As Exception
        MsgBox(ex.Message)
        Exit Sub
    End Try

    GridView2.DataSource = dt
    GridView2.DataBind()

    TimeLabel.Text = DateAndTime.Now

GridView

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns ="False" CellPadding="4" ForeColor="#333333">
                        <AlternatingRowStyle BackColor="White" />
                        <Columns>
                            <asp:BoundField DataField="Emp_ID" HeaderText="Emp ID" /> 
                            <asp:BoundField DataField="Cust_ID" HeaderText="Cust ID" />
                            <asp:BoundField DataField="Rgst_ID" HeaderText="Rgst ID" />
                            <asp:BoundField DataField="TRAN_LN_NUM" HeaderText="Line #" />
                            <asp:BoundField DataField="DISC_CD" HeaderText="Disc CD" />
                            <asp:BoundField DataField="AUTH_EMP_ID" HeaderText="Auth Emp ID" />
                            <asp:BoundField DataField="ORIG_PRC" HeaderText="Orig Prc" />
                            <asp:BoundField DataField="Disc_Amt" HeaderText="Disc Amt" />
                            <asp:BoundField DataField="Disc_Pct" HeaderText="Disc Pct" />
                            <asp:BoundField DataField="GL_ACCT_ID" HeaderText="GL Acct ID" />
                        </Columns>

Answer 1

In your GridView markup use TemplateColumns instead, this will give you the flexibility you need, e.g.

             <asp:TemplateColumn>
           <HeaderTemplate>
              <b> Tax </b>
           </HeaderTemplate>
           <ItemTemplate>
              <asp:Label
                   Text='<%# DataBinder.Eval(Container.DataItem, "Tax") %>'
                   runat="server"/>
           </ItemTemplate>
           <EditItemTemplate>
              <asp:CheckBox
                   Text="Taxable" 
                   runat="server"/>
           </EditItemTemplate>
           <FooterTemplate>
              <asp:HyperLink id="HyperLink1"
                   Text="Microsoft"
                   NavigateUrl="http://www.microsoft.com"
                   runat="server"/>
           </FooterTemplate>
        </asp:TemplateColumn>

https://learn.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.templatecolumn?view=netframework-4.8