ASP.NET GridView - Add a new record

Introduction

This code drop is part of a Smash and Grab series for the seriously ADD (Attention Deficit Disorder) programmer. I'll bet there are a lot of other programmers out there who would like to make code available to others, but just don't want to spend the hours required to create a full-blown "CodeProject" article. This is an attempt to see if I can make some useful code available to other programmers, and do it in 10 minutes. I encourage others to contribute to "Smash and Grab".

The point of this series is to present usable pre-canned classes or techniques which solve a real world problem without going into too much detail about how it works. I mean, who really cares about how System.Collections.ArrayList works, you just use it.

Please fully comment the code so that the person who really cares (and has the time) will be able to understand the underlying algorithm.

Using the code

There has been a lot of whining on the net about what a train-wreck the ASP.NET 2.0 GridView is because it won't do inserts and if the table source is empty, the grid does not even render.

Here is a solution in less than 15 lines of code.

Create a GridView as usual (I am using the NorthWind Categories table as an example).
Create the data source.

Change:

SelectCommand="SELECT [CategoryID], [CategoryName]," + 
            " convert(nvarchar(1000),[Description]) FROM [Categories]";

to:

SelectCommand="SELECT '' as [CategoryID], '' as [CategoryName]," + 
         " '' as [Description] UNION SELECT [CategoryID]," + 
         " [CategoryName], convert(nvarchar(1000),[Description])" + 
         " FROM [Categories]";

This inserts a blank row as the first line of the grid.

This is the line that you will be editing to add the new record.

Add:
```
OnRowUpdating="GridView1_RowAdding";
```
as an event to the grid (you can do it through the GUI).
Add the highlighted JavaScript code.
It finds the first "Edit Delete" text and changes it to "Add".
Add the "GridView1_RowAdding" method.

OnRowUpdating is called by the GridView before it updates a row. The GridView thinks it is doing an update to CategoryID=0 (which will silently fail). Meanwhile, you are scooping the data and doing a secret insert.

<%@ Page Language="C#" AutoEventWireup="true" 
             CodeFile="AddUpdate.aspx.cs" Inherits="AddUpdate" %>

<!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 runat="server">
    <title>Untitled Page</title>
    <script>
    function FixGrid(idGrid)
    {
      // content looks like:
      //"<A href=\"javascript:__doPostBack('GridView1',
      //    'Edit$0')\">Edit</A> 
      // <A href=\"javascript:__doPostBack('GridView1',
      //    'Delete$0')\">Delete</A>"
      // replace Edit with Add, remove Delete
      var Parts = 
       idGrid.firstChild.childNodes[1].childNodes[0].innerHTML.split(">Edit<");
      var tmp = Parts.join(">Add<"); 
      Parts = tmp.split(">Delete<");
      idGrid.firstChild.childNodes[1].childNodes[0].innerHTML = 
                                        Parts.join("><");
    }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" 
                 AutoGenerateColumns="False" DataKeyNames="CategoryID"
            DataSourceID="SqlDataSource1" 
                    OnRowUpdating="GridView1_RowAdding">
            <Columns>
                <asp:CommandField ShowDeleteButton="True" 
                    ShowEditButton="True" />
                <asp:BoundField DataField="CategoryID" 
                    HeaderText="CategoryID" InsertVisible="False"
                    ReadOnly="True" SortExpression="CategoryID" />
                <asp:BoundField DataField="CategoryName" 
                       HeaderText="CategoryName" 
                       SortExpression="CategoryName" />
                <asp:BoundField DataField="Description" 
                       HeaderText="Description" 
                       SortExpression="Description" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
              ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            DeleteCommand="DELETE FROM [Categories] 
                           WHERE [CategoryID] = @CategoryID"
            InsertCommand="INSERT INTO [Categories] 
                           ([CategoryName], [Description]) 
                           VALUES (@CategoryName, @Description)"
            SelectCommand="SELECT '' as [CategoryID], 
                '' as [CategoryName], '' as [Description]
                UNION SELECT [CategoryID], [CategoryName], 
                convert(nvarchar(1000),[Description])
                FROM [Categories]" 
            UpdateCommand="UPDATE [Categories] SET 
                           [CategoryName] = @CategoryName, 
                           [Description] = @Description 
                           WHERE [CategoryID] = @CategoryID">
            <DeleteParameters>
                <asp:Parameter Name="CategoryID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="CategoryName" Type="String" />
                <asp:Parameter Name="Description" Type="String" />
                <asp:Parameter Name="CategoryID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="CategoryName" Type="String" />
                <asp:Parameter Name="Description" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>
    
    </div>
    </form>
        <script>
            FixGrid(document.all.GridView1);
        </script>
