How to Bind,Insert,Edit,Update,Delete in GridView in asp.net VB.Net ~ Asp.Net,C#.Net,VB.Net,MVC,jQuery,JavaScipt,AJAX,WCF,Sql Server example

Introduction: In this article you will learn the following using Asp.Net with VB.Net Language. You can also read the same article in Asp.Net with C# language on Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView in asp.net C#

How to Save/Insert and update data in Sql Server Database from asp.net web application?
How to Bind Data from Sql Server Database table to GridView Data Control?
How to Edit, Update and Delete in GridView?
How to Fill Day, Month and Year in DropDownList control and set current date as selected?
How to find controls like TextBox and DropDownList placed inside GridView and get their values?
How to implement paging in GridView?

In previous articles i explained Bind,upload,download,delete image files from the GridView in asp.net using Sql Server as a back end database and How to create crystal reports in visual studio 2010 using asp.net and Create and consume WCF Services and WCF Service to bind,insert,edit,update,delete from sql server database and Bind state categories and cities sub categories in single dropdownlist and Prevent duplicate record entry on page refresh.

Bind,save, edit, update, delete, paging example in gridview

Click on image to enlarge

Implementation: Let's create an asp.net application to see it in action.

First You need to create the Database in Sql Server and Name it "Emp_DB" or whatever you want. Create a Table having columns as shown in figure and name it "Emp_Tb" .

Note: Emp_Id_Pk column is set to Primary key and Identity specification is set to yes with Identity increment and Identity seed equal to 1.

In the Web.config file create the connection string as:

</connectionStrings>

Note: Replace the Data Source and Initial Catalog as per your application.

Create a folder in the root directory and name it "Images" to store the edit and delete images used in gridview. Search edit and delete .png icon image from the google search and add to the "Images" folder.
Now In the Design page(.aspx) design the web page as:

Note: I have also implemented the asp.net validations on the Employee Name, Address and the Salary fields.

HTML Source

<fieldset style="width:545px;">

<legend style="font-size:20px; color:Green;">Save, Edit, Update, Delete, Paging example in

Gridview</legend>

<table>

<tr>

<td>Employee Name</td>

<td>

<asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="rfvEmpName" runat="server"

ErrorMessage="Please enter employee name"

ControlToValidate="txtEmployeeName" Display="Dynamic" ForeColor="Red"

SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr><td> Address</td>

<td>

<asp:TextBox ID="txtAddress" runat="server" TextMode="MultiLine" Height="59px"

Width="267px"></asp:TextBox><asp:RequiredFieldValidator ID="rfvAddress" runat="server"

ErrorMessage="Please enter Address"

ControlToValidate="txtAddress" Display="Dynamic" ForeColor="Red"

SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<td>

<asp:DropDownList ID="ddlYear" runat="server" ToolTip="Select Year">

</asp:DropDownList>

<asp:DropDownList ID="ddlMonth" runat="server" ToolTip="Select Month">

</asp:DropDownList>

<asp:DropDownList ID="ddlDay" runat="server" ToolTip="Select Day">

</asp:DropDownList>

</td>

</tr>

<tr><td>Salary</td>

<td>

<asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="rfvSal" runat="server"

ErrorMessage="Please enter salary" ControlToValidate="txtSalary"

Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>
<tr><td> </td>

<td>

<asp:Button ID="btnSave" runat="server" Text="Save" />

<asp:Button ID="btnReset" runat="server" Text="Reset" CausesValidation="false" />

</td></tr>

</table>
<asp:GridView ID="grdEmp" runat="server"

DataKeyNames="Emp_Id_Pk" CssClass="rowHover" RowStyle-CssClass="rowHover"

AutoGenerateColumns="False" AllowPaging="True"

PageSize="4" CellPadding="4" ForeColor="#333333" GridLines="None">

<asp:TemplateField HeaderText="Emp Name" HeaderStyle-HorizontalAlign="Center">

<%#Eval("EmpName")%>

</ItemTemplate>

<asp:TextBox ID="txtEmpName" runat="server" Text='<%#Eval("EmpName") %>'></asp:TextBox>

<asp:RequiredFieldValidator ID="rfvEmpName" runat="server" ErrorMessage="*" ForeColor="red" ControlToValidate="txtEmpName" Display="Dynamic" SetFocusOnError="True"></asp:RequiredFieldValidator>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Address" HeaderStyle-HorizontalAlign="Center">

