Scroll To Top
Blog

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.

<%@ 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""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

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

    <div> 

<table>
        <tr>            
            <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>
        </tr>        
        <tr>
        <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>
                            </tr>                            
                            <tr>
                                <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"
                                        AutoGenerateColumns="False">
                                        <RowStyle BackColor="#EFF3FB" />
                                        <Columns>
                                            <asp:TemplateField HeaderText="Id">
                                            <ItemTemplate>
                                                <asp:Label ID="lblId" runat="server" Text='<% #Eval("ID") %>'></asp:Label>                                           
                                            </ItemTemplate>
                                            </asp:TemplateField>
                                            <asp:TemplateField HeaderText="Product Name">
                                            <ItemTemplate>
                                                <asp:Label ID="lblName" runat="server" Text='<% #Eval("Name") %>'></asp:Label>                                           
                                            </ItemTemplate>
                                            </asp:TemplateField>
                                            <asp:TemplateField HeaderText="Price">
                                            <ItemTemplate>
                                                <asp:Label ID="lblPrice" runat="server" Text='<% #Eval("Price") %>'></asp:Label>                                           
                                            </ItemTemplate>
                                            </asp:TemplateField>
                                            <asp:TemplateField HeaderText="Product Description">
                                            <ItemTemplate>
                                                <asp:Label ID="lblDescription" runat="server" Text='<% #Eval("Description") %>'></asp:Label>                                           
                                            </ItemTemplate>
                                            </asp:TemplateField>
                                        </Columns>
                                        <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" />
                                    </asp:GridView>
                                </td>
                                <td valign="top" align="left" width="1%">
                                    &nbsp;</td>
                            </tr>
                           
        </table>
        <table>
        <tr>
        <td>
        <asp:Label ID="lblErrMsg" runat="server" Width="100%" Text = "" ForeColor="red"></asp:Label>
        </td>        
        </tr>
        </table> 

    </div>

    </form>

</body>

</html>
 

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)
    {
        con.Open();
        tran = con.BeginTransaction();
        cmd.Transaction = tran;
        string slno=null;
        try
        {
                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;
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();
                lblMessage.Text = "Records inserted successfully";
        }            
        catch(Exception ex)
        {
                tran.Rollback();
                lblMessage.Text = "Error Occured  near Sr. No. = " + slno + "<br />";
                lblMessage.Text += "No Data Is Inserted. <br />";
                //lblMessage.Text += ex.Message;
        }
        finally
        {
                con.Close();
        }