Add (Insert) record in ASP.Net Repeater using DataTable and insert in database on Button Click using C# and VB.Net

Hi akhter,

Check this example. Now please take its reference and correct your code.

Database

I have made use of the following table Customers with the schema as follows.

Download SQL file

HTML

<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
    <tr>
        <td style="padding-bottom: 10px">Name:<br />
            <asp:TextBox ID="txtName" runat="server" />
        </td>
    </tr>
    <tr>
        <td style="padding-bottom: 10px">Country:<br />
            <asp:TextBox ID="txtCountry" runat="server" />
        </td>
    </tr>
    <tr>
        <td style="width: 100px">
            <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="OnAdd" />
        </td>
    </tr>
</table>
<hr />
<asp:Repeater ID="rptCustomers" runat="server">
    <HeaderTemplate>
        <table>
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Country</th>
                </tr>
            </thead>
            <tbody>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td>
                <asp:Label ID="lblName" Text=' <%#Eval("Name") %>' runat="server" /></td>
            <td>
                <asp:Label ID="lblCountry" Text=' <%#Eval("Country") %>' runat="server" /></td>
        </tr>
    </ItemTemplate>
    <FooterTemplate>
        </tbody>
        </table>
    </FooterTemplate>
</asp:Repeater>
<br />
<asp:Button ID="btnSave" runat="server" Text="Save" 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.AddRange(new DataColumn[2]
        {
            new DataColumn("Name"),
            new DataColumn("Country")
        });
        ViewState["Customers"] = dt;
        this.BindCustomers();
    }
}

protected void OnAdd(object sender, EventArgs e)
{
    DataTable dt = (DataTable)ViewState["Customers"];
    dt.Rows.Add(txtName.Text.Trim(), txtCountry.Text.Trim());
    ViewState["Customers"] = dt;
    this.BindCustomers();
    txtName.Text = string.Empty;
    txtCountry.Text = string.Empty;
}

protected void OnSave(object sender, EventArgs e)
{
    foreach (RepeaterItem item in rptCustomers.Items)
    {
        string name = (item.FindControl("lblName") as Label).Text.Trim();
        string country = (item.FindControl("lblCountry") as Label).Text.Trim();
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string query = "INSERT INTO Customers VALUES (@Name,@Country)";
        using (SqlConnection con = new SqlConnection(conString))
        {
            SqlCommand cmd = new SqlCommand(query);
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Country", country);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

protected void BindCustomers()
{
    rptCustomers.DataSource = (DataTable)ViewState["Customers"];
    rptCustomers.DataBind();
}

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.AddRange(New DataColumn(1) {
                            New DataColumn("Name"),
                            New DataColumn("Country")})
        ViewState("Customers") = dt
        Me.BindCustomers()
    End If
End Sub

Protected Sub OnAdd(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = CType(ViewState("Customers"), DataTable)
    dt.Rows.Add(txtName.Text.Trim(), txtCountry.Text.Trim())
    ViewState("Customers") = dt
    Me.BindCustomers()
    txtName.Text = String.Empty
    txtCountry.Text = String.Empty
End Sub

Protected Sub OnSave(ByVal sender As Object, ByVal e As EventArgs)
    For Each item As RepeaterItem In rptCustomers.Items
        Dim name As String = (TryCast(item.FindControl("lblName"), Label)).Text.Trim()
        Dim country As String = (TryCast(item.FindControl("lblCountry"), Label)).Text.Trim()
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim query As String = "INSERT INTO Customers VALUES (@Name,@Country)"
        Using con As SqlConnection = New SqlConnection(conString)
            Dim cmd As SqlCommand = New SqlCommand(query)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                cmd.Parameters.AddWithValue("@Name", name)
                cmd.Parameters.AddWithValue("@Country", country)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    Next
End Sub

Protected Sub BindCustomers()
    rptCustomers.DataSource = CType(ViewState("Customers"), DataTable)
    rptCustomers.DataBind()
End Sub

Screenshot

Record after insert