<%#Eval("Address")%>

</ItemTemplate>

<asp:TextBox ID="txtEditAddress" runat="server" Text='<%#Eval("Address") %>' TextMode="MultiLine"></asp:TextBox>

<asp:RequiredFieldValidator ID="rfvAddress" runat="server" ErrorMessage="*" ForeColor="red" ControlToValidate="txtEditAddress" Display="Dynamic" SetFocusOnError="True"></asp:RequiredFieldValidator>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="DOJ" HeaderStyle-HorizontalAlign="Center" ItemStyle-Width="180px">

<%#Eval("DOJ", "{0:dd/MM/yyyy}")%>

</ItemTemplate>

<asp:DropDownList ID="ddlEditDay" runat="server" ToolTip="Select Day">

</asp:DropDownList>

<asp:DropDownList ID="ddlEditMonth" runat="server" ToolTip="Select Month">

</asp:DropDownList>

<asp:DropDownList ID="ddlEditYear" runat="server" ToolTip="Select Year">

</asp:DropDownList>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Salary" HeaderStyle-HorizontalAlign="Center">

<%#Eval("Salary")%>

</ItemTemplate>

<asp:TextBox ID="txtSal" runat="server" Text='<%#Eval("Salary") %>'></asp:TextBox>

<asp:RequiredFieldValidator ID="rfvSal" runat="server" ErrorMessage="*" ForeColor="red" ControlToValidate="txtSal" Display="Dynamic" SetFocusOnError="True"></asp:RequiredFieldValidator>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Edit" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center">

<asp:ImageButton ID="imgEdit" runat="server" CommandName="Edit" ImageUrl="~/Images/Edit.png" ToolTip="Edit" CausesValidation="false"/>

</ItemTemplate>

<asp:LinkButton ID="lkUpdate" runat="server" Text="Update" CommandName="Update" ToolTip="Update" CausesValidation="false"></asp:LinkButton>

<asp:LinkButton ID="lkCancel" runat="server" Text="Cancel" CommandName="Cancel" ToolTip="Cancel" CausesValidation="false"></asp:LinkButton>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Delete" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center">

<asp:ImageButton ID="imgDelete" runat="server" CommandName="Delete" ImageUrl="~/Images/Delete.png" OnClientClick="return confirm('Are you sure you want to delete selected record ?')" ToolTip="Delete" CausesValidation="false"/>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</fieldset>

VB.Net Code to Save, Edit, Update, Cancel, Delete, Paging in GridView

In the Code behind file (.aspx.vb) write the code as:

#Region "namespaces"

Imports System.Collections.Generic

Imports System.Linq

Imports System.Web

Imports System.Web.UI

Imports System.Web.UI.WebControls

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Imports System.IO

#End Region

Partial Class _Default

Inherits System.Web.UI.Page

'Creating Connection object and getting connection string

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

#Region "page Load Event"

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

'Check and Open Database connection

If con.State = ConnectionState.Closed Then

con.Open()

End If

If Not Page.IsPostBack Then

'Bind gridview existing records

bindGridView()

'DOJ Field

'Fill Years with current year selected

For i As Integer = 2013 To 1980 Step -1

ddlYear.Items.Add(i.ToString())

ddlYear.Items.FindByValue(System.DateTime.Now.Year.ToString()).Selected = True

'Set current year as selected

'Fill Months with current month selected

For i As Integer = 1 To 12

ddlMonth.Items.Add(i.ToString())

ddlMonth.Items.FindByValue(System.DateTime.Now.Month.ToString()).Selected = True

‘Set current month as selected

'Fill days based on selected month

FillDays()

End If

End Sub

#End Region

#Region "Save employee record"

Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click

Dim doj As String = String.Empty

'Joining selected Day, month and year to create Date of Joining

doj = Convert.ToString((ddlDay.SelectedValue + "/" + ddlMonth.SelectedValue & "/") + ddlYear.SelectedValue)

Try

Dim cmd As New SqlCommand("Insert into Emp_Tb(EmpName,Address,DOJ,Salary) values (@EmpName,@Address,@Doj,@Salary)", con)

cmd.CommandType = CommandType.Text

