Insert Update Edit Delete Rows Record In GridView

In this example i am explaining how to Insert Update Edit Delete Records Rows In GridView With SqlDataSource Using C# VB.NET Asp.Net using SqlDataSource.

For inserting record, i've put textboxes in footer row of GridView using ItemTemplate and FooterTemaplete.


Go to design view of aspx page and drag a GridView control from toolbox, click on smart tag of GridView and choose new datasource
Select Database and click Ok
 
In next screen, Enter your SqlServer name , username and password and pick Database name from the dropdown , Test the connection
 
 
 In next screen, select the table name and fields , Click on Advance tab and check Generate Insert,Edit and Delete statements checkbox , alternatively you can specify your custom sql statements 
 
 
Click on ok to finish 
Check Enable Editing , enable deleting checkbox in gridView smart tag 
Now go to html source of page and define DatakeyNames field in gridview source
<asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="False" 
              DataKeyNames="ID"
              DataSourceID="SqlDataSource1" 
              OnRowDeleted="GridView1_RowDeleted" 
              OnRowUpdated="GridView1_RowUpdated" 
              ShowFooter="true" 
              OnRowCommand="GridView1_RowCommand">
</asp:GridView>
Remove the boundFields and put ItemTemplate and EditItemTemplate and labels and textboxs respectively, complete html source of page should look like this
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="False" 
              DataKeyNames="ID"
              DataSourceID="SqlDataSource1" 
              OnRowDeleted="GridView1_RowDeleted" 
              OnRowUpdated="GridView1_RowUpdated" 
              ShowFooter="true" 
              OnRowCommand="GridView1_RowCommand">
<Columns>
    <asp:CommandField ShowDeleteButton="True" 
                      ShowEditButton="True" />
    <asp:TemplateField HeaderText="ID" SortExpression="ID">
    <ItemTemplate>
    <asp:Label ID="lblID" runat="server" 
                          Text='<%#Eval("ID") %>'>
    </asp:Label>
    </ItemTemplate>
    <FooterTemplate>
    <asp:Button ID="btnInsert" runat="server" 
                Text="Insert" CommandName="Add" />
    </FooterTemplate>
    </asp:TemplateField>
    
    <asp:TemplateField HeaderText="FirstName" 
                       SortExpression="FirstName">
    <ItemTemplate>
    <asp:Label ID="lblFirstName" runat="server" 
               Text='<%#Eval("FirstName") %>'>
    </asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtFirstName" runat="server" 
                 Text='<%#Bind("FirstName") %>'>
    </asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox ID="txtFname" runat="server">
    </asp:TextBox>
    </FooterTemplate>
    </asp:TemplateField>
    
    <asp:TemplateField HeaderText="LastName" 
                       SortExpression="LastName">
    <ItemTemplate>
    <asp:Label ID="lblLastName" runat="server" 
               Text='<%#Eval("LastName") %>'>
    </asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtLastName" runat="server" 
                 Text='<%#Bind("LastName") %>'>
    </asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox ID="txtLname" runat="server">
    </asp:TextBox>
    </FooterTemplate>
    </asp:TemplateField>
    
    <asp:TemplateField HeaderText="Department" 
                       SortExpression="Department">
    <ItemTemplate>
    <asp:Label ID="lblDepartment" runat="server" 
               Text='<%#Eval("Department") %>'>
    </asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtDepartmentName" runat="server" 
                 Text='<%#Bind("Department") %>'>
    </asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox ID="txtDept" runat="server">
    </asp:TextBox>
    </FooterTemplate>
    </asp:TemplateField>
    
    <asp:TemplateField HeaderText="Location" 
                       SortExpression="Location">
    <ItemTemplate>
    <asp:Label ID="lblLocation" runat="server" 
               Text='<%#Eval("Location") %>'>
    </asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtLocation" runat="server" 
                 Text='<%#Bind("Location") %>'>
    </asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox ID="txtLoc" runat="server">
    </asp:TextBox>
    </FooterTemplate>
    </asp:TemplateField>
</Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:DBConString%>"
DeleteCommand="DELETE FROM [Employees] WHERE [ID] = @ID" 
InsertCommand="INSERT INTO [Employees] ([FirstName], 
[LastName],[Department], [Location]) 
VALUES (@FirstName, @LastName, @Department, @Location)"
SelectCommand="SELECT [ID], [FirstName], [LastName], 
[Department], [Location] FROM [Employees]"
UpdateCommand="UPDATE [Employees] SET 
[FirstName] = @FirstName, [LastName] = @LastName, 
[Department] = @Department, [Location] = @Location 
WHERE [ID] = @ID" OnInserted="SqlDataSource1_Inserted">

