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.
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 Integer, ByVal FirstName As String, ByVal LastName AsString, 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
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
