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

MrPattison
 
on Mar 23, 2020 12:51 AM
738 Views

My database table structure is as follows:

Id -- Office Name -- Manager -- Contact Number

I would like to create a table with the following layout on my aspx page (web form)

enter image description here

There will be 6 rows/countries (fixed for now). When the user comes to this page they enter the Office name, Manager and contact number of all countries in one attempt (so they fill in all the yellow highlighted areas).

Once they have entered the data, they would click Save which will save the data to my database table above (so it would create multiple rows)

After some research I've tried playing with a Gridview, FormView but non seem to allow me to achieve what im after. Is there a way to achieve this with any asp net control? Would appreciate any examples to get me on track.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arunkurmi
 
on Mar 23, 2020 03:36 AM

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