</body>
</html>

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class AddUpdate : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void GridView1_RowAdding(object sender, 
                        GridViewUpdateEventArgs e)
    {
        if (e.RowIndex > 0)
            return; // RowIndex=0 is the row we want to insert
        System.Collections.Hashtable h = 
                    new System.Collections.Hashtable();

        foreach (System.Collections.DictionaryEntry x in e.NewValues)
        {
            h[x.Key] = x.Value;
        }
        // you now have the data to insert in a hashtable
        // get it into the database using your
        // usual Data Access Layer methods

    }
}

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Gary Dryden

Software Developer (Senior)

Canada

Member

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

Quick and Easy fix for insert record

drewster11

23:44 6 Jun '11

I noticed the insert query was not firing, so I made a quick and dirty mod of the original sub, Replace the gridview1_rowadding sub with the following, change sqlstr variable to your schema Smile | :)

Basicaly it just loops throught the hashtable and forms a query to execute. Sorry about the vb! add a label txtError to see whats going on...Hope this helps someone out!

Protected Sub GridView1_RowAdding(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
 
        If e.RowIndex > 0 Then
            Return
        End If
        ' RowIndex=0 is the row we want to insert
        Dim h As New System.Collections.Hashtable()
        Dim Item As DictionaryEntry
        For Each x As System.Collections.DictionaryEntry In e.NewValues
            h(x.Key) = x.Value
        Next
        Dim sqlstr As String = "INSERT INTO [mytable] (col3, col2, col1) values ("
        For Each Item In h
            sqlstr = sqlstr & "'" & Item.Value & "',"
            'debug txtError.Text = txtError.Text & "key:" & Item.Key & "value:" & Item.Value & " "
        Next
        'strip off last comma
        sqlstr = Left(sqlstr, Len(sqlstr) - 1)
        sqlstr = sqlstr & ")"
        'debug txtError.Text = sqlstr
        'execute sql
        Dim myConnection As New System.Data.SqlClient.SqlConnection(ConnStr)
 
        Try
            myConnection.Open()
            Dim command As New System.Data.SqlClient.SqlCommand(sqlstr, myConnection)
            command.ExecuteNonQuery()
            myConnection.Close()
            txtError.Text = "<br />Row has been added."
        Catch ex As Exception
            txtError.Text = "An error occured:" & ex.ToString() & " sql:" & sqlstr 
        Finally
            myConnection.Close()
        End Try
    End Sub

Works well for me, but problems arise with paging.

stapes

0:56 14 Apr '11

Thank you. This solution works for me in Internet Explorer only. For some reason, it does not work in Firefox, Edit remains Edit.

Anyway, I have added AllowPaging to my gridview, as there are currently nearly 70000 records in my database. On page 1, all is fine. But go to page 2, the top line is saying Add, but there is a record there.
I don't know what is best here. Do I switch off the Fixgrid routine if the GridView.PageIndex is higher than 0? In which case, the user can only insert if he is on GridView.PageIndex 0.
Firstly, I don't know how to do that.
Secondly, is this really the most elegant solution?

Does not work in Firefox

stapes

2:06 5 Apr '11

I am still trying to get this to work in Firefox. First, it did not like document.all. Replaced that line with: var grid=document.getElementById('ctl00_ContentPlaceHolder1_gvFieldReturns'); FixGrid(grid); Now, it says idGrid.firstChild.childNode[1] is undefined. This is the line in question: var Parts = idGrid.firstChild.childNodes[1].childNodes[0].innerHTML.split(">Edit<");
Sign In·View Thread·Permalink

Re: Does not work in Firefox

stapes

1:50 14 Apr '11

The method below corrects this:Corrected javascript for IE, FF, and Safari
Sign In·View Thread·Permalink

error !

sharathu7

22:59 30 Dec '10

Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified. Cooooooooooool !
Sign In·View Thread·Permalink

display blank grid in add/edit/update mode on UI

Mr. MAHESHKUMAR

0:28 6 Jan '10

I want to display blank GridView with one row and 5 columns on the UI for that the user can insert value into Database with that grid.
Sign In·View Thread·Permalink	1.50/5 (4 votes)

Eliminating GridView1_RowAdding

Roy Oliver

8:25 6 Aug '09

If you use an update stored procedure to be smart enough for updating and inserting a record, then you don't need to catch the GridView1_RowAdding event.
Sign In·View Thread·Permalink	1.00/5 (1 vote)

Re: Eliminating GridView1_RowAdding

Roy Oliver

10:20 6 Aug '09

If you're using the boring GridView control, this javascript may work a lil better for you.

function AlterFirstEditToAdd(GridViewClientID)
{
    if (GridViewClientID != null && GridViewClientID != '')
    {
        try
        {
            var gridView = null;
            var parts = null;
            var tmp = null;
            var html = null;
            var firstRowNode = 1;
            var isFirstPage = true;
            
            gridView = document.getElementById(GridViewClientID);
            
            //Get the actual first row of content.            
            html = gridView.firstChild.childNodes[0].childNodes[0].innerHTML;
            
            //Determine if we're not on the first page of data.
            if (html.indexOf('Page$1') > -1)
            {
                //The Add button should only show on the first page.
                isFirstPage = false;
            }
            
            if (isFirstPage == true)
            {
                //Attempt to grab the first row containing command links.
                html = gridView.firstChild.childNodes[firstRowNode].childNodes[0].innerHTML;
                
                //Determine if we actual have the first row. If not, then this is the header row, so move down one.
                if (html.indexOf('Edit$0') == -1 )
                {
                    firstRowNode = firstRowNode + 1;
                }
 
                //Begin to alter the command links for the first row.
                html = gridView.firstChild.childNodes[firstRowNode].childNodes[0].innerHTML;
                
                parts = html.split(">Edit<");
                
                tmp = parts.join(">Add<");
 
                parts = tmp.split(">Delete<");
 
                gridView.firstChild.childNodes[firstRowNode].childNodes[0].innerHTML = parts.join("><");
            }
        }
        catch (e)
        {
            //do nothing...
        }
    }
}

Gridview

ksarchana

1:47 3 Jan '09

how to update and add in gridview using stored procedures
Sign In·View Thread·Permalink	2.00/5 (2 votes)

Corrected javascript for IE, FF, and Safari

karen_webexc

5:12 30 Jul '08

The javascript is different in IE and FF/Safari.

         if (/Firefox[\/\s](\d+\.\d+)/.test(navigator.userAgent))
         {
            var Parts = idGrid.lastChild.childNodes[1].childNodes[1].innerHTML.split(">Edit<");
            var tmp = Parts.join(">Add<");
            Parts = tmp.split(">Delete<");
            idGrid.lastChild.childNodes[1].childNodes[1].innerHTML = Parts.join("><");
         }
         else if (/MSIE (\d+\.\d+);/.test(navigator.userAgent))
         {
            var Parts = idGrid.lastChild.childNodes[1].childNodes[0].innerHTML.split(">Edit<");
            var tmp = Parts.join(">Add<");
            Parts = tmp.split(">Delete<");
            idGrid.lastChild.childNodes[1].childNodes[0].innerHTML = Parts.join("><");
         }
         else if (/Safari[\/\s](\d+\.\d+)/.test(navigator.userAgent))
         {
            var Parts = idGrid.lastChild.childNodes[1].childNodes[1].innerHTML.split(">Edit<");
            var tmp = Parts.join(">Add<");
            Parts = tmp.split(">Delete<");
            idGrid.lastChild.childNodes[1].childNodes[1].innerHTML = Parts.join("><");
         }

Pls provide the code for inserting data in hashtable using DAL

Member 3159987

8:21 23 Jul '08

Hello, Can any one please provide the code for completing this task of adding the records, ie. code for inserting data in hashtable through data access layer. It would be a great help. Thanks, Anadi Singh
Sign In·View Thread·Permalink	3.00/5 (2 votes)

How to place data from SQLSERVER into gridview and then edit and delete data

waseemtalib

19:55 27 May '08

Hi, i am biggner to ASP.Net using C# please help me out .How to place data from SQLSERVER into gridview and then edit and delete data and also after deleting and updating data from gridview and store updated data again in database
Sign In·View Thread·Permalink	1.67/5 (3 votes)

Alternative insert/update code

Paul /)/+)