<DeleteParameters>
    <asp:Parameter Name="ID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
    <asp:Parameter Name="FirstName" Type="String" />
    <asp:Parameter Name="LastName" Type="String" />
    <asp:Parameter Name="Department" Type="String" />
    <asp:Parameter Name="Location" Type="String" />
    <asp:Parameter Name="ID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
    <asp:Parameter Name="FirstName" Type="String" />
    <asp:Parameter Name="LastName" Type="String" />
    <asp:Parameter Name="Department" Type="String" />
    <asp:Parameter Name="Location" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<asp:Label ID="lblMessage" runat="server" 
           Font-Bold="True"></asp:Label><br />
</div>
</form>
Write this code in RowCommand Event of GridView in codebehind
C# code Behind
protected void GridView1_RowCommand
(object sender, GridViewCommandEventArgs e)
{
  if (e.CommandName == "Add")
  {
   string strFirstName = ((TextBox)
   GridView1.FooterRow.FindControl("txtFname")).Text;

   string strLastName = 
   ((TextBox)GridView1.FooterRow.FindControl
                         ("txtLname")).Text;

   string strDepartment = 
   ((TextBox)GridView1.FooterRow.FindControl
                            ("txtDept")).Text;
   string strLocation = ((TextBox)GridView1.FooterRow.
                          FindControl("txtLoc")).Text;
   //SqlDataSource1.InsertParameters.Clear();
   //SqlDataSource1.InsertParameters.Add
                       //("FirstName", strFirstName);
   //SqlDataSource1.InsertParameters.Add
                          //("LastName", strLastName);
   //SqlDataSource1.InsertParameters.Add
                         //("Department", strDepartment);
   //SqlDataSource1.InsertParameters.Add
                              //("Location", strLocation);

  SqlDataSource1.InsertParameters["FirstName"].DefaultValue 
                                             = strFirstName;
  SqlDataSource1.InsertParameters["LastName"].DefaultValue 
                                             = strLastName;
  SqlDataSource1.InsertParameters["Department"].DefaultValue 
                                             = strDepartment;
  SqlDataSource1.InsertParameters["Location"].DefaultValue
                                            = strLocation;
  SqlDataSource1.Insert();
  }
}

VB.NET Code Behind

Protected Sub GridView1_RowCommand(ByVal sender As Object, 
                       ByVal e As GridViewCommandEventArgs)
        If e.CommandName = "Add" Then
            Dim strFirstName As String = 
            DirectCast(GridView1.FooterRow.
            FindControl("txtFname"), TextBox).Text()

            Dim strLastName As String = 
            DirectCast(GridView1.FooterRow.
            FindControl("txtLname"), TextBox).Text()

            Dim strDepartment As String = 
            DirectCast(GridView1.FooterRow.
            FindControl("txtDept"), TextBox).Text()
            Dim strLocation As String = 
            DirectCast(GridView1.FooterRow.
            FindControl("txtLoc"), TextBox).Text()

            'SqlDataSource1.InsertParameters.Clear();
            'SqlDataSource1.InsertParameters.Add
            '("FirstName", strFirstName);
            'SqlDataSource1.InsertParameters.Add
            '("LastName", strLastName);
            'SqlDataSource1.InsertParameters.Add
            '("Department", strDepartment);
            'SqlDataSource1.InsertParameters.Add
            '("Location", strLocation);

            SqlDataSource1.InsertParameters("FirstName").
            DefaultValue = strFirstName
            SqlDataSource1.InsertParameters("LastName").
            DefaultValue = strLastName
            SqlDataSource1.InsertParameters("Department").
            DefaultValue = strDepartment
            SqlDataSource1.InsertParameters("Location").
            DefaultValue = strLocation
            SqlDataSource1.Insert()
        End If
    End Sub


Download the sample code attached


You would also like to read
Edit or update multiple records/rows in gridview with checkbox using C# in ASP.NET

Delete multiple rows records in Gridview with checkbox and confirmation in ASP.NET
If you like this post than join us or share

60 comments:

landhoolive said...

not bad:D


safia said...

Wow cool yar


amiT jaiN said...

@safia:

Thanks :)


Dorababu said...

Hi good article. Can i have the code for my requirement which is i am having the Data in the database as follows

Id Name FileName
1 Dorababu dora.doc
2 ABCDEFGH Abc.Pdf

