ARTICLE

Edit, Delete, Update and Insert operations in a DataGrid: Part II

Posted by Rahul Kumar Saxena in Articles | ADO.NET in VB.NET on March 17, 2009

Tags: Delete and insert a record in a DataGrid, Edit

The basic operations of DataGrid, like Edit, delete, Update, Insert a record.

Tweet
19321
3

Reader Level: Beginner

In the previous article, I explained, how to edit, delete and update the record in DataGrid. Now I am going to explain about inserting a new record in a DataGrid.

This is the aspx code.

<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" Debug="true" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Edit Update Delete Cancel and Insert In DataGrid</title>

</head>

<body>

<div>

<asp:DataGrid ID="gridedit" runat="server" Width="100px"
DataKeyField="id" BorderStyle="Ridge" GridLines="None"

BorderWidth="2px" BorderColor="White" BackColor="White"
CellPadding="3" CellSpacing="1" AllowSorting="True"

PagerStyle-HorizontalAlign="Center" HorizontalAlign="Left"

          OnEditCommand="editgrid_click" OnCancelCommand=
          "gridcancel_click" OnPageIndexChanged=
          "gridedit_PageIndexChanged"
          OnUpdateCommand="updategrid_UpdateCommand"
         Height="267px" PageSize=5 AllowPaging="true"

          OnDeleteCommand="gridedit_DeleteCommand"
          AutoGenerateColumns="false"
          SelectedItemStyle-Width="100px">

<asp:BoundColumn DataField=id HeaderText="ID" ItemStyle-Width="10px" Visible="false">

</asp:BoundColumn>

<asp:BoundColumn DataField=name HeaderText="Name">

</asp:BoundColumn>

<asp:BoundColumn DataField=F_name HeaderText="F_Name">

</asp:BoundColumn>

<asp:BoundColumn DataField=l_name HeaderText="L_Name">

</asp:BoundColumn>

<asp:BoundColumn DataField=City HeaderText="City">

</asp:BoundColumn>

<asp:BoundColumn DataField=State HeaderText="State">

</asp:BoundColumn>

<asp:EditCommandColumn CancelText="Cancel" EditText="Edit" UpdateText="Update" HeaderText="Edit">

</asp:EditCommandColumn>

<asp:ButtonColumn CommandName="Delete" HeaderText="Delete" Text="Delete">

</asp:ButtonColumn>

</Columns>

</asp:DataGrid>

<asp:Button ID="btninsert" runat="server" Text="Click Here to Insert a New record" Width="250px"
OnClick="InsertNewRecord_click" /><br /><br />

<asp:Label ID="lblnewname" runat="server" Width="120px" Text="Enter Name" Visible="false"></asp:Label>

<asp:TextBox ID="txtnewname" runat="server" Visible="false"></asp:TextBox><br /><br />

<asp:Label ID="lblF_name" runat="server" Width="120px" Text="Enter Father Name" Visible="false"></asp:Label>

<asp:TextBox ID="txtF_name" runat="server" Visible="false" ></asp:TextBox>

<asp:Label ID="lblLast_name" runat="server" Width="120px" Text="Enter Last Name" Visible="false"></asp:Label>

<asp:TextBox ID="txtLast_Nmae" runat="server" Visible="false"></asp:TextBox>

<asp:Label ID="lblcity" runat="server" Text="Enter city" Width="120px" Visible="false"></asp:Label>

<asp:TextBox ID="txtcity" runat="server" Visible="false"></asp:TextBox>

<asp:Label ID="lblState" runat="server" Width="120px" Text="Enter State Name" Visible="false"></asp:Label>

<asp:TextBox ID="txtState" runat="server" Visible="false"></asp:TextBox>

<asp:Button ID="btnnewRecordSubmit" runat="server" Text="Insert" OnClick="Submitnew" Width="100px"
Visible="false" />

</div>

</form>

</body>

</html>

By the help of this aspx code, the application will look like as:

Figure 1: Application in Designing State.

Here, I set the visible property of all labels, textBox and insert button as False. All of these wiil be visible when I will click on Insert a new record button.

The Source code for this is:

Imports System

Imports System.Data

Imports System.Configuration

Imports System.Web

Imports System.Web.Security

Imports System.Web.UI

Imports System.Web.UI.WebControls

Imports System.Web.UI.WebControls.WebParts

Imports System.Web.UI.HtmlControls

Imports System.Data.SqlClient

Partial Public Class _Default

Inherits System.Web.UI.Page

Dim da As SqlDataAdapter

Dim ds As DataSet = New DataSet()

Dim con As SqlConnection

Dim cmd As SqlCommand = New SqlCommand()

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

If Not Page.IsPostBack Then

Binddata()

End If

End Sub

' Define the Edit Command

Public Sub editgrid_click(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)

gridedit.EditItemIndex = e.Item.ItemIndex

Binddata()

End Sub

' Define the Cancel Command

Public Sub gridcancel_click(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)

gridedit.EditItemIndex = -1

Binddata()

End Sub

'Here we Bind the data

Public Sub Binddata()

con = New SqlConnection(ConfigurationSettings.AppSettings("connect"))

cmd.CommandText = "select * from record order by id Asc"

cmd.Connection = con

da = New SqlDataAdapter(cmd)

da.Fill(ds)

con.Open()

cmd.ExecuteNonQuery()

gridedit.DataSource = ds

gridedit.DataBind()

con.Close()

End Sub

'Update Command Defination

Protected Sub updategrid_UpdateCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)

Try

