Hi PSowmiya,
Check this example. Now please take its reference and correct your code.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
Namespaces
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
VB.Net
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Code
C#
DataTable prdatatable;
private void Form1_Load(object sender, EventArgs e)
{
prdatatable = GetData();
dataGridView1.AutoGenerateColumns = false;
dataGridView1.ColumnCount = 8;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
dataGridView1.DataSource = prdatatable;
dataGridView1.AutoSize = false;
dataGridView1.ScrollBars = ScrollBars.Both;
dataGridView1.Columns[0].Name = "Id";
dataGridView1.Columns[0].HeaderText = "Id";
dataGridView1.Columns[0].DataPropertyName = "EmployeeID";
dataGridView1.Columns[0].ReadOnly = true;
dataGridView1.Columns[1].Name = "Name";
dataGridView1.Columns[1].HeaderText = "Name";
dataGridView1.Columns[1].DataPropertyName = "FirstName";
dataGridView1.Columns[1].ReadOnly = true;
dataGridView1.Columns[2].Name = "City";
dataGridView1.Columns[2].HeaderText = "City";
dataGridView1.Columns[2].DataPropertyName = "City";
dataGridView1.Columns[2].ReadOnly = true;
dataGridView1.Columns[3].Name = "Country";
dataGridView1.Columns[3].HeaderText = "Country";
dataGridView1.Columns[3].DataPropertyName = "Country";
dataGridView1.Columns[3].ReadOnly = true;
dataGridView1.Columns[4].Name = "Address";
dataGridView1.Columns[4].HeaderText = "Address";
dataGridView1.Columns[4].DataPropertyName = "Address";
dataGridView1.Columns[4].ReadOnly = true;
DataGridViewComboBoxColumn returnstatus = new DataGridViewComboBoxColumn();
returnstatus.Name = "ReturnStatus";
returnstatus.Items.Add("Yes");
returnstatus.Items.Add("No");
dataGridView1.Columns.Insert(5, returnstatus);
dataGridView1.Columns[6].Name = "PostalCode";
dataGridView1.Columns[6].HeaderText = "PostalCode";
dataGridView1.Columns[6].DataPropertyName = "PostalCode";
dataGridView1.Columns[6].ReadOnly = true;
dataGridView1.Columns[7].Name = "Phone";
dataGridView1.Columns[7].HeaderText = "Phone";
dataGridView1.Columns[7].DataPropertyName = "HomePhone";
dataGridView1.Columns[7].ReadOnly = true;
}
private DataTable GetData()
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT TOP 5 * FROM Employees";
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
DataRow addrow;
if (e.ColumnIndex == 1)
{
int noOfRows = (int)dataGridView1.CurrentRow.Cells[0].Value;
var currentRow = dataGridView1.CurrentRow.Index;
currentRow = currentRow + 1;
for (var i = 0; i <= currentRow - 1; i++)
{
addrow = prdatatable.NewRow();
addrow.ItemArray[0] = prdatatable.Rows[currentRow - 1].ItemArray[0];
addrow.ItemArray[1] = prdatatable.Rows[currentRow - 1].ItemArray[1];
addrow.ItemArray[2] = prdatatable.Rows[currentRow - 1].ItemArray[2];
addrow.ItemArray[3] = prdatatable.Rows[currentRow - 1].ItemArray[3];
addrow.ItemArray[4] = prdatatable.Rows[currentRow - 1].ItemArray[4];
addrow.ItemArray[5] = prdatatable.Rows[currentRow - 1].ItemArray[5];
addrow.ItemArray[6] = prdatatable.Rows[currentRow - 1].ItemArray[6];
addrow.ItemArray[7] = prdatatable.Rows[currentRow - 1].ItemArray[7];
addrow.ItemArray[8] = prdatatable.Rows[currentRow - 1].ItemArray[8];
addrow.ItemArray[9] = prdatatable.Rows[currentRow - 1].ItemArray[9];
addrow.ItemArray[10] = prdatatable.Rows[currentRow - 1].ItemArray[10];
//addrow.ItemArray[11] = prdatatable.Rows[currentRow - 1].ItemArray[11];
prdatatable.Rows.InsertAt(addrow, currentRow);
prdatatable.AcceptChanges();
currentRow = currentRow + 1;
}
}
}
VB.Net
Private prdatatable As DataTable
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
prdatatable = GetData()
dataGridView1.AutoGenerateColumns = False
dataGridView1.ColumnCount = 8
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
dataGridView1.DataSource = prdatatable
dataGridView1.AutoSize = False
dataGridView1.ScrollBars = ScrollBars.Both
dataGridView1.Columns(0).Name = "Id"
dataGridView1.Columns(0).HeaderText = "Id"
dataGridView1.Columns(0).DataPropertyName = "EmployeeID"
dataGridView1.Columns(0).ReadOnly = True
dataGridView1.Columns(1).Name = "Name"
dataGridView1.Columns(1).HeaderText = "Name"
dataGridView1.Columns(1).DataPropertyName = "FirstName"
dataGridView1.Columns(1).ReadOnly = True
dataGridView1.Columns(2).Name = "City"
dataGridView1.Columns(2).HeaderText = "City"
dataGridView1.Columns(2).DataPropertyName = "City"
dataGridView1.Columns(2).ReadOnly = True
dataGridView1.Columns(3).Name = "Country"
dataGridView1.Columns(3).HeaderText = "Country"
dataGridView1.Columns(3).DataPropertyName = "Country"
dataGridView1.Columns(3).ReadOnly = True
dataGridView1.Columns(4).Name = "Address"
dataGridView1.Columns(4).HeaderText = "Address"
dataGridView1.Columns(4).DataPropertyName = "Address"
dataGridView1.Columns(4).ReadOnly = True
Dim returnstatus As DataGridViewComboBoxColumn = New DataGridViewComboBoxColumn()
returnstatus.Name = "ReturnStatus"
returnstatus.Items.Add("Yes")
returnstatus.Items.Add("No")
dataGridView1.Columns.Insert(5, returnstatus)
dataGridView1.Columns(6).Name = "PostalCode"
dataGridView1.Columns(6).HeaderText = "PostalCode"
dataGridView1.Columns(6).DataPropertyName = "PostalCode"
dataGridView1.Columns(6).ReadOnly = True
dataGridView1.Columns(7).Name = "Phone"
dataGridView1.Columns(7).HeaderText = "Phone"
dataGridView1.Columns(7).DataPropertyName = "HomePhone"
dataGridView1.Columns(7).ReadOnly = True
End Sub
Private Function GetData() As DataTable
Dim conString As String = "Server=.;DataBase=Northwind;UID=sa;PWD=pass@123"
Dim query As String = "SELECT TOP 5 * FROM Employees"
Dim cmd As SqlCommand = New SqlCommand(query)
Using con As SqlConnection = New SqlConnection(conString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As DataTable = New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
Private Sub dataGridView1_CellClick(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles dataGridView1.CellClick
Dim addrow As DataRow
If e.ColumnIndex = 1 Then
Dim noOfRows As Integer = dataGridView1.CurrentRow.Cells(0).Value
Dim currentRow = dataGridView1.CurrentRow.Index
currentRow = currentRow + 1
For i = 0 To currentRow - 1
addrow = prdatatable.NewRow()
addrow.Item(0) = prdatatable.Rows(currentRow - 1).Item(0)
addrow.Item(1) = prdatatable.Rows(currentRow - 1).Item(1)
addrow.Item(2) = prdatatable.Rows(currentRow - 1).Item(2)
addrow.Item(3) = prdatatable.Rows(currentRow - 1).Item(3)
addrow.Item(4) = prdatatable.Rows(currentRow - 1).Item(4)
addrow.Item(5) = prdatatable.Rows(currentRow - 1).Item(5)
addrow.Item(6) = prdatatable.Rows(currentRow - 1).Item(6)
addrow.Item(7) = prdatatable.Rows(currentRow - 1).Item(7)
addrow.Item(8) = prdatatable.Rows(currentRow - 1).Item(8)
addrow.Item(9) = prdatatable.Rows(currentRow - 1).Item(9)
addrow.Item(10) = prdatatable.Rows(currentRow - 1).Item(10)
prdatatable.Rows.InsertAt(addrow, currentRow)
prdatatable.AcceptChanges()
currentRow = currentRow + 1
Next
End If
End Sub
Screenshot
