ASP.NET Delete Data In A GridView Control Using VB.NET

Trang chủ » Visual Basic Clear Gridview » ASP.NET Delete Data In A GridView Control Using VB.NET

Có thể bạn quan tâm

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

article1.gif

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.

article2.gif

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.

article3.gif

Figure3

Now click on the delete button this will show a confirm message box before delete.

article4.gif

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.

article5.gif

Figure5

Từ khóa » Visual Basic Clear Gridview