cmd.Parameters.Add("@EmpName", SqlDbType.VarChar, 100).Value = txtEmployeeName.Text.Trim()

cmd.Parameters.Add("@Address", SqlDbType.VarChar, 500).Value = txtAddress.Text.Trim()

cmd.Parameters.Add("@Doj", SqlDbType.VarChar, 50).Value = doj

cmd.Parameters.Add("@Salary", SqlDbType.Int).Value = txtSalary.Text.Trim()

cmd.ExecuteNonQuery()

'Clear all controls after saving the record

Clear_Controls()

'Bind gridview after saving the record

bindGridView()

Catch ex As Exception

'Show error occured in message box using JavaScript

ScriptManager.RegisterClientScriptBlock(Page, Page.[GetType](), Guid.NewGuid().ToString(), "alert('" & ex.Message.ToString() & "');", True)

Finally

doj = String.Empty

'Close the database connection

con.Close()

End Try

End Sub

#End Region

#Region "Bind gridview with data"

Public Sub bindGridView()

Try

Dim cmd As New SqlCommand("select * from Emp_Tb", con)

Dim adp As New SqlDataAdapter(cmd)

Dim ds As New DataSet()

adp.Fill(ds)

If ds.Tables(0).Rows.Count > 0 Then

grdEmp.DataSource = ds

grdEmp.DataBind()

Else

'Bind Empty grdview with Columns names in Header and "No employee record found" message if no records are found in the database

BindEmptyGridWithHeader(grdEmp, ds)

End If

Catch ex As Exception

ScriptManager.RegisterClientScriptBlock(Page, Page.[GetType](), Guid.NewGuid().ToString(), "alert('" & ex.Message.ToString() & "');", True)

Finally

con.Close()

End Try

End Sub

#End Region

#Region "paging in gridview"

Protected Sub grdEmp_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles grdEmp.PageIndexChanging

grdEmp.PageIndex = e.NewPageIndex

bindGridView()

End Sub

#End Region

#Region "Cancel Code in GridView"

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

grdEmp.EditIndex = -1

bindGridView()

End Sub

#End Region

#Region "Deletion in gridview"

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

Try

Dim empId As Integer = Convert.ToInt32(grdEmp.DataKeys(e.RowIndex).Value)

Dim cmd As New SqlCommand("delete from Emp_Tb where Emp_Id_Pk= @EmpId", con)

cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId

cmd.CommandType = CommandType.Text

cmd.ExecuteNonQuery()

grdEmp.EditIndex = -1

bindGridView()

Catch ex As Exception

ScriptManager.RegisterClientScriptBlock(Page, Page.[GetType](), Guid.NewGuid().ToString(), "alert('" & ex.Message.ToString() & "');", True)

Finally

con.Close()

End Try

End Sub

#End Region

#Region "updation in gridview"

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

Dim empName As String = String.Empty

Dim address As String = String.Empty

Dim dojUpdated As String = String.Empty

Try

'Finding dropdownlist inside gridview and getting updated value

Dim ddlEditDojYear As DropDownList = DirectCast(grdEmp.Rows(e.RowIndex).FindControl("ddlEditYear"), DropDownList)

Dim ddlEditDojMonth As DropDownList = DirectCast(grdEmp.Rows(e.RowIndex).FindControl("ddlEditMonth"), DropDownList)

Dim ddlEditDojDay As DropDownList = DirectCast(grdEmp.Rows(e.RowIndex).FindControl("ddlEditDay"), DropDownList)

'Creating Updated DOJ field

dojUpdated = Convert.ToString((ddlEditDojDay.SelectedValue + "/" + ddlEditDojMonth.SelectedValue & "/") + ddlEditDojYear.SelectedValue)

'Read Emp_Id_Pk from DataKeyNames

Dim empId As Integer = Convert.ToInt32(grdEmp.DataKeys(e.RowIndex).Value)

'Finding TextBox inside gridview and getting updated value

empName = DirectCast(grdEmp.Rows(e.RowIndex).FindControl("txtEmpName"), TextBox).Text.Trim()

address = DirectCast(grdEmp.Rows(e.RowIndex).FindControl("txtEditAddress"), TextBox).Text.Trim()

Dim sal As Integer = Convert.ToInt32(DirectCast(grdEmp.Rows(e.RowIndex).FindControl("txtSal"), TextBox).Text.Trim())

