Delete row from table using ExecuteNonQuery in VB.NET

Using ExecuteNonQuery to delete row from table.
  • 3028
Download Files:
 

In my previous articleI 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 IntegerByVal FirstName As StringByVal LastName As StringByVal Country AsStringByVal Phone As LongByVal 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

delete1.gif

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.

delete2.gif

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.