Insert data into database from Gridview in ASP.Net

<%@ 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%">
</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();
}
}

5 Comments

Join the conversation! Your thoughts help the community grow.

Aashima Chauhan5y ago
Hyii i tried your code but getting error at gv1.cells[0].............. the error is identifier expected? can you tell me why is this so???
shweta kulkarni11y ago
please suggest what is wrong in above code
shweta kulkarni11y ago
String str = "INSERT INTO JOB_Quality_Rating values('" + comboBox1.SelectedItem + "','" + comboBox3.SelectedItem + "','" + textBox1.Text + "'.'" + cmbtaskno.SelectedItem + "','" + comboBox2.SelectedItem + "','" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[1].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[2].Value.ToString() + "','" + txtTotal.Text + "','" + mdlconnection.user_id + "','" + DateTime.Now + "')"; //int d = mdlconnection.excuteQuery(str); //if (d > 0) mdlconnection.excuteQuery(str); { MessageBox.Show("Job Quality Save sucessfully");
selvi subramanian12y ago
but here you use foreach so many times data are inserted
Spandana DotNet12y ago
Nice One