Dim cmd As New SqlCommand("update Emp_Tb set EmpName=@EmpName,Address=@Address,DOJ=@Doj,Salary=@Salary where Emp_Id_Pk=@EmpId", con)

cmd.CommandType = CommandType.Text

cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId

cmd.Parameters.Add("@EmpName", SqlDbType.VarChar, 50).Value = empName

cmd.Parameters.Add("@Address", SqlDbType.VarChar, 50).Value = address

cmd.Parameters.Add("@Doj", SqlDbType.VarChar, 50).Value = dojUpdated

cmd.Parameters.Add("@Salary", SqlDbType.BigInt).Value = sal

cmd.ExecuteNonQuery()

grdEmp.EditIndex = -1

bindGridView()

Catch ex As Exception

ScriptManager.RegisterClientScriptBlock(Page, Page.[GetType](), Guid.NewGuid().ToString(), "alert('" & ex.Message.ToString() & "');", True)

Finally

empName = String.Empty

address = String.Empty

dojUpdated = String.Empty

con.Close()

End Try

End Sub

#End Region

#Region "Editing in gridview"

Protected Sub grdEmp_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles grdEmp.RowEditing

grdEmp.EditIndex = e.NewEditIndex

bindGridView()

End Sub

#End Region

#Region "Bind Empty Gridview with header"

Public Sub BindEmptyGridWithHeader(ByVal gridView As GridView, ByVal ds As DataSet)

Try

If ds.Tables(0).Rows.Count = 0 Then

'Add a blank row to the dataset

ds.Tables(0).Rows.Add(ds.Tables(0).NewRow())

'Bind the DataSet to the GridView

gridView.DataSource = ds

gridView.DataBind()

'Get the number of columns to know what the Column Span should be

Dim columnCount As Integer = gridView.Rows(0).Cells.Count

'Call the clear method to clear out any controls that you use in the columns.

gridView.Rows(0).Cells.Clear()

gridView.Rows(0).Cells.Add(New TableCell())

gridView.Rows(0).Cells(0).ColumnSpan = columnCount

gridView.Rows(0).Cells(0).Text = "<font color=Red><b><center>No employee record Found !</center></b></font>"

End If

'Do your exception handling here

Catch ex As Exception

End Try

End Sub

#End Region

#Region "Grid RowDataBound Event"

Protected Sub grdEmp_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdEmp.RowDataBound

Dim day As String = String.Empty

Dim month As String = String.Empty

Dim year As String = String.Empty

Try

If e.Row.RowType = DataControlRowType.DataRow AndAlso grdEmp.EditIndex = e.Row.RowIndex Then

'Read Emp_Id_Pk from DataKeyNames

Dim empId As Integer = Convert.ToInt32(grdEmp.DataKeys(e.Row.RowIndex).Value)

Dim adp1 As New SqlDataAdapter("Select * from Emp_Tb where Emp_Id_Pk=@EmpId", con)

adp1.SelectCommand.CommandType = CommandType.Text

adp1.SelectCommand.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId

Dim dtEdit As New DataTable()

adp1.Fill(dtEdit)

Dim getDoj As String = dtEdit.Rows(0)("DOJ").ToString()

'Splitting the DOJ field into day, month and year

Dim strDoj As String() = getDoj.Split("/"c)

day = strDoj(0)

month = strDoj(1)

year = strDoj(2)

Dim ddlEditDojYear As DropDownList = DirectCast(e.Row.FindControl("ddlEditYear"), DropDownList)

Dim ddlEditDojMonth As DropDownList = DirectCast(e.Row.FindControl("ddlEditMonth"), DropDownList)

Dim ddlEditDojDay As DropDownList = DirectCast(e.Row.FindControl("ddlEditDay"), DropDownList)

'Fill Years

For i As Integer = 2013 To 1980 Step -1

ddlEditDojYear.Items.Add(i.ToString())

ddlEditDojYear.SelectedValue = year

'Fill Months

For i As Integer = 1 To 12

ddlEditDojMonth.Items.Add(i.ToString())

ddlEditDojMonth.SelectedValue = month

'Fill days

FillDaysInsideGrid(ddlEditDojDay, ddlEditDojYear, ddlEditDojMonth, day)

