Gridview select update,delete,insert,sorting and paging operations


In Gridview we can bind the data using SQL Server. Select, insert, delete, edit, update, CancelEdit, sorting, and paging operations can also be done in GridView...Please go through below example.

Example:
In Html Coding


<asp:Label ID="Label6" runat="server" Text="ID"><asp:Label> <asp:TextBox ID="TextBox1" runat="server"><asp:TextBox>
<asp:Label ID="Label7" runat="server" Text="Salary"><asp:Label>
<asp:TextBox ID="TextBox5" runat="server"><asp:TextBox>
<asp:Label ID="Label8" runat="server" Text="year of passing"></asp:Label>
<asp:TextBox ID="TextBox6" runat="server"><asp:TextBox>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
AutoGenerateEditButton="True" BackColor="White" BorderColor="White"
BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1"
GridLines="None" AutoGenerateDeleteButton ="True" ShowFooter ="True" AllowPaging="true"
Width="100%" PageSize="10" AllowSorting="True" AutoGenerateSelectButton="true">
<Columns>
<asp:TemplateField HeaderText="id" SortExpression ="id">
<EditItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("id") %>'><asp:Label>
<EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="TextBox4" runat="server"><asp:TextBox>
<FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("id") %>'><asp:Label>
<ItemTemplate>
<asp:TemplateField>
<asp:TemplateField HeaderText="salary" SortExpression="salary">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("salary") %>'><asp:TextBox>
<EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="TextBox7" runat="server"><asp:TextBox>
<FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("salary") %>'><asp:Label>
<ItemTemplate>
<asp:TemplateField>
<asp:TemplateField HeaderText="yearofpassing" SortExpression="yearofpassing">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("yearofsalary") %>'><asp:TextBox>
<EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="TextBox8" runat="server"><asp:TextBox>
<FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("yearofsalary") %>'><asp:Label>
<ItemTemplate>
<asp:TemplateField>
<asp:TemplateField HeaderText="Insert">
<EditItemTemplate>
<asp:Label ID="Label5" runat="server"><asp:Label>
<EditItemTemplate>
<FooterTemplate>
<asp:Button ID="Button3" runat="server" Text="Insert" CommandName ="Insert" >
<FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server"><asp:Label>
<ItemTemplate>
<asp:TemplateField>
<Columns>
<FooterStyle BackColor="#C6C3C6" ForeColor="Black" >
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" >
<PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" >
<RowStyle BackColor="#DEDFDE" ForeColor="Black" >
<SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" >
<SortedAscendingCellStyle BackColor="#F1F1F1" >
<SortedAscendingHeaderStyle BackColor="#594B9C" >
<SortedDescendingCellStyle BackColor="#CAC9C9" >
<SortedDescendingHeaderStyle BackColor="#33276A" >
<asp:GridView>


In vbcoding:

Imports System.Data.SqlClient
Partial Class About
Inherits System.Web.UI.Page
Dim con As SqlConnection = New SqlConnection("SERVER=197.165.5.16\Sql2008;DATABASE=master;uid=sa;pwd=Sdguf;")
Dim order As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then
order = "asc"
ViewState("order") = order
bindata()
End If
End Sub
Sub bindata()
con.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from employee_salary", con)
Dim ds As Data.DataSet = New Data.DataSet()
da.Fill(ds)
GridView1.DataSource = ds
GridView1.DataBind()

con.Close()
End Sub
Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging
GridView1.PageIndex = e.NewPageIndex
bindata()
End Sub
Protected Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit
GridView1.EditIndex = -1
bindata()
End Sub
Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
If (e.CommandName.Equals("Insert")) Then
con.Open()
Dim com As SqlCommand = New SqlCommand("insert into employee_salary values(" + CType(GridView1.FooterRow.FindControl("TextBox4"), TextBox).Text + "," + CType(GridView1.FooterRow.FindControl("TextBox7"), TextBox).Text + "," + CType(GridView1.FooterRow.FindControl("TextBox8"), TextBox).Text + ")", con)
com.ExecuteNonQuery()
con.Close()
bindata()
End If
End Sub
Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting
con.Open()
Dim com As SqlCommand = New SqlCommand("delete from employee_salary where id=" + CType(GridView1.Rows(e.RowIndex).FindControl("Label1"), Label).Text + "", con)
com.ExecuteNonQuery()
con.Close()
bindata()
End Sub
Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing
GridView1.EditIndex = e.NewEditIndex
bindata()
End Sub
Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
con.Open()
Dim com As SqlCommand = New SqlCommand("update employee_salary set salary=" + CType(GridView1.Rows(e.RowIndex).FindControl("TextBox2"), TextBox).Text + ",yearofsalary=" + CType(GridView1.Rows(e.RowIndex).FindControl("TextBox3"), TextBox).Text + " where id=" + CType(GridView1.Rows(e.RowIndex).FindControl("Label4"), Label).Text + " ", con)
com.ExecuteNonQuery()
con.Close()
GridView1.EditIndex = -1
bindata()
End Sub

Protected Sub GridView1_SelectedIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSelectEventArgs) Handles GridView1.SelectedIndexChanging
Dim row As GridViewRow = GridView1.Rows(e.NewSelectedIndex)

' Display the company name from the selected row.
' In this example, the third column (index 2) contains
' the company name.
TextBox1.Text = CType(row.FindControl("Label1"), Label).Text
TextBox5.Text = CType(row.FindControl("Label2"), Label).Text
TextBox6.Text = CType(row.FindControl("Label3"), Label).Text

End Sub
Protected Sub GridView1_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView1.Sorting
Try
con.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from employee_salary order by " + e.SortExpression.ToString() + " " + SortOrder(), con)
Dim ds As Data.DataSet = New Data.DataSet()
da.Fill(ds)
GridView1.DataSource = ds
GridView1.DataBind()
con.Close()
Exit Sub
Catch ex As Exception
End Try
End Sub

Public Function SortOrder() As String
order = Convert.ToString(ViewState("order").ToString())
If ViewState("order").ToString() = "asc" Then
order = "desc"
ViewState("order") = order
ElseIf ViewState("order").ToString() = "desc" Then
order = "asc"
ViewState("order") = order
End If
Return order
End Function

End Class


Reference: Gridview insert,delete,select,update,sorting and paging operation.


Comments

Author: karthik27 Jun 2011 Member Level: Bronze   Points : 0

Yeah Preeti i get you.

thanx alot..

Author: Bhuvana S08 Jul 2011 Member Level: Gold   Points : 1

Nice Preethi...
Very useful...
Good keep it up..
Keep on posting good articles like this...
Wish you all the very best....

Guest Author: Ryan20 Feb 2012

I am facing the same problem but wrapping in form tag is not fixing the problem. Preferences Page One Page Two Page Three You are not logged in.Please login to access e service Please help.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: