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','[email protected]','Mathura')
GO
INSERT INTO userinfo1 VALUES('Rohatash','[email protected]','Delhi')
Go
INSERT INTO userinfo1 VAlues ('Ram','[email protected]','Agra')
go
INSERT INTO userinfo1 VAlues ('Shyam','[email protected]','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)HandlesMe.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 AsSystem.Web.UI.WebControls.Grid View Command Event Args) Handles GridView1.Row Command
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