Introduction
In this article I will explain how to insert, update and delete data in gridview using dropdown list in asp.net.
Insert,Update and Delete data into database is an important portion in Asp.Net. In this tutorial we use SQL-Server as backend. Here, we insert, Update, Delete data from a Customer Information form into database using C# Asp.Net. There are three steps to insert, update,delete data into database.
- Create table in database.
- Design .aspx page.
- Write Code on .aspx.CS page.
Step 1. Create table in Database
Table Name: - Customer
Query for creating a table
Step 2. Create/design .aspx page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td colspan="2" style="text-align:center;"><h3>Customer Information</h3></td>
</tr>
<tr>
<td colspan="2" style="text-align:center;">
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="Id" DataValueField="Id" Width="70px"></asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:mycon %>" SelectCommand="SELECT * FROM [customer]"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td>First Name :</td>
<td><asp:TextBox ID="TextBox1" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Last Name :</td>
<td><asp:TextBox ID="TextBox2" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Address :</td>
<td><asp:TextBox ID="TextBox3" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Email :</td>
<td><asp:TextBox ID="TextBox4" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Contact No :</td>
<td><asp:TextBox ID="TextBox5" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td colspan="2" style="text-align:center;">
<asp:Button ID="btninsert" runat="server" Text="Insert" OnClick="btninsert_Click" />
<asp:Button ID="btnupdate" runat="server" Text="Update" OnClick="btnupdate_Click" />
<asp:Button ID="btndelete" runat="server" Text="Delete" OnClick="btndelete_Click" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
</td>
</tr>
</table>
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" DataKeyNames="Id" >
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" InsertVisible="False" ReadOnly="True" SortExpression="Id" />
<asp:BoundField DataField="firstname" HeaderText="firstname" SortExpression="firstname" />
<asp:BoundField DataField="lastname" HeaderText="lastname" SortExpression="lastname" />
<asp:BoundField DataField="address" HeaderText="address" SortExpression="address" />
<asp:BoundField DataField="email" HeaderText="email" SortExpression="email" />
<asp:BoundField DataField="contactno" HeaderText="contactno" SortExpression="contactno" />
</Columns>
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:mycon %>" SelectCommand="SELECT * FROM [customer]"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
Screen-shot
Step 3. C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;
public partial class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ConnectionString);
SqlCommand cmd = new SqlCommand();
protected void Page_Load(object sender, EventArgs e)
{
bindgrid();
}
protected void bindgrid()
{
if (!IsPostBack == true)
{
if (con.State == ConnectionState.Closed)
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from customer";
cmd.Connection = con;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
con.Close();
}
}
}
protected void btninsert_Click(object sender, EventArgs e)
{
if (IsPostBack == true)
{
if (con.State == ConnectionState.Closed)
{
con.Open();
cmd.CommandText = "insert into customer values(@firstname,@lastname,@address,@email,@contactno)";
cmd.Connection = con;
cmd.Parameters.AddWithValue("@firstname", TextBox1.Text);
cmd.Parameters.AddWithValue("@lastname", TextBox2.Text);
cmd.Parameters.AddWithValue("@address", TextBox3.Text);
cmd.Parameters.AddWithValue("@email", TextBox4.Text);
cmd.Parameters.AddWithValue("@contactno", TextBox5.Text);
cmd.ExecuteNonQuery();
bindgrid();
Label1.ForeColor = System.Drawing.Color.Green;
Label1.Text = "Inserted Data...";
Response.Redirect("Default.aspx");
con.Close();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
}
else
{
Label1.ForeColor = System.Drawing.Color.Red;
Label1.Text = "Error...";
}
}
}
protected void btnupdate_Click(object sender, EventArgs e)
{
if (con.State == ConnectionState.Closed)
{
con.Open();
cmd.CommandText = "update customer set firstname=@firstname,lastname=@lastname,address=@address,email=@email,contactno=@contactno where id='"+DropDownList1.SelectedItem.Value+"'";
cmd.Connection = con;
cmd.Parameters.AddWithValue("@firstname", TextBox1.Text);
cmd.Parameters.AddWithValue("@lastname", TextBox2.Text);
cmd.Parameters.AddWithValue("@address", TextBox3.Text);
cmd.Parameters.AddWithValue("@email", TextBox4.Text);
cmd.Parameters.AddWithValue("@contactno", TextBox5.Text);
cmd.ExecuteNonQuery();
bindgrid();
Label1.ForeColor = System.Drawing.Color:Green;
Label1.Text = "Updated Data...";
Response.Redirect("Default.aspx");
con.Close();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
}
else
{
Label1.ForeColor = System.Drawing.Color.Red;
Label1.Text = "Error...";
}
}
protected void btndelete_Click(object sender, EventArgs e)
{
if (con.State == ConnectionState.Closed)
{
con.Open();
cmd.CommandText = "delete customer where id='" + DropDownList1.SelectedItem.Value + "'";
cmd.Connection = con;
cmd.Parameters.AddWithValue("@firstname", TextBox1.Text);
cmd.Parameters.AddWithValue("@lastname", TextBox2.Text);
cmd.Parameters.AddWithValue("@address", TextBox3.Text);
cmd.Parameters.AddWithValue("@email", TextBox4.Text);
cmd.Parameters.AddWithValue("@contactno", TextBox5.Text);
cmd.ExecuteNonQuery();
bindgrid();
Label1.ForeColor = System.Drawing.Color.Green;
Label1.Text = "Deleted Data...";
Response.Redirect("Default.aspx");
con.Close();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
}
else
{
Label1.ForeColor = System.Drawing.Color.Red;
Label1.Text = "Error...";
}
}
}
Explanation:
You are advised to change your SqlConnection string where web.config file
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
</system.web>
<connectionStrings>
<add name="mycon" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\KRUNAL\Documents\Blogspot Data\InsertUpdateDeleteData\App_Data\Database.mdf;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
</system.web>
<connectionStrings>
<add name="mycon" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\KRUNAL\Documents\Blogspot Data\InsertUpdateDeleteData\App_Data\Database.mdf;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
Output:
Thank You...
No comments :
Post a Comment