Save (Insert) GridView rows in database using C# and VB.Net in ASP.Net

snickk
 
on Aug 08, 2021 11:23 PM
Sample_165929.zip
477 Views

I have 3 database table

student table consist of studentId, studentName

studentmark table consists of studentmarkId, studentId, subjectId, marksObtained (mapped of student and subject table)

Student Math Science English

StudentA Textbox Textbox Textbox

StudentB Textbox Textbox Textbox

StudentC Textbox Textbox Textbox

I want to display a form which consits of student list in column and subject list in rows so that i can fill all student marks in one page and save it at table (studentmark)

is there any reliable solution for this without using javascript (ajax)

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 09, 2021 06:03 AM

Hi snickk,

Refer below code.

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowEditing="OnRowEditing">
    <Columns>
        <asp:BoundField DataField="Student" HeaderText="Student" />
        <asp:TemplateField HeaderText="Math">
            <ItemTemplate>
                <asp:TextBox ID="txtMath" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Science">
            <ItemTemplate>
                <asp:TextBox ID="txtScience" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="English">
            <ItemTemplate>
                <asp:TextBox ID="txtEnglish" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<br />
<asp:Button 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.AddRange(new DataColumn[] { new DataColumn("Student") });
        dt.Rows.Add("Student A");
        dt.Rows.Add("Student B");
        dt.Rows.Add("Student C");
        GridView1.DataSource = ViewState["dt"] as DataTable;
        GridView1.DataBind();
    }
}

protected void OnSave(object sender, EventArgs e)
{
    foreach (GridViewRow row in GridView1.Rows)
    {
        string student = row.Cells[0].Text;
        string math = (row.FindControl("txtMath") as TextBox).Text;
        string science = (row.FindControl("txtScience") as TextBox).Text;
        string english = (row.FindControl("txtEnglish") as TextBox).Text;
        string query = "INSERT INTO StudentMark VALUES(@Name, @Math, @Science, @English)";
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.Parameters.AddWithValue("@Name", student);
                cmd.Parameters.AddWithValue("@Math", math);
                cmd.Parameters.AddWithValue("@Science", science);
                cmd.Parameters.AddWithValue("@English", english);
                cmd.Connection = con;
                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.AddRange(New DataColumn() {New DataColumn("Student")})
        dt.Rows.Add("Student A")
        dt.Rows.Add("Student B")
        dt.Rows.Add("Student C")
        GridView1.DataSource = TryCast(ViewState("dt"), DataTable)
        GridView1.DataBind()
    End If
End Sub

Protected Sub OnSave(ByVal sender As Object, ByVal e As EventArgs)
    For Each row As GridViewRow In GridView1.Rows
        Dim student As String = row.Cells(0).Text
        Dim math As String = TryCast(row.FindControl("txtMath"), TextBox).Text
        Dim science As String = TryCast(row.FindControl("txtScience"), TextBox).Text
        Dim english As String = TryCast(row.FindControl("txtEnglish"), TextBox).Text
        Dim query As String = "INSERT INTO StudentMark VALUES(@Name, @Math, @Science, @English)"
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Using cmd As SqlCommand = New SqlCommand(query)
                cmd.Parameters.AddWithValue("@Name", student)
                cmd.Parameters.AddWithValue("@Math", math)
                cmd.Parameters.AddWithValue("@Science", science)
                cmd.Parameters.AddWithValue("@English", english)
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    Next
End Sub