1:52 16 Oct '07

Nice submission, cheers.

I wanted the 'insert' line at the end of the list, rather than the first line, so instead of using the view_updating event used the datasource_updating instead.

An attempt to 'update' a record with a primary key of 0, causes an insert to be executed - the update parameters are copied into the insert parameter collection (minus the leading '@') so it is quite self contained.

There appears to be no way of chekcing how many parameters there are, so I have (rather naffly) just trapped the error when it tries to read past the end of the array.

I leave the update to fail quitely - however I guess it could be cancelled.

Hope its of use to someone.

Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating
If e.Command.Parameters.Item("@id").Value = 0 Then
SqlDataSource1.InsertParameters.Clear()
Dim i As Integer = 0
Try
Do
SqlDataSource1.InsertParameters.Add(e.Command.Parameters.Item(i).ParameterName.Remove(0, 1), e.Command.Parameters.Item(i).Value)
i += 1
Loop
Catch ex As Exception
End Try
SqlDataSource1.InsertParameters.Add("page", Request.QueryString("id"))
SqlDataSource1.Insert()
End If
End Sub

Paul /)/+)

Works great except for Paging & Sorting...

imxuf

13:46 17 Jul '07

I got the code to work, and its awesome! However, when I sort or page, the data in the grid replaces the first "Add" row with the appropriate data... Anyone have a fix for this?
Sign In·View Thread·Permalink	1.00/5 (1 vote)

