Insert multiple TextBox values into multiple row in database using C# and VB.Net in ASP.Net

Hi lingers,

Refer below code.

HTML

<table id="tblData" width="95%" runat="server">
    <tr>
        <th>School/College Name</th>
        <th>Date of Entry</th>
        <th>Date of Leaving</th>
        <th>Name of Board/University</th>
        <th>Exam Passed</th>
        <th>Division</th>
        <th>subjects</th>
        <th>Percentage</th>
        <th>Year of Passing</th>
    </tr>
    <tr>
        <td><asp:TextBox ID="TextBox1" runat="server" Width="200px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox2" runat="server" Width="80px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox3" runat="server" Width="80px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox4" runat="server" Width="200px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox5" runat="server"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox6" runat="server" Width="85px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox7" runat="server" Width="250px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox8" runat="server" Width="85px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox9" runat="server" Width="80px"></asp:TextBox></td>
    </tr>
    <tr>
        <td><asp:TextBox ID="TextBox10" runat="server" Width="200px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox11" runat="server" Width="80px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox12" runat="server" Width="80px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox13" runat="server" Width="200px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox14" runat="server"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox15" runat="server" Width="85px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox16" runat="server" Width="250px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox17" runat="server" Width="85px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox18" runat="server" Width="80px"></asp:TextBox></td>
    </tr>
    <tr>
        <td><asp:TextBox ID="TextBox19" runat="server" Width="200px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox20" runat="server" Width="80px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox21" runat="server" Width="80px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox22" runat="server" Width="200px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox23" runat="server"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox24" runat="server" Width="85px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox25" runat="server" Width="250px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox26" runat="server" Width="85px"></asp:TextBox></td>
        <td><asp:TextBox ID="TextBox27" runat="server" Width="80px"></asp:TextBox></td>
    </tr>
</table>
<asp:Button ID="btnSave" Text="Save" OnClick="Save" runat="server" />

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

protected void Save(object sender, EventArgs e)
{
    foreach (HtmlTableRow row in tblData.Rows)
    {
        List<HtmlTableCell> cells = row.Controls.OfType<HtmlTableCell>().ToList();
        if (cells[0].Controls.OfType<TextBox>().ToList().Count > 0)
        {
            string collegeName = cells[0].Controls.OfType<TextBox>().ToList()[0].Text;
            string dateofEntry = cells[1].Controls.OfType<TextBox>().ToList()[0].Text;
            string dateofLeaving = cells[2].Controls.OfType<TextBox>().ToList()[0].Text;
            string university = cells[3].Controls.OfType<TextBox>().ToList()[0].Text;
            string examPassed = cells[4].Controls.OfType<TextBox>().ToList()[0].Text;
            string division = cells[5].Controls.OfType<TextBox>().ToList()[0].Text;
            string subjects = cells[6].Controls.OfType<TextBox>().ToList()[0].Text;
            string percentage = cells[7].Controls.OfType<TextBox>().ToList()[0].Text;
            string yearOfPassing = cells[8].Controls.OfType<TextBox>().ToList()[0].Text;
            this.SaveRecord(collegeName, dateofEntry, dateofLeaving, university, examPassed, division, subjects, percentage, yearOfPassing);
        }
    }
}

private void SaveRecord(string collegeName, string dateOfEntry, string dateOfLeaving, string university, string exampassed, string division, string subjects, string percentage, string yearOfPassing)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "INSERT INTO TableName (CollegeName,DateOfEntry,DateOfLeaving,University,Exampassed,Division,Subjects,Percentage,YearOfPassing) VALUES (@CollegeName,@DateOfEntry,@DateOfLeaving,@University,@Exampassed,@Division,@Subjects,@Percentage,@YearOfPassing)";
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@CollegeName", collegeName);
            cmd.Parameters.AddWithValue("@DateOfEntry", dateOfEntry);
            cmd.Parameters.AddWithValue("@DateOfLeaving", dateOfLeaving);
            cmd.Parameters.AddWithValue("@University", university);
            cmd.Parameters.AddWithValue("@Exampassed", exampassed);
            cmd.Parameters.AddWithValue("@Division", division);
            cmd.Parameters.AddWithValue("@Subjects", subjects);
            cmd.Parameters.AddWithValue("@Percentage", percentage);
            cmd.Parameters.AddWithValue("@YearOfPassing", yearOfPassing);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

VB.Net

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    For Each row As HtmlTableRow In tblData.Rows
        Dim cells As List(Of HtmlTableCell) = row.Controls.OfType(Of HtmlTableCell)().ToList()
        If cells(0).Controls.OfType(Of TextBox)().ToList().Count > 0 Then
            Dim collegeName As String = cells(0).Controls.OfType(Of TextBox)().ToList()(0).Text
            Dim dateofEntry As String = cells(1).Controls.OfType(Of TextBox)().ToList()(0).Text
            Dim dateofLeaving As String = cells(2).Controls.OfType(Of TextBox)().ToList()(0).Text
            Dim university As String = cells(3).Controls.OfType(Of TextBox)().ToList()(0).Text
            Dim examPassed As String = cells(4).Controls.OfType(Of TextBox)().ToList()(0).Text
            Dim division As String = cells(5).Controls.OfType(Of TextBox)().ToList()(0).Text
            Dim subjects As String = cells(6).Controls.OfType(Of TextBox)().ToList()(0).Text
            Dim percentage As String = cells(7).Controls.OfType(Of TextBox)().ToList()(0).Text
            Dim yearOfPassing As String = cells(8).Controls.OfType(Of TextBox)().ToList()(0).Text
            Me.SaveRecord(collegeName, dateofEntry, dateofLeaving, university, examPassed, division, subjects, percentage, yearOfPassing)
        End If
    Next
End Sub

Private Sub SaveRecord(ByVal collegeName As String, ByVal dateOfEntry As String, ByVal dateOfLeaving As String, ByVal university As String, ByVal exampassed As String, ByVal division As String, ByVal subjects As String, ByVal percentage As String, ByVal yearOfPassing As String)
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "INSERT INTO TableName (CollegeName,DateOfEntry,DateOfLeaving,University,Exampassed,Division,Subjects,Percentage,YearOfPassing) VALUES (@CollegeName,@DateOfEntry,@DateOfLeaving,@University,@Exampassed,@Division,@Subjects,@Percentage,@YearOfPassing)"
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            cmd.Connection = con
            cmd.CommandText = query
            cmd.Parameters.AddWithValue("@CollegeName", collegeName)
            cmd.Parameters.AddWithValue("@DateOfEntry", dateOfEntry)
            cmd.Parameters.AddWithValue("@DateOfLeaving", dateOfLeaving)
            cmd.Parameters.AddWithValue("@University", university)
            cmd.Parameters.AddWithValue("@Exampassed", exampassed)
            cmd.Parameters.AddWithValue("@Division", division)
            cmd.Parameters.AddWithValue("@Subjects", subjects)
            cmd.Parameters.AddWithValue("@Percentage", percentage)
            cmd.Parameters.AddWithValue("@YearOfPassing", yearOfPassing)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub