Insert data into database from Gridview in ASP.Net

By Mohd Rizwan Feb 04 2013
In this blog we will know how to insert data from Gridview to database.
    • Like
    • Love It
    • Awesome
    • Interesting
    • It's Okay
    • Thumbs Down
  • 40.7k
  • 0

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"Inherits="Insert_data_from_Gridview_database._Default" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""">


<html xmlns="" >

<head runat="server">

    <title>Untitled Page</title>



    <form id="form1" runat="server">


            <td valign="top" align="left">Upload File <input type="file"  id="fileName" tabindex="1"  Runat="server" onclick="return fileName_onclick()" /></td>
            <td valign="top" align="left" class="style1"><a href="Docs/UploadData1.csv" target="_blank">Download template.</a></td>
        <td><asp:button id="btnUpload"  Text="Upload Data" Runat="server" OnClick="btnUpload_Click" /></td>
                                <td valign="top" align="left" class="style1">
                                    <asp:Button ID="btnAddToDb" runat="server" Height="25px" Text="Insert To Database."
                                        Width="138px" onclick="btnAddToDb_Click" Enabled="false" />
                                    <asp:Label ID="lblMessage" runat="server" Text=""></asp:Label>
                                <td valign="top" align="left" width="1%"></td>
                                <td valign="top" align="left" class="style1">
                                    <asp:GridView ID="Gvrecords" runat="server" Height="217px" Width="340px"
                                        CellPadding="4" ForeColor="#333333" GridLines="None"
                                        <RowStyle BackColor="#EFF3FB" />
                                            <asp:TemplateField HeaderText="Id">
                                                <asp:Label ID="lblId" runat="server" Text='<% #Eval("ID") %>'></asp:Label>                                           
                                            <asp:TemplateField HeaderText="Product Name">
                                                <asp:Label ID="lblName" runat="server" Text='<% #Eval("Name") %>'></asp:Label>                                           
                                            <asp:TemplateField HeaderText="Price">
                                                <asp:Label ID="lblPrice" runat="server" Text='<% #Eval("Price") %>'></asp:Label>                                           
                                            <asp:TemplateField HeaderText="Product Description">
                                                <asp:Label ID="lblDescription" runat="server" Text='<% #Eval("Description") %>'></asp:Label>                                           
                                        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                                        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                                        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                                        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                                        <EditRowStyle BackColor="#2461BF" />
                                        <AlternatingRowStyle BackColor="White" />
                                <td valign="top" align="left" width="1%">
        <asp:Label ID="lblErrMsg" runat="server" Width="100%" Text = "" ForeColor="red"></asp:Label>





using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

namespace Insert_data_from_Gridview_database


    SqlConnection con;
    SqlCommand cmd;
    SqlTransaction tran;        
    protected void Page_Load(object sender, EventArgs e)
        con = new SqlConnection("Data Source=MANIK-PC\\SQLEXPRESS;Initial Catalog=mycity123;Integrated Security=True");
        cmd = new SqlCommand();
        cmd.Connection = con;           

    protected void btnAddToDb_Click(object sender, EventArgs e)
        tran = con.BeginTransaction();
        cmd.Transaction = tran;
        string slno=null;
                foreach (GridViewRow  g1 in Gvrecords.Rows )
                    string id = (g1.FindControl("lblId") as Label).Text;
                    string name = (g1.FindControl("lblName") as Label).Text;
                    string price = (g1.FindControl("lblPrice") as Label).Text;
                    string description = (g1.FindControl("lblDescription") as Label).Text;
                    string query = "insert into product values(" +id+ ",'" + name + "'," +price+ ",'" + description + "')";
                    //cmd.CommandText = "insert into Members values ('" + g1.Cells[0].Text + "','" + g1.Cells[1].Text + "','" + g1.Cells[2].Text + "','" + g1.Cells[3].Text + "')";
                    slno = id;
                    cmd.CommandText = query;
                lblMessage.Text = "Records inserted successfully";
        catch(Exception ex)
                lblMessage.Text = "Error Occured  near Sr. No. = " + slno + "<br />";
                lblMessage.Text += "No Data Is Inserted. <br />";
                //lblMessage.Text += ex.Message;