Here is how you can make it work on multiple GridViews

HamidTheProgrammer

11:42 28 Mar '07

This worked very well for me. I used it in a page with seven GridViews that are not visible all the time, so here is what I did to make it work:

1- Repeated the lines below for as many data grids I wanted to apply the code to:
<script>
FixGrid(document.all.GridView1);
FixGrid(document.all.GridView2);
FixGrid(document.all.GridView...n);
</script>

2-Added an "if" statement to the FixGrid(idGrid) method to make sure it will not produce the error "firstChild is null or not an object" when one of the GridViews are not visible:

<script>
function FixGrid(idGrid)
{
// content looks like:
//"Edit
// Delete"
// replace Edit with Add, remove Delete
if (idGrid)
{
var Parts = idGrid.firstChild.childNodes[1].childNodes[0].innerHTML.split(">Edit<");
var tmp = Parts.join(">Add<");
Parts = tmp.split(">Delete<");
Parts = tmp.split(">Select<");
idGrid.firstChild.childNodes[1].childNodes[0].innerHTML = Parts.join("><");
}
}
</script>

HamidTheProgrammer

Does't Work

Member #3760552

3:40 21 Mar '07

I tried to use your code exactly as it was written, and when I enter a new record in line 0 and press update, it goes to the function of ROWADDING do what it suppose to do but nothing is entered into the table. Have any idea why? THanks mohandasclt@gmail.com
Sign In·View Thread·Permalink	2.00/5 (2 votes)

Another method to change to ADD link - Use RowDataBound [modified]

dbernett

7:15 15 Nov '06

Had problems calling the Javascript and Master Page.   I did it this way in the code.
My GridView name is grdUser and my ID for the ContentPlaceHolder is "CM"   Also be careful on ctl00.   It might be different based on a web config setting but that's a whole other topic.   Just mouse over your Edits and see how they are named.

   Protected Sub grdUser_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)

If e.Row.RowIndex = 0 And e.Row.RowState <> DataControlRowState.Edit Then

e.Row.Cells(0).Text = "<a href=""javascript:__doPostBack('ctl00$CM$grdUser','Edit$0')"">Add</a>"

End If

End Sub

And this is my SLOP code for inserting

         Dim i As Integer = 0
            For Each x As System.Collections.DictionaryEntry In e.NewValues
                  'h(x.Key) = x.Value

                  If i = 0 Then Title = x.Value
                  If i = 1 Then FirstName = x.Value
                  If i = 2 Then LastName = x.Value
                  If i = 3 Then Email = x.Value
                  If i = 4 Then Admin = x.Value
                  If i = 5 Then Active = x.Value

                  i += 1
            Next

            If Title <> "" And FirstName <> "" And LastName <> "" And Email <> "" Then
                  With SqlDataSource1
                        .InsertParameters("usrTitle").DefaultValue = Title
                        .InsertParameters("usrFirstname").DefaultValue = FirstName
                        .InsertParameters("usrLastName").DefaultValue = LastName
                        .InsertParameters("usrEmail").DefaultValue = Email
                        .InsertParameters("TrainingAdmin").DefaultValue = Admin
                        .InsertParameters("Active").DefaultValue = Active
                        .Insert()
                  End With
            End If

-- modified at 12:22 Wednesday 15th November, 2006

