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 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
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
