Add columns (DataColumn) dynamically (programmatically) to DataTable using VB.NET

Tutorials PanelPosted by

Below is a sample code for how to add a column to a DataTable using vb.NET. Column will be added dynamically and will contain the yearly salary of each employee in an employee’s table.

First, let’s create the tables in SQL Server as shown below.

DesignView

Employee definition

SQL

CREATE TABLE [dbo].[Employee] (
    [Id]         INT          NOT NULL,
    [Name]       VARCHAR (50) NULL,
    [Salary]     MONEY        NULL,
    [Occupation] VARCHAR (50) NULL,
    [Department] VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Now add few records to that table.

DataView

employees-Data

Now create a new vb.NET solution, then add a DataGridView to the main form as shown below.

DataGrid-Employee-Salary

Then create a sub to populate the DataGridView.

VB.NET

Sub BindGrid()
       Try

           Dim connetionString = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;"
           Dim conn As System.Data.SqlClient.SqlConnection = New SqlClient.SqlConnection(connetionString)
       Dim da As New SqlDataAdapter
       Dim cmd As New SqlCommand
       Dim dt As New DataTable
       cmd.CommandText = "Select * from Employee Order by Name ASC"
       da.SelectCommand = cmd
       da.SelectCommand.Connection = conn
           da.Fill(dt)
           ' Add the column Yearly salary to the DataTable, which is the salary * 12 month
           dt.Columns.Add("Yearly Salary", GetType(String), "Salary * 12")
           dgv.DataSource = dt
           conn.Close()

       Catch ex As Exception
           MessageBox.Show(ex.Message)
       End Try
   End Sub

Call the sub from the Form Load Event

Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  BindGrid()
End Sub

Run the application.

Employee-Data-Grid-Salary

Notice the column “Yearly Salary” has been added to the DataGridView at the run time.

2 comments

Leave a Reply

Your email address will not be published. Required fields are marked *