The file names are displayed in grid view as link button what i need is when i click on that particular file name it should ask like this Open save cancel as we have when we are downloading the attached document of our mail. Can any one give me the code please


vaseem said...

sample code is not available for download .can u plz fix that problem


amiT jaiN said...

@vaseem:

Download link is fixed


Anonymous said...

man! i owe you a beer.


Anonymous said...

I want to upload files using upload control in sql table and I want download the uploaded files from sql and also manage or delete, update and edit using grind view how I can do? Please send me the code with short tutorial I need the above progresses in different page or form please send me argent i am beginner for asp.net and C#


Anonymous said...

I found this site using [url=http://google.com]google.com[/url] And i want to thank you for your work. You have done really very good site. Great work, great site! Thank you!

Sorry for offtopic


Anonymous said...

Gracias por el tutorial esta excelente! :):):):):):)


Anonymous said...

Could not find a suitable section so I written here, how to become a moderator for your forum, that need for this?


Anonymous said...

very nice tutorial....man what is your email id.
skype id.. wanna be in ur contact to learn more ...
thanks alot
please add me on skype jst_mir


Anonymous said...

goooooooooooooood


Anonymous said...

Clean and to the point...i like it! Thank you. :)


Anonymous said...

a


Anonymous said...

i m getting the following error in this program for all the 3events .Error 1 'ASP.gridview_aspx' does not contain a definition for 'GridView1_RowUpdated' and no extension method 'GridView1_RowUpdated' accepting a first argument of type 'ASP.gridview_aspx' could be found (are you missing a using directive or an assembly reference?)

'ASP.gridview_aspx' does not contain a definition for 'GridView1_OnRowDeleted' and no extension method 'GridView1_OnRowDeleted' accepting a first argument of type 'ASP.gridview_aspx' could be found (are you missing a using directive or an assembly reference?)

'ASP.gridview_aspx' does not contain a definition for 'SqlDataSource1_Inserted' and no extension method 'SqlDataSource1_Inserted' accepting a first argument of type 'ASP.gridview_aspx' could be found (are you missing a using directive or an assembly reference?)


Anonymous said...

hi i m getting this error

Error 1 'ASP.gridview_aspx' does not contain a definition for 'GridView1_RowUpdated' and no extension method 'GridView1_RowUpdated' accepting a first argument of type 'ASP.gridview_aspx' could be found (are you missing a using directive or an assembly reference?) 12
Error 2 'ASP.gridview_aspx' does not contain a definition for 'GridView1_OnRowDeleted' and no extension method 'GridView1_OnRowDeleted' accepting a first argument of type 'ASP.gridview_aspx' could be found (are you missing a using directive or an assembly reference?) 12
Error 3 'ASP.gridview_aspx' does not contain a definition for 'SqlDataSource1_Inserted' and no extension method 'SqlDataSource1_Inserted' accepting a first argument of type 'ASP.gridview_aspx' could be found (are you missing a using directive or an assembly reference?) 108


Anonymous said...

Oh man that you so much for your post at beneficial time. It helped me in my assignment. Thanks Alot


Anonymous said...

Oh man that you so much in return your enter at high-mindedness time. It helped me in my assignment. Thanks Alot


Anonymous said...

u did good job ur code working fine but when i insert a new record why it add two records instead of one with same values
i m using ur sample code download from given link (this page) i just replace ur c# code with vb code in a new project
please help me............

thanks
ROhit KUmar

kindly reply as soon as possible..........


Anonymous said...

How will i add exception error message in asp.net using sqldatasource


Anonymous said...

Fantastic web site, I hadn't come across csharpdotnetfreak.blogspot.com earlier during my searches!
Carry on the superb work!


Anonymous said...

Wow neat! This is a really great site! I am wondering if anyone else has come across something
like this in the past? Keep up the great work!


Jagadeesh said...

very useful for beginners!!


manoj kumar said...

I AM GETTING THIS ERROR
The type or namespace name 'List' could not be found (are you missing a using directive or an assembly reference?)


manoj kumar said...

i m getting this error
The type or namespace name 'List' could not be found (are you missing a using directive or an assembly reference?)


manoj kumar said...

i am getting this error The type or namespace name 'List' could not be found (are you missing a using directive or an assembly reference?)


Anonymous said...

Thanks for sharing this link, but unfortunately it seems to be offline... Does anybody have a mirror or another source? Please answer to my post if you do!

I would appreciate if a staff member here at csharpdotnetfreak.blogspot.com could post it.

Thanks,
William


Anonymous said...

Hello there,