End If

Catch ex As Exception

ScriptManager.RegisterClientScriptBlock(Page, Page.[GetType](), Guid.NewGuid().ToString(), "alert('" & ex.Message.ToString() & "');", True)

Finally

day = String.Empty

month = String.Empty

year = String.Empty

End Try

End Sub

#End Region

#Region "DOJ"

Protected Sub ddlYear_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlYear.SelectedIndexChanged

FillDays()

End Sub

Protected Sub ddlMonth_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlMonth.SelectedIndexChanged

FillDays()

End Sub

Public Sub FillDays()

If Not Page.IsPostBack Then

ddlDay.Items.Clear()

End If

'getting number of days in selected month & year

Dim noofdays As Integer = DateTime.DaysInMonth(Convert.ToInt32(ddlYear.SelectedValue), Convert.ToInt32(ddlMonth.SelectedValue))

'Fill days

For i As Integer = 1 To noofdays

ddlDay.Items.Add(i.ToString())

If Not Page.IsPostBack Then

'Set current date as selected

ddlDay.Items.FindByValue(System.DateTime.Now.Day.ToString()).Selected = True

End If

End Sub

#End Region

#Region "Fill Days Inside GridView"

Public Sub FillDaysInsideGrid(ByVal ddlEditDojDay As DropDownList, ByVal ddlEditDojYear As DropDownList, ByVal ddlEditDojMonth As DropDownList, ByVal day As String)

ddlEditDojDay.Items.Clear()

'getting number of days in selected month & year

Dim noofdays As Integer = DateTime.DaysInMonth(Convert.ToInt32(ddlEditDojYear.SelectedValue), Convert.ToInt32(ddlEditDojMonth.SelectedValue))

'Fill days

For i As Integer = 1 To noofdays

ddlEditDojDay.Items.Add(i.ToString())

ddlEditDojDay.SelectedValue = day

End Sub

#End Region

#Region "Clear & reset all controls"

Protected Sub btnReset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReset.Click

Clear_Controls()

End Sub

Private Sub Clear_Controls()

txtEmployeeName.Text = String.Empty

txtSalary.Text = String.Empty

txtEmployeeName.Focus()

txtAddress.Text = String.Empty

txtEmployeeName.Focus()

'Reset DOJ Field

'Fill Years with current year selected

ddlYear.Items.Clear()

For i As Integer = 2013 To 1980 Step -1

ddlYear.Items.Add(i.ToString())

ddlYear.Items.FindByValue(System.DateTime.Now.Year.ToString()).Selected = True

'set current year as selected

'Fill Months with current month selected

ddlMonth.Items.Clear()

For i As Integer = 1 To 12

ddlMonth.Items.Add(i.ToString())

ddlMonth.Items.FindByValue(System.DateTime.Now.Month.ToString()).Selected = True

'Set current month as selected

'Fill days according to current month

ddlDay.Items.Clear()

'getting number of days in selected month & year

Dim noofdays As Integer = DateTime.DaysInMonth(Convert.ToInt32(ddlYear.SelectedValue), Convert.ToInt32(ddlMonth.SelectedValue))

'Fill days

For i As Integer = 1 To noofdays

ddlDay.Items.Add(i.ToString())

ddlDay.Items.FindByValue(System.DateTime.Now.Day.ToString()).Selected = True

' Set current date as selected

End Sub

#End Region

End Class

Now over to you:

If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

8 comments

Click here for comments

Lalit Raghuvanshi

admin

August 04, 2013 ×

thanks for your appreciation..stay tuned by subscribing for more updates like this..

Anonymous

August 07, 2013 ×

hai i want code...but here didn't have download option please provide that one ....

It is always better to read, understand and create your own application so that you will have good understanding of the code. Follow the article and create your own.

Unknown

August 12, 2013 ×

NICE WORK SIR

thanks kesavanagaprasad thonta

October 11, 2013 ×

No word to praise
Excellent

February 08, 2014 ×

One of the most comprehensive explinations of the gridview including Insert functionality i've seen. Thanks!

Thanks for appreciating my work..i am glad you liked my article..stay connected and keep reading for more useful updates like this...:)

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..

How to Bind,Insert,Edit,Update,Delete in GridView in asp.net VB.Net

Related Post

8 comments