Update table by ExecuteNonQuery In VB.NET

ExecuteNonQuery method use to execute a SQL Command that does not return a set of rows. This method you can use when want to do manipulation such as Update,Delete and Insert sql commands.
  • 6273
Download Files:
 

In my previous article you have seen that how can use ObjectDataSource to add database to Grid view. Now you will learn how to update table using ObjectDataSource.
 I have use ExecuteNONQuery and ExecuteReader methods in this article.
 ExecuteNonQuery method use to execute a SQL Command that does not return a set of rows. This method you can use when want to do manipulation such as Update,Delete and Insert sql commands.
 ExecuteReader method use for accessing data. It provide read only ,forward only and connected record set.

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" AutoGenerateColumns="False" 
            DataSourceID="ObjectDataSource1"  >
            <Columns>
                <asp:CommandField ShowEditButton="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 ID="ObjectDataSource1" runat="server" 
            SelectMethod="All_Detail" TypeName="Employee" UpdateMethod="Update_Detail">
            <UpdateParameters>
                <asp:Parameter Name="Id" Type="Int32" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="Country" Type="String" />
                <asp:Parameter Name="Phone" Type="Int64" />
                <asp:Parameter Name="Salary" Type="Int64" />
            </UpdateParameters>
        </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 AsStringByVal Country As StringByVal 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

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


Edit1.gif

 

edit2.gif
 

Edit3.gif
 

Edit4.gif

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.