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();
        }      

COMMENT USING

PREMIUM SPONSORS

Hire Mobile & Web Developer on demand. 100% satisfaction. Try for 1 week or Money Back. Local and remote developers available all over USA.

Latest Blogs