con = New SqlConnection(ConfigurationSettings.AppSettings("connect"))

cmd.CommandText = "Update record set name=@name ,F_name=@F_Name, l_name=@l_name,City=@City,State=@State where id=@id"

cmd.Parameters.Add("@name", SqlDbType.Char).Value = (CType(e.Item.Cells(1).Controls(0), TextBox)).Text

cmd.Parameters.Add("@F_name", SqlDbType.Char).Value = (CType(e.Item.Cells(2).Controls(0), TextBox)).Text

cmd.Parameters.Add("@l_name", SqlDbType.Char).Value = (CType(e.Item.Cells(3).Controls(0), TextBox)).Text

cmd.Parameters.Add("@City", SqlDbType.Char).Value = (CType(e.Item.Cells(4).Controls(0), TextBox)).Text

cmd.Parameters.Add("@State", SqlDbType.Char).Value = (CType(e.Item.Cells(5).Controls(0), TextBox)).Text

cmd.Parameters.Add("@id", SqlDbType.Int).Value = gridedit.DataKeys(e.Item.ItemIndex)

cmd.Connection = con

cmd.Connection.Open()

cmd.ExecuteNonQuery()

cmd.Connection.Close()

gridedit.EditItemIndex = -1

Binddata()

Catch ex As Exception

Response.Write(ex.Message)

End Try

End Sub

' Delete Command Defination

Public Sub gridedit_DeleteCommand(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)

con = New SqlConnection(ConfigurationSettings.AppSettings("connect"))

Dim U_ID As Integer = CType(gridedit.DataKeys(CType(e.Item.ItemIndex), int, __1__))

cmd.CommandText = " Delete from record where id=" + U_ID

cmd.Connection = con

cmd.Connection.Open()

cmd.ExecuteNonQuery()

cmd.Connection.Close()

gridedit.EditItemIndex = -1

Binddata()

End Sub

' For Paging

Public Sub gridedit_PageIndexChanged(ByVal source As Object, ByVal e As DataGridPageChangedEventArgs)

gridedit.CurrentPageIndex = e.NewPageIndex

Binddata()

End Sub

'Link for Insert a new Record in a table

Public Sub InsertNewRecord_click(ByVal source As Object, ByVal e As System.EventArgs)

lblNewname.Visible = True

txtNewname.Visible = True

lblF_name.Visible = True

txtF_name.Visible = True

lblLast_name.Visible = True

txtLast_Nmae.Visible = True

lblcity.Visible = True

txtcity.Visible = True

lblState.Visible = True

txtState.Visible = True

btnNewRecordSubmit.Visible = True

End Sub

' Command for insert a new Record

Public Sub SubmitShadows(ByVal source As Object, ByVal e As System.EventArgs)

con = New SqlConnection(ConfigurationSettings.AppSettings("connect"))

cmd.CommandText = "insert into record(name,F_name,l_name,City,State) values(@name,@F_Name,@l_name,@City,@State)"

cmd.Parameters.Add("@name", SqlDbType.Char).Value = txtNewname.Text

cmd.Parameters.Add("@F_Name", SqlDbType.Char).Value = txtF_name.Text

cmd.Parameters.Add("@l_name", SqlDbType.Char).Value = txtLast_Nmae.Text

cmd.Parameters.Add("@City", SqlDbType.Char).Value = txtcity.Text

cmd.Parameters.Add("@State", SqlDbType.Char).Value = txtState.Text

cmd.Connection = con

cmd.Connection.Open()

cmd.ExecuteNonQuery()

cmd.Connection.Close()

gridedit.EditItemIndex = -1

Binddata()

txtNewname.Text = ""

txtF_name.Text = ""

txtLast_Nmae.Text = ""

txtcity.Text = ""

txtState.Text = ""

lblNewname.Visible = False

txtNewname.Visible = False

lblF_name.Visible = False

txtF_name.Visible = False

lblLast_name.Visible = False

txtLast_Nmae.Visible = False

lblcity.Visible = False

txtcity.Visible = False

lblState.Visible = False

txtState.Visible = False

btnNewRecordSubmit.Visible = False

End Sub

End Class

Now, on running the project, the window will be looking like:

Figure 2:A State PROJECT (RUNNING)

Suppose, if user wants to insert a new record, then hehas to click on "Click Here" button to insert a new record. After clicking the button, the new window will be looking:

Figure 3: Insert a new Record.

When User click the insert button, record will be inserted in the DataGrid.

Figure 4: Record Inserted.

Article Extensions

Contents added by sadegh razavi on Jul 08, 2010

Download File: FIFA_World_Cup_South_Africa_2010_Official.zip

post comment

i work on a flash website with vb.net content management so i can add update delete from xml
but there is some area i cant change it and i am starter with asp.net so i need help.
woh can i send you the project and tell me who much the cost if you want to make all the content management.
wating for your response, its ergent
best regards.

amer akkad
admin@amerakkad.com

Posted by amer akkad on May 28, 2009

HI aarthi,

Thanx to visit my article,

Can u explain me where u r getting this error

Posted by Rahul Kumar Saxena on Mar 18, 2009

Hi this article was very useful, but i got an error pls help me Server Error in '/' Application. -------------------------------------------------------------------------------- Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [NullReferenceException: Object reference not set to an instance of an object.] WebApplication1.WebForm1.Submitnew(Object sender, EventArgs e) +54 System.EventHandler.Invoke(Object sender, EventArgs e) +0 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +83 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292

Posted by aarthi on Mar 18, 2009

TOP LEADERS