Re: Another method to change to ADD link - Use RowDataBound

ccshine

13:42 5 Dec '06

I'm using an image instead of text for Edit, Cancel, etc. and ran into some problems using the Javascript version. After the row was added the Edit image wasn't updated to the Add image. I'm using Atlas which I don't believe would cause the new issue, but wanted to provide as much info as possible. I'm now attempting to perform the image switch in code behind. The first row image is replaced successfully but when I click on it to enter Edit mode I receive an error - Invalid postback or callback argument. Blah, blah, blah. Can someone see what I may be doing wrong?

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

        If e.Row.RowIndex = 0 And e.Row.RowState <> DataControlRowState.Edit Then

            e.Row.Cells(1).Text = "<input type=""image"" src=""Images/add.gif"" alt=""Add""  önclick=""javascript:__doPostBack('ctl00$ContentPlaceHolder1$grdUsers','Edit$0')"" style=""border-width:0px;"" />"

        End If

 

    End Sub

But, if we use sorting for the same grid

kvPriya

19:41 5 Oct '06

Hi, It's very good. But, how will be the situation, if we use sorting and paging for the same grid? If we sort the grid by a column, the empty row at the first line which we use for adding the new one will be populated with some other record.How can we overcome that?? -Priya
Sign In·View Thread·Permalink	2.00/5 (1 vote)

I fix the javascript for the linkbutton names. Check it out!

kampfer!

10:52 24 Sep '06

Hey, for those of you who had the same problem with changing the name for the linkbuttons here is the fix: var Parts = idGrid.childNodes[1].childNodes[1].innerHTML.split(">Edit<"); var tmp = Parts.join(">Add<"); Parts = tmp.split(">Delete<"); idGrid.childNodes[1].childNodes[1].innerHTML = Parts.join("><");
Sign In·View Thread·Permalink

doesnt work with strongly typed datasets

xgnitesh

21:52 13 Sep '06

Hi I tried this with SqlDataSource and it worked. When I try this with strongly typed dataset as datasource, it does not work. Any special setting to be done in this case? Thanks for any help. Nitesh
Sign In·View Thread·Permalink	2.00/5 (1 vote)

Re: doesnt work with strongly typed datasets [modified]

rbm_the_spitfire

23:09 2 Oct '06

I tried to add this to my grid, but I got an error. I used to insert the data in the SqlDataSource the following code.

-------------------------------------------------
// Insert parameter TestGroupDescription
if (h["TestGroupDescription"] == null)
{
h["TestGroupDescription"] = "";
}
SqlDataSource1.InsertParameters["@TestGroupDescription"] = new Parameter(h["TestGroupDescription"].ToString());

// Insert parameter TestGroupPriority
SqlDataSource1.InsertParameters["@TestGroupPriority"] = new Parameter(h["TestGroupPriority"].ToString());

// insert values in the database
SqlDataSource1.Insert();
-------------------------------------------------
On the Insert() command I get the error that TestGroupDescription is inserted as a NULL value (and my database does not allow NULL values). Can you help me pass this exception? Thanks in advantage.

Greetings,

RBM the Spitfire

-- modified at 4:26 Tuesday 3rd October, 2006

Good, but the Edit,Delete are not being replaced by Add

kvPriya

19:38 8 Sep '06

It's working well.But, the at the new row, Edit,Delete are not being replaced by Add. In FixGrid function, I've tried using the same code as it is in...also using document.getElementById(.....) ... but of no use. Please help me,if anyone have worked well with this. -Priya
Sign In·View Thread·Permalink	2.00/5 (1 vote)

Re: Good, but the Edit,Delete are not being replaced by Add

zwitterion

8:55 14 Sep '06

Your problem is probably that the cell with the Edit Delete links is not being referenced correctly in that little javascript snippet. I know "idGrid.firstChild.childNodes[1].childNodes[0]" wasn't it for me on the first try but you'll have to do some hunting around to figure out what it is exactly (firefox + DOM Browser make it a cinch). I think the key was changing 'firstChild' to 'lastChild' or something like that.

Re: Good, but the Edit,Delete are not being replaced by Add

Vincent D'Souza

6:55 25 Oct '06

Why don't you use the prerender event of the grid and iterate through the rows of the grid and its cells and add or remove controls. I haven't tried for this one but have done lot of manipulation for other controls. Try it and let us know... --Vincent D'souza
Sign In·View Thread·Permalink	3.50/5 (2 votes)