Insert (Save) Checked GridView row with form data in database using C# and VB.Net in ASP.Net

Hi akhter,

Check this sample. now 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

HTML

<table>
    <tr>
        <td>Pack No</td>
        <td><asp:TextBox ID="txtPackNo" runat="server" /></td>
    </tr>
    <tr>
        <td>Transfer Order Ref</td>
        <td><asp:TextBox ID="txtTransferNo" runat="server" /></td>
    </tr>
</table>
<asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false" AllowPaging="true"
    PageSize="5" OnPageIndexChanging="gvOrders_PageIndexChanging">
    <Columns>
        <asp:TemplateField>
            <HeaderTemplate>
                <asp:CheckBox ID="checkAll" runat="server" onclick="checkAll(this);true" />
            </HeaderTemplate>
            <ItemTemplate>
                <asp:CheckBox ID="chkRows" runat="server" onclick="Check_Click(this)" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="OrderID" HeaderText="Order ID" />
        <asp:BoundField DataField="CustomerID" HeaderText="Customer Name" />
        <asp:BoundField DataField="Freight" HeaderText="Price" />
    </Columns>
</asp:GridView>
<asp:Button ID="btnTransfer" Text="Transfer" runat="server" OnClick="OnTranasfer" />

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#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindOrders();
    }
}

protected void gvOrders_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    this.gvOrders.PageIndex = e.NewPageIndex;
    this.BindOrders();
}

private void BindOrders()
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT OrderID,CustomerID,Freight FROM Orders", con))
        {
            cmd.CommandType = CommandType.Text;
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = cmd;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                this.gvOrders.DataSource = dt;
                this.gvOrders.DataBind();
            }
        }
    }
}

protected void OnTranasfer(object sender, EventArgs e)
{
    string packNo = txtPackNo.Text.Trim();
    string transferNo = txtTransferNo.Text.Trim();
    foreach (GridViewRow row in gvOrders.Rows)
    {
        if ((row.FindControl("chkRows") as CheckBox).Checked)
        {
            string orderID = row.Cells[1].Text;
            string CustomerID = row.Cells[2].Text;
            string Price = row.Cells[3].Text;
            this.Insert(orderID, CustomerID, Price, packNo, transferNo);
        }
    }

}

private void Insert(string orderID, string CustomerID, string Price, string packNo, string transferNo)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr1"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO sbTransfer VALUES(@OrderID,@CustomerID,@Price,@PackNo,@TransferNo)", con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@OrderID", orderID);
            cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
            cmd.Parameters.AddWithValue("@Price", Price);
            cmd.Parameters.AddWithValue("@PackNo", packNo);
            cmd.Parameters.AddWithValue("@TransferNo", transferNo);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindOrders()
    End If
End Sub

Protected Sub gvOrders_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    Me.gvOrders.PageIndex = e.NewPageIndex
    Me.BindOrders()
End Sub

Private Sub BindOrders()
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("SELECT OrderID,CustomerID,Freight FROM Orders", con)
            cmd.CommandType = CommandType.Text
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                sda.SelectCommand = cmd
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                Me.gvOrders.DataSource = dt
                Me.gvOrders.DataBind()
            End Using
        End Using
    End Using
End Sub

Protected Sub OnTranasfer(ByVal sender As Object, ByVal e As EventArgs)
    Dim packNo As String = txtPackNo.Text.Trim()
    Dim transferNo As String = txtTransferNo.Text.Trim()
    For Each row As GridViewRow In gvOrders.Rows
        If (TryCast(row.FindControl("chkRows"), CheckBox)).Checked Then
            Dim orderID As String = row.Cells(1).Text
            Dim CustomerID As String = row.Cells(2).Text
            Dim Price As String = row.Cells(3).Text
            Me.Insert(orderID, CustomerID, Price, packNo, transferNo)
        End If
    Next
End Sub

Private Sub Insert(ByVal orderID As String, ByVal CustomerID As String, ByVal Price As String, ByVal packNo As String, ByVal transferNo As String)
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr1").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("INSERT INTO sbTransfer VALUES(@OrderID,@CustomerID,@Price,@PackNo,@TransferNo)", con)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@OrderID", orderID)
            cmd.Parameters.AddWithValue("@CustomerID", CustomerID)
            cmd.Parameters.AddWithValue("@Price", Price)
            cmd.Parameters.AddWithValue("@PackNo", packNo)
            cmd.Parameters.AddWithValue("@TransferNo", transferNo)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub

Screenshot

Inserted records