This is a message for the webmaster/admin here at csharpdotnetfreak.blogspot.com.

Can I use some of the information from this post right above if I provide a link back to this website?

Thanks,
Jack


Anonymous said...

sir, I want to know if there is a way the items can be submitted to database
thanks
Ik


Anonymous said...

create a grid view for a doctor-patient facility using asp.net c#


Huma Mohsin said...

Awesome tutorial


Anonymous said...

Hi,

I have a message for the webmaster/admin here at csharpdotnetfreak.blogspot.com.

May I use part of the information from this blog post right above if I give a link back to this website?

Thanks,
James


Anonymous said...

Thanks, one small problem. I have this identity column into which I require to add an auto incrementing number. How do I specify the command "Set IDENTITY_INSERT on" for this db and turn it back to off again?


ram shanker said...

nice coding


Anonymous said...

Thank you


Anonymous said...

Thank you very much !


Anonymous said...

thank you very much !


Anonymous said...

Hi,

Thanks for sharing this link - but unfortunately it seems to be down? Does anybody here at csharpdotnetfreak.blogspot.com have a mirror or another source?


Cheers,
Thomas


Anonymous said...

Hi there,

Thanks for sharing this link - but unfortunately it seems to be down? Does anybody here at csharpdotnetfreak.blogspot.com have a mirror or another source?


Cheers,
Jules


vijay said...

Also Try This link friends.

http://vijayvigneshonflex.blogspot.com/p/how-to-do-insertupdatedelete-and-select.html

Its really superb !! insert,update,delete without writing a single piece of code.


Anonymous said...

Hi,

Thanks for sharing the link - but unfortunately it seems to be not working? Does anybody here at csharpdotnetfreak.blogspot.com have a mirror or another source?


Thanks,
Jack


Anonymous said...

Hi,

I have a message for the webmaster/admin here at csharpdotnetfreak.blogspot.com.

Can I use part of the information from your post above if I give a link back to your website?

Thanks,
Daniel


Anonymous said...

from this gredview coding, may i know how to display message box after we click delete


amiT jaiN said...

@Above: read Delete multiple records in Gridview with checkbox and confirmation to know how to show delete confirmation box using javascript in GridView


Anonymous said...

Hi , Amit Handsome , please can you guide me how i can auto rotate gridview one page to second page after 30 second ? example i have 5 pages in gridview i want that the first page auto move at second page after 30 second and 2nd page move at 3rd page after 30 second and so on... with ajax ? thanks .... you have excellent things in your posts keep it up ...... SSSuperb


Anonymous said...

hi i download a file and i want to run it so can you tell me how to configure it?

Thanks


Anonymous said...

sample code cann't download pls correct it


Anonymous said...

sorry now its downloaded


Nilesh Umaretiya said...

Thanks for provide easy code.............


Anonymous said...

Damn Gudd Tutorial..Thanks a ton budddy...:)


Anonymous said...

i have problem that said
-Object reference not set to an instance of an object.

Line 37: '("Location", strLocation);
Line 38:
Line 39: SqlDataSource1.InsertParameters("FirstName").DefaultValue = strFirstName
Line 40: SqlDataSource1.InsertParameters("LastName").DefaultValue = strLastName
Line 41: SqlDataSource1.InsertParameters("Department").DefaultValue = strDepartment


Anonymous said...

I do not disagree with this blog.


Anonymous said...

I fully agree completely...


Anonymous said...

This might be the greatest piece on here..


Anonymous said...

thanx for your valubale help.:)


ankur said...

how to update the value in gridview by creating connection string


ankur said...

i hv weite the code in c# to update delete insert record in grid view through connection string


Siyasanga(your student) said...

You are the best man, im sure you are in demand all over wow, thanks alot. please have a post on how to work with the Profile object in asp.net 4.0, please.

PS, Your number 1 fan :)


EMIL said...

below code is my delete action
nt wrks

protected void btnDelete_Click(object sender, EventArgs e)
{

string strID = string.Empty;

Class1 c = new Class1();
OdbcConnection con = c.getconnection();
if (con.State == ConnectionState.Open) con.Close();
con.Open();
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
CheckBox chkDelete = (CheckBox)GridView1.Rows[i].
Cells[0].FindControl("chkSelect");
if (chkDelete != null)
{
if (chkDelete.Checked)
{
strID = GridView1.Rows[i].Cells[1].Text;
cmd.CommandText ="DELETE FROM branchdtl WHERE BrnhId=?";
cmd.CommandType = CommandType.Text;

LoadWindow();

}
}
}


Find More Articles