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 AsString, 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.
