Save (Insert) data from Gridview (Table) to database using C# and VB.Net in ASP.Net

Hi MrPattison,

Check this sample. now take its reference.

HTML

<asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:TemplateField HeaderText="Country">
            <ItemTemplate>
                <asp:Label ID="lblCountry" Text='<%# Eval("Country") %>' runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Office Name">
            <ItemTemplate>
                <asp:TextBox ID="txtOfficeName" Text='<%# Eval("OfficeName") %>' runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Manager">
            <ItemTemplate>
                <asp:TextBox ID="txtManager" Text='<%# Eval("Manager") %>' runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Contact Number">
            <ItemTemplate>
                <asp:TextBox ID="txtNumber" Text='<%# Eval("ContactNumber") %>' runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView><br />
<asp:Button ID="btnSave" Text="Save" runat="server" OnClick="OnSave" />

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)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Country");
        dt.Columns.Add("OfficeName");
        dt.Columns.Add("Manager");
        dt.Columns.Add("ContactNumber");
        dt.Rows.Add("Uk", "", "", "");
        dt.Rows.Add("India", "", "", "");
        dt.Rows.Add("USA", "", "", "");
        dt.Rows.Add("Russia", "", "", "");
        dt.Rows.Add("France", "", "", "");
        dt.Rows.Add("Germany", "", "", "");
        this.gvEmployees.DataSource = dt;
        this.gvEmployees.DataBind();
    }
}

protected void OnSave(object sender, EventArgs e)
{
    foreach (GridViewRow row in gvEmployees.Rows)
    {
        string country = (row.FindControl("lblCountry") as Label).Text;
        string officeName = (row.FindControl("txtOfficeName") as TextBox).Text;
        string manager = (row.FindControl("txtManager") as TextBox).Text;
        string contactNumber = (row.FindControl("txtNumber") as TextBox).Text;
        this.Insert(country, officeName, manager, contactNumber);
    }
    ClientScript.RegisterStartupScript(this.GetType(), "", "alert('Record Inserted Successfullty.')", true);
}

private void Insert(string country, string officeName, string manager, string contactNumber)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO tblEmployee VALUES(@Country,@OfficeName,@Manager,@Number)", con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Country", country);
            cmd.Parameters.AddWithValue("@OfficeName", officeName);
            cmd.Parameters.AddWithValue("@Manager", manager);
            cmd.Parameters.AddWithValue("@Number", contactNumber);
            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
        Dim dt As DataTable = New DataTable()
        dt.Columns.Add("Country")
        dt.Columns.Add("OfficeName")
        dt.Columns.Add("Manager")
        dt.Columns.Add("ContactNumber")
        dt.Rows.Add("Uk", "", "", "")
        dt.Rows.Add("India", "", "", "")
        dt.Rows.Add("USA", "", "", "")
        dt.Rows.Add("Russia", "", "", "")
        dt.Rows.Add("France", "", "", "")
        dt.Rows.Add("Germany", "", "", "")
        Me.gvEmployees.DataSource = dt
        Me.gvEmployees.DataBind()
    End If
End Sub

Protected Sub OnSave(ByVal sender As Object, ByVal e As EventArgs)
    For Each row As GridViewRow In gvEmployees.Rows
        Dim country As String = (TryCast(row.FindControl("lblCountry"), Label)).Text
        Dim officeName As String = (TryCast(row.FindControl("txtOfficeName"), TextBox)).Text
        Dim manager As String = (TryCast(row.FindControl("txtManager"), TextBox)).Text
        Dim contactNumber As String = (TryCast(row.FindControl("txtNumber"), TextBox)).Text
        Me.Insert(country, officeName, manager, contactNumber)
    Next
    ClientScript.RegisterStartupScript(Me.[GetType](), "", "alert('Record Inserted Successfullty.')", True)
End Sub

Private Sub Insert(ByVal country As String, ByVal officeName As String, ByVal manager As String, ByVal contactNumber As String)
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("INSERT INTO tblEmployee VALUES(@Country,@OfficeName,@Manager,@Number)", con)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@Country", country)
            cmd.Parameters.AddWithValue("@OfficeName", officeName)
            cmd.Parameters.AddWithValue("@Manager", manager)
            cmd.Parameters.AddWithValue("@Number", contactNumber)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub