Post
Ask Question

Insert data into database from Gridview in ASP.Net

  1.  <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"Inherits="Insert_data_from_Gridview_database._Default" %>  
  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  3. <html xmlns="http://www.w3.org/1999/xhtml" >  
  4. <head runat="server">  
  5.     <title>Untitled Page</title>  
  6. </head>  
  7. <body>  
  8.     <form id="form1" runat="server">  
  9.     <div>   
  10. <table>  
  11.         <tr>              
  12.             <td valign="top" align="left">Upload File <input type="file"  id="fileName" tabindex="1"  Runat="server" onclick="return fileName_onclick()" /></td>  
  13.             <td valign="top" align="left" class="style1"><a href="Docs/UploadData1.csv" target="_blank">Download template.</a></td>  
  14.         </tr>          
  15.         <tr>  
  16.         <td><asp:button id="btnUpload"  Text="Upload Data" Runat="server" OnClick="btnUpload_Click" /></td>  
  17.                                 <td valign="top" align="left" class="style1">  
  18.                                     <asp:Button ID="btnAddToDb" runat="server" Height="25px" Text="Insert To Database."  
  19.                                         Width="138px" onclick="btnAddToDb_Click" Enabled="false" />  
  20.                                     <asp:Label ID="lblMessage" runat="server" Text=""></asp:Label>  
  21.             </td>  
  22.                             </tr>                              
  23.                             <tr>  
  24.                                 <td valign="top" align="left" width="1%"></td>  
  25.                                 <td valign="top" align="left" class="style1">  
  26.                                     <asp:GridView ID="Gvrecords" runat="server" Height="217px" Width="340px"  
  27.                                         CellPadding="4" ForeColor="#333333" GridLines="None"  
  28.                                         AutoGenerateColumns="False">  
  29.                                         <RowStyle BackColor="#EFF3FB" />  
  30.                                         <Columns>  
  31.                                             <asp:TemplateField HeaderText="Id">  
  32.                                             <ItemTemplate>  
  33.                                                 <asp:Label ID="lblId" runat="server" Text='<% #Eval("ID") %>'></asp:Label>                                             
  34.                                             </ItemTemplate>  
  35.                                             </asp:TemplateField>  
  36.                                             <asp:TemplateField HeaderText="Product Name">  
  37.                                             <ItemTemplate>  
  38.                                                 <asp:Label ID="lblName" runat="server" Text='<% #Eval("Name") %>'></asp:Label>                                             
  39.                                             </ItemTemplate>  
  40.                                             </asp:TemplateField>  
  41.                                             <asp:TemplateField HeaderText="Price">  
  42.                                             <ItemTemplate>  
  43.                                                 <asp:Label ID="lblPrice" runat="server" Text='<% #Eval("Price") %>'></asp:Label>                                             
  44.                                             </ItemTemplate>  
  45.                                             </asp:TemplateField>  
  46.                                             <asp:TemplateField HeaderText="Product Description">  
  47.                                             <ItemTemplate>  
  48.                                                 <asp:Label ID="lblDescription" runat="server" Text='<% #Eval("Description") %>'></asp:Label>                                             
  49.                                             </ItemTemplate>  
  50.                                             </asp:TemplateField>  
  51.                                         </Columns>  
  52.                                         <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  
  53.                                         <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />  
  54.                                         <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />  
  55.                                         <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  
  56.                                         <EditRowStyle BackColor="#2461BF" />  
  57.                                         <AlternatingRowStyle BackColor="White" />  
  58.                                     </asp:GridView>  
  59.                                 </td>  
  60.                                 <td valign="top" align="left" width="1%">  
  61.                                      </td>  
  62.                             </tr>  
  63.         </table>  
  64.         <table>  
  65.         <tr>  
  66.         <td>  
  67.         <asp:Label ID="lblErrMsg" runat="server" Width="100%" Text = "" ForeColor="red"></asp:Label>  
  68.         </td>          
  69.         </tr>  
  70.         </table>   
  71.     </div>  
  72.     </form>  
  73. </body>  
  74. </html>
  1. using System;  
  2. using System.Collections;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Linq;  
  6. using System.Web;  
  7. using System.Web.Security;  
  8. using System.Web.UI;  
  9. using System.Web.UI.HtmlControls;  
  10. using System.Web.UI.WebControls;  
  11. using System.Web.UI.WebControls.WebParts;  
  12. using System.Xml.Linq;  
  13. using System.Data.SqlClient;  
  14. namespace Insert_data_from_Gridview_database  
  15. {  
  16.     SqlConnection con;  
  17.     SqlCommand cmd;  
  18.     SqlTransaction tran;          
  19.     protected void Page_Load(object sender, EventArgs e)  
  20.     {  
  21.         con = new SqlConnection("Data Source=MANIK-PC\\SQLEXPRESS;Initial Catalog=mycity123;Integrated Security=True");  
  22.         cmd = new SqlCommand();  
  23.         cmd.Connection = con;             
  24.     }  
  25.     protected void btnAddToDb_Click(object sender, EventArgs e)  
  26.     {  
  27.         con.Open();  
  28.         tran = con.BeginTransaction();  
  29.         cmd.Transaction = tran;  
  30.         string slno=null;  
  31.         try  
  32.         {  
  33.                 foreach (GridViewRow  g1 in Gvrecords.Rows )  
  34.                 {  
  35.                     string id = (g1.FindControl("lblId") as Label).Text;  
  36.                     string name = (g1.FindControl("lblName") as Label).Text;  
  37.                     string price = (g1.FindControl("lblPrice") as Label).Text;  
  38.                     string description = (g1.FindControl("lblDescription") as Label).Text;  
  39.                     string query = "insert into product values(" +id+ ",'" + name + "'," +price+ ",'" + description + "')";  
  40.                     //cmd.CommandText = "insert into Members values ('" + g1.Cells[0].Text + "','" + g1.Cells[1].Text + "','" + g1.Cells[2].Text + "','" + g1.Cells[3].Text + "')";  
  41.                     slno = id;  
  42.                     cmd.CommandText = query;  
  43.                     cmd.ExecuteNonQuery();  
  44.                 }  
  45.                 tran.Commit();  
  46.                 lblMessage.Text = "Records inserted successfully";  
  47.         }              
  48.         catch(Exception ex)  
  49.         {  
  50.                 tran.Rollback();  
  51.                 lblMessage.Text = "Error Occured  near Sr. No. = " + slno + "<br />";  
  52.                 lblMessage.Text += "No Data Is Inserted. <br />";  
  53.                 //lblMessage.Text += ex.Message;  
  54.         }  
  55.         finally  
  56.         {  
  57.                 con.Close();  
  58.         }  
  59. }