This articles describes you how to delete SQL
Server database data using GridView Control in ASP. NET. To do that we create a
table in a SQL database. we use a GridView control to display table data on the
form and also delete database data using GridView control.
SQL Server Database
Create Table
Now create a table in a SQL Server database with
username, password and email fields. The table looks like this.
CREATE TABLE [dbo].[userinfo1](
[UserId] [int]
IDENTITY(1,1) NOT NULL,
[Username] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Address] [varchar](50) NULL
) ON [PRIMARY]
Now inserting Records in the table.
INSERT INTO userinfo1 VALUES('Monu','monu@gmail.com','Mathura')
GO
INSERT INTO userinfo1 VALUES('Rohatash','rohatash@gmail.com','Delhi')
Go
INSERT INTO userinfo1 VAlues
('Ram','ram@gmail.com','Agra')
go
INSERT INTO userinfo1 VAlues
('Shyam','shyam@gmail.com','kanpur')
Now using select statement
SELECT * FROM userinfo1
OUTPUT
Figure1
Step 2:
Now drag and drop a GridView control on asp. net the form. Select GridView and
press F4 to property window and set the columns collection property of the
GridView control. GridView control looks like below figure.
Figure2
The ASP.NET code for the GridView control. In this code below code, you see
database table columns binding with the bound fields and formatting is provided
using the template fields.
| <asp:GridView ID="GridView1" runat="Server" BorderWidth="1px" CellPadding="4" HeaderStyle-HorizontalAlign="left" RowStyle-VerticalAlign="Top" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False" onrowcommand="GridView1_RowCommand"> <Columns> <asp:TemplateField HeaderText="Username"> <ItemTemplate> <%# Eval("Username") %> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtusername" runat="Server" Text='<%# Eval("Username") %>' Columns="30"></asp:TextBox> <asp:RequiredFieldValidator ID="req1" runat="Server" Text="*" ControlToValidate="txtusername"></asp:RequiredFieldValidator> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Email"> <ItemTemplate> <%# Eval("Email") %> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtemail" runat="Server" TextMode="MultiLine" Rows="10" Columns="50" Text='<%# Eval("Email") %>'></asp:TextBox> <asp:RequiredFieldValidator ID="req2" runat="Server" Text="*" ControlToValidate="txtemail"></asp:RequiredFieldValidator> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Address"> <ItemTemplate> <%# Eval("Address") %> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtaddress" runat="Server" TextMode="MultiLine" Rows="10" Columns="50" Text='<%# Eval("Address") %>'></asp:TextBox> <asp:RequiredFieldValidator ID="req2" runat="Server" Text="*" ControlToValidate="txtaddress"></asp:RequiredFieldValidator> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField ShowHeader="False"> <ItemTemplate> <span onclick="return confirm('Are you sure to insert?')"> <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandArgument='<%# bind("userid") %>' CommandName="Delete1" Text="Delete"></asp:LinkButton> </ItemTemplate> </asp:TemplateField> </Columns> <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#FFFBD6" ForeColor="#333333" VerticalAlign="Top" /> <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" /> <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" /> <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> |
Add the following VB.NET code on the page.
This RowCommand event is use to delete a row in
database.
| Imports System.Data.SqlClient Imports System.Data Public Class WebForm1 Inherits System.Web.UI.Page Dim conn As New SqlConnection("Data Source=.;uid=sa;pwd=Password$2;database=master") Dim ad As New SqlDataAdapter() Dim cmd As New SqlCommand() Dim dataTable As DataTable Protected Sub Page_Load(ByVal senderAs Object,ByVal e As System.EventArgs)Handles Me.Load If Not IsPostBack Then BindData() End If End Sub Private Sub BindData() dataTable =New DataTable() cmd.Connection = conn cmd.CommandText = "SELECT * FROM userinfo1" ad =New SqlDataAdapter(cmd) ad.Fill(dataTable) GridView1.DataSource = dataTable GridView1.DataBind() End Sub Protected Sub GridView1_RowCommand(ByVal senderAs Object,ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs)Handles GridView1.RowCommand If e.CommandName ="Delete1" Then Dim userid As String = e.CommandArgument.ToString() cmd.Connection = conn cmd.CommandText = "DELETE FROM userinfo1 WHERE UserId='" & userid & "'" conn.Open() cmd.ExecuteNonQuery() conn.Close() BindData() End If End Sub End Class |
Now build and run the application.
Figure3
Now click on the delete button this will show a confirm
message box before delete.
Figure4
Now click on the OK button of confirm box this will
delete row from the GridView and also from database. After delete row GridView
looks like this.
Figure5
Now also test Database table.
Note: You can see a demo of this article
by downloading this application.
Conclusion
This was a quick and simple overview of GridView control. Hope you like this
article.