In
my previous article, I
have explained how you can update your table using ExecuteNonQuery. Now, in
this article I am going to explain how you can delete specific row from table.
As, I explain that ExecuteNonQuery method use for data manipulation (insert,
update and delete).
I have created Delete_One method that will delete specific row from table.
Default.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"
AutoGenerateColumns="False" AutoGenerateEditButton="True"
DataKeyNames="ID">
<Columns>
<asp:CommandField ShowDeleteButton="True" />
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName"
SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName"
SortExpression="LastName" />
<asp:BoundField DataField="Country" HeaderText="Country"
SortExpression="Country" />
<asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
<asp:BoundField DataField="Salary" HeaderText="Salary"
SortExpression="Salary" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource TypeName="Employee" SelectMethod="All_Detail"
DeleteMethod="Delete_One" ID="ObjectDataSource1" runat="server">
<DeleteParameters>
<asp:Parameter Name="Id" Type="Int32" />
</DeleteParameters>
</asp:ObjectDataSource>
</div>
</form>
</body>
</html>
App_code/Employee.vb
Imports
Microsoft.VisualBasic
Imports
System.Collections.Generic
Imports System.Web
Imports
System.Web.Configuration
Imports System.Data
Imports
System.Data.SqlClient
Public Class Employee
Private
Shared ReadOnly Connection_String
As String
Shared Sub New()
Connection_String = WebConfigurationManager.ConnectionStrings("EmployeeTable").ConnectionString
End Sub
Private E_id
As Integer
Private E_fname
As String
Private E_lname
As String
Private E_country
As String
Private E_phone
As Long
Private E_salary
As Long
Public
Property ID() As Integer
Get
Return E_id
End Get
Set(ByVal
value As Integer)
E_id = value
End Set
End Property
Public
Property FirstName() As String
Get
Return E_fname
End Get
Set(ByVal
value As String)
E_fname = value
End Set
End
Property
Public
Property LastName() As String
Get
Return E_lname
End Get
Set(ByVal
value As String)
E_lname = value
End Set
End
Property
Public
Property Country() As String
Get
Return E_country
End Get
Set(ByVal
value As String)
E_country = value
End Set
End
Property
Public
Property Phone() As Long
Get
Return E_phone
End Get
Set(ByVal
value As Long)
E_phone = value
End Set
End
Property
Public
Property Salary() As Long
Get
Return E_salary
End Get
Set(ByVal
value As Long)
E_salary = value
End Set
End
Property
Public
Function All_Detail() As List(Of Employee)
Dim List_Emp
As New List(Of Employee)()
Dim con As New SqlConnection(Connection_String)
Dim cmd As New SqlCommand("SELECT
Emp_id,Emp_fname,Emp_lname,Emp_countyr,Emp_phone_no,Emp_salary from Employee",
con)
Using con
con.Open()
Dim SdReader
As SqlDataReader
= cmd.ExecuteReader()
While SdReader.Read()
Dim Emp
As New Employee()
Emp.ID = CInt(SdReader("Emp_ID"))
Emp.FirstName = DirectCast(SdReader("Emp_fname"),
String)
Emp.LastName = DirectCast(SdReader("Emp_lname"),
String)
Emp.Country = DirectCast(SdReader("Emp_countyr"),
String)
Emp.Phone = CLng(SdReader("Emp_phone_no"))
Emp.Salary = CLng(SdReader("Emp_salary"))
List_Emp.Add(Emp)
End While
End
Using
Return List_Emp
End Function
Public Sub
Update_Detail(ByVal Id
As Integer,
ByVal FirstName As String, ByVal
LastName As String,
ByVal Country As String, ByVal
Phone As Long,
ByVal Salary As Long)
Dim con As New SqlConnection(Connection_String)
Dim cmd As New SqlCommand("Update
Employee set Emp_fname=@fname, Emp_lname=@lname, Emp_countyr=@country,
Emp_phone_no=@phone, Emp_salary=@salary where Emp_id=@id",
con)
cmd.Parameters.AddWithValue("@id",
Id)
cmd.Parameters.AddWithValue("@fname",
FirstName)
cmd.Parameters.AddWithValue("@lname",
LastName)
cmd.Parameters.AddWithValue("@country",
Country)
cmd.Parameters.AddWithValue("@phone",
Phone)
cmd.Parameters.AddWithValue("@salary",
Salary)
Using con
con.Open()
cmd.ExecuteNonQuery()
End Using
End Sub
Public Sub Delete_One(ByVal
Id As Integer)
Dim con As New SqlConnection(Connection_String)
Dim cmd As New SqlCommand("DELETE
Employee WHERE Emp_id=@id", con)
cmd.Parameters.AddWithValue("@id",
Id)
Using con
con.Open()
cmd.ExecuteNonQuery()
End Using
End Sub
End Class
Web.Config
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="EmployeeTable" connectionString="Data
Source=GURJEET-PC\GURJEET;Initial Catalog=master;Persist Security Info=True;User
ID=sa;Password=gurjeetsingh"
providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<compilation debug="true" strict="false" explicit="true" targetFramework="4.0"/>
</system.web>
</configuration>
Output
Click
on Delete button on first row. When you click on Delete button , first row will
delete from table. See below output after clicking on delete button you will get
this type of output.
