VB.NET Only Want To Insert Dyanmically Coloumns Between DataGridView Select Query

See more:

VB

Hello Experts,

I developed a program for displaying stock in DataGridView, I have Two Tables First Table Has some columns:-

Product_code, Product_name, Product_Price, Product_Quantity, Product_Vat.

and the Second Table has:-

Product_code, Product_name, Danger_Lavel.

Now, I want The DataGridView Rows Displaying Like Below Headers:-

P_Code    | Item_Name    | Rate  | Qty.   | Vat    |

above Columns from first table, now I calculate or show amount of first row (Rate * Qty.) in next columns and add to DataGridView.

After That I want To Display Second Table Data In DataGridView

that mean:- When Form load event I want To:-
Datagridview Load First Table Data and Then After That I Add Some New Columns To DataGridView
and after that I want to Display Second Table Data.

My Query is Here To Display Data...... and Full Code.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        lblGrand.Text = ""
        DGridStoc.Columns.Clear()
        Dim commM55 As New OleDbCommand
        Dim datadM55 As New OleDbDataAdapter
        Dim Mdt55 As New DataTable
        Dim lev, amt, netamt As New DataGridViewTextBoxColumn
        If co.State = ConnectionState.Closed Then
            co.ConnectionString = "Provider = Microsoft.Jet.Oledb.4.0; Data Source = D:\InventSys\InveoSystem.mdb;" & "Jet OLEDB:Database Password=abstractinnadmin"
            co.Open()
        End If
        commM55.CommandText = "Select s.PCode, s.ProductName, s.PQuantity, s.PRate, s.VatPer, c.LevelValue from TblSTOCKING s, TblCodeList c Where s.PCode = c.PCode"
        commM55.CommandType = CommandType.Text
        datadM55.SelectCommand = commM55
        datadM55.SelectCommand.Connection = co
        datadM55.Fill(Mdt55)
        DGridStoc.DataSource = Mdt55
        DGridStoc.DataBindings.Clear()
        DGridStoc.Columns(0).HeaderText = "Category"
        DGridStoc.Columns(0).Width = 140
        DGridStoc.Columns(1).HeaderText = "Product Name"
        DGridStoc.Columns(1).Width = 350
        DGridStoc.Columns(2).HeaderText = "QTY."
        DGridStoc.Columns(2).Width = 100
        DGridStoc.Columns(3).HeaderText = "Price"
        DGridStoc.Columns(3).Width = 100
        DGridStoc.Columns(4).HeaderText = "VAT %"
        DGridStoc.Columns(4).Width = 90
        DGridStoc.Columns(5).HeaderText = "DLavel"
        DGridStoc.Columns(5).Width = 100
        '------------------------------------------------
        'Columns Add In DGridStoc
        DGridStoc.Columns.Add(amt)              'Coloumn 6
        DGridStoc.Columns.Add(netamt)           'Coloumn 7
        DGridStoc.Columns.Add(lev)              'Coloumn 8
        amt.HeaderText = "Amount"
        amt.Name = "Amount1"
        amt.Width = 80
        netamt.HeaderText = "Total"
        netamt.Name = "NetAmt1"
        netamt.Width = 100
        lev.HeaderText = "Level"
        lev.Name = "Level1"
        lev.Width = 60
        Dim cl, q As Long
        Dim vatper, cal As Double
        vatper = 0.0
        cal = 0.0
        For cl = 0 To Mdt55.Rows.Count - 1
            q = Val(DGridStoc.Rows(cl).Cells(2).Value)
            vatper = Val(DGridStoc.Rows(cl).Cells(4).Value)
            DGridStoc.Rows(cl).Cells(6).Value = DGridStoc.Rows(cl).Cells(2).Value * DGridStoc.Rows(cl).Cells(3).Value
            cal = Val(vatper) * Val(DGridStoc.Rows(cl).Cells(6).Value) / 100
            cal = Val(cal) + Val(DGridStoc.Rows(cl).Cells(6).Value)
            DGridStoc.Rows(cl).Cells(7).Value = cal.ToString
            If q < 5 Then
                DGridStoc.Rows(cl).DefaultCellStyle.Font = New Font("Arial", 9, FontStyle.Bold)
                DGridStoc.Rows(cl).DefaultCellStyle.BackColor = Color.LightYellow
                DGridStoc.Rows(cl).DefaultCellStyle.ForeColor = Color.Red
                DGridStoc.Rows(cl).Cells(8).Value = "Danger".ToString
            Else
                DGridStoc.Rows(cl).Cells(8).Value = "OK".ToString
            End If
        Next
        Try
            Dim grd, grd1 As Double
            Dim cpltr As Long
            For cpltr = 0 To DGridStoc.RowCount - 1
                grd = grd + Val(DGridStoc.Rows(cpltr).Cells(7).Value)
            Next
            grd1 = grd + Val(lblGrand.Text)
            lblGrand.Text = grd1.ToString
        Catch ex As Exception
            MsgBox("Stock Not Found!", MsgBoxStyle.Information, "No Stock")
        End Try
'Right Align Numeric Columns
        DGridStoc.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        DGridStoc.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        DGridStoc.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        DGridStoc.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        DGridStoc.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        co.Close()
    End Sub
End Class  

This Code is Perfect But I Want To Display TblSTOCKING Only Starting Four Columns i.e.
PCode, ProductName, PQuantity, PRate from First Table and Then Add My own Dynamically Columns and then TblCodeList Table One Columns After Dynamically Columns Add......

Thanks In Advanced....................