ASP.NET delete data in a GridView Control using VB.NET

This articles describes you how to delete SQL Server database data using GridView Control in ASP. NET.
  • 7398
 

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 ArgsHandles 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

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.