SubsDotNet: Gridview CRUD Operations using VB.NET

Ans:-

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 id="Head1" runat="server">
<title>Gridview CRUD Operations</title>
<style type="text/css">

</style>
</head>
<body>
<form id="form1" runat="server">
<center>
<div>
<div>Welcome..</div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AutoGenerateColumns="False" BackColor="White" BorderColor="#999999"
BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical"
PageSize="5" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" ShowFooter ="true" >
<RowStyle BackColor="#EEEEEE" ForeColor="Black" />
<Columns>
<asp:TemplateField HeaderText="Sr.No." SortExpression="srno">

<ItemTemplate>
<%#Container.DataItemIndex+1 %>
</ItemTemplate>
<FooterTemplate>

<asp:Button ID="btnAdd" runat="server" Text ="Add" onclick="btnAdd_Click"></asp:Button>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="DeptID" SortExpression="deptid">
<EditItemTemplate>
<asp:TextBox ID="txtDid" runat="server" Text='<%# Bind("deptid") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblDid" runat="server" Text='<%# Bind("deptid") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>

<asp:TextBox ID="txtDeptid" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="DeptName" SortExpression="dname">
<EditItemTemplate>
<asp:TextBox ID="txtDname" runat="server" Text='<%# Bind("dname") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblDname" runat="server" Text='<%# Bind("dname") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>

<asp:TextBox ID="txtDeptname" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="#DCDCDC" />
</asp:GridView>
<div></div>
</div>
</center>

</form>
</body>
</html>

Default.aspx.cs:-

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Page.IsPostBack <> True Then

BindData()

End If

End Sub

Sub BindData()

Dim cn As SqlConnection = New SqlConnection("server=localhost;Database=SubsDB;User id=sa;Pwd=123")

Dim strQry As String = "select * from Dept"

Dim da As SqlDataAdapter = New SqlDataAdapter(strQry, cn)

Dim ds As DataSet = New DataSet()

da.Fill(ds, "Dept")

GridView1.DataSource = ds

GridView1.DataBind()

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

BindData()

End Sub

Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting

Dim cn As SqlConnection = New SqlConnection("server=localhost;Database=SubsDB;User id=sa;Pwd=123")

cn.Open()

Dim grdv As GridViewRow = GridView1.Rows(e.RowIndex)

Dim tDid As Label = grdv.FindControl("lblDid")

Dim strQry As String = "Delete from Dept where deptid=" + tDid.Text

Dim cmd As SqlCommand = New SqlCommand(strQry, cn)

Dim i As Integer = cmd.ExecuteNonQuery()

If (i > 0) Then

Response.Write("Record is Successfully Deleted")

Else

Response.Write("Record is not Deleted")

End If

cn.Close()

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

BindData()

End Sub

Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating

Dim cn As SqlConnection = New SqlConnection("server=localhost;Database=SubsDB;User id=sa;Pwd=123")

cn.Open()

Dim grdv As GridViewRow = GridView1.Rows(e.RowIndex)

Dim tDid As TextBox = grdv.FindControl("txtDid")

Dim tDname As TextBox = grdv.FindControl("txtDname")

Dim strQry As String = "Update Dept set dname='" + tDname.Text + "' where deptid=" + tDid.Text

Dim cmd As SqlCommand = New SqlCommand(strQry, cn)

Dim i As Integer = cmd.ExecuteNonQuery()

If (i > 0) Then

Response.Write("Record is Successfully Updated")

Else

Response.Write("Record is not Updated")

End If

GridView1.EditIndex = -1

BindData()

cn.Close()

End Sub

Protected Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit

GridView1.EditIndex = -1

BindData()

End Sub

Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim cn As SqlConnection = New SqlConnection("server=localhost;Database=SubsDB;User id=sa;Pwd=123")

cn.Open()

Dim grdv As GridViewRow = GridView1.FooterRow

Dim tDid As TextBox = grdv.FindControl("txtDeptid")

Dim tDname As TextBox = grdv.FindControl("txtDeptname")

Dim strQry As String = "Insert into Dept(deptid,dname) values(" + tDid.Text + ",'" + tDname.Text + "')"

Dim cmd As SqlCommand = New SqlCommand(strQry, cn)

Dim i As Integer = cmd.ExecuteNonQuery()

If (i > 0) Then

Response.Write("Record is Successfully Inserted")

Else

Response.Write("Record is not Inserted")

End If

cn.Close()

End Sub

End Class

web.config:-

</connectionStrings>

Create Table in Sql Server:-

create table Dept(deptid int constraint pr_key1 primary key,dname varchar(15))

SubsDotNet

Tuesday, 28 January 2014

Gridview CRUD Operations using VB.NET

No comments:

Post a Comment