After inserting a record in a DetailsView control, set the master GridView control to the newly inserted record’s ID (identity)

The problem

I’m learning how to build ASP.NET websites in C# using Microsoft Visual Studio 2005. Everything about this is new to me. I have done a lot of work building HTML websites, quite a bit of javascript, and a little php and MySQL development. But now everything I know amounts to nothing as I try to learn a new development environment (Visual Studio), and two new programming languages (C# and ASP.NET). It’s an interesting process, and I rather enjoy what I’m learning, but I wanted to make it clear right up front that the advice included in this post may not be the best advice you can find: I still have a lot to learn!

That being said, let me describe one of the first problems I encountered in my new programming scenario:

I am building a website to allow users to add, edit, and delete items in a list of events. I am using an ASP.NET/Visual Studio MultiView control to manage the process flow for selecting and editing the events. The first view that the user sees is a GridView control displaying the complete list of events in the database.


Image 1: The GridView control listing the events


From this GridView, the user will select an event to view or edit. When they select an event, they are taken to the next View in the MultiView control where they will see a DetailsView control with the specific details for that event.

Image 2: The DetailsView for a particular event


For my application, I needed to have a third View control with additional details. This was not a problem until I tried to add a new item to my database. As soon as I added the new item, I realized that the GridView control was either reset to select nothing or else it kept its original selection rather than automatically selecting the new item. What’s worse, since my GridView’s data source is sorted, the GridView would often have the same index (relative position) selected, but it could be a completely different item since the actual values at each index had changed!

To avoid this problem, I needed to find some way for the application to insert a new item into my SQL database, return the Identity (ID) value for that new record, refresh the data displayed in my GridView List, and then automatically select the row in my GridView list that contains the new record. Here’s how I did it:

Step 1: Returning the primary key (identity) of the new record

The most important piece of this, it seemed to me, was getting the web page to actually record the value of the new identity column. I am used to programming with PHP and MySQL, and there is a handy built-in function that allows you to do this easily. You simply call a MySQL INSERT operation and then call the function mysql_insert_id(), which returns the Identity (ID) of the record inserted. There is no such built-in function in SQL, ASP.NET, or Visual Studio. So I had to build my own method to do this.

First, I wrote a Stored Procedure in SQL Server Management Studio that would take the values from my website, insert them into a new record in the SQL database, and return the value of the Identity of the newly-inserted record. Here’s the stored procedure I used:

SQL Code Sample: Stored Procedure that inserts a new record and returns the Identity value

    CREATE PROCEDURE [dbo].[spAddEventAndReturnEventIDValue] (
	-- Add the parameters for the stored procedure here
	@startDate DateTime,
	@endDate DateTime,
	@title varchar(500),
	@description varchar(1000),
	@location varchar(500),
	@NewID int = NULL OUTPUT
    )
    AS
    BEGIN
             -- Insert SQL Command for this Procedure
	INSERT INTO [tblCalendarEvent] ([startDate], [endDate], [title], 
		[description], [location])
	VALUES (@startDate, @endDate, @title, 
		@description, @location)
	
	-- Read the just-inserted Event ID into @NewID
	SET @NewID = SCOPE_IDENTITY()

	--Return the @NewID variable
	RETURN @NewID
	
    END

Once you have created your stored procedure, it is a fairly simple matter to call it. In Visual Studio, select the SQL data source control and change the properties such that the InsertCommandType is “StoredProcedure” and in the InsertQuery Query Builder window (Where you would normally type the SQL INSERT query itself), type in the name of the stored procedure (In this case, spAddEventAndReturnEventIDValue).

Step 2: Finding and selecting the new record in your GridView control

Simply calling a stored procedure that returns a value is not enough to do the job, of course. The next thing you have to do is take that returned value and make it the selected value for the GridView control.

Used to dealing with DropDown lists, I thought I could just assign the GridView’s value property to the returned value using a statement something like this:

GridView1.SelectedValue = command.Parameters[“@NewID”].Value;

Unfortunately, it is not that easy. When I tried to do this, I got the following error message:

Compiler Error Message: CS0200: Property or indexer ‘System.Web.UI.WebControls.GridView.SelectedValue’ cannot be assigned to — it is read only

So much for doing things the easy way. The only thing left to do, as far as I could figure, would be to write a code that cycles through the entire GridView list until it finds a row that matches the value returned by my stored procedure (The Identity value of the newly-inserted record). In my case, the GridView was set to allow paging, so I had to not only identify the correct index of the new record, but also the right page.

I don’t know if this is the best or the only way to do things, but here’s the code that I came up with:

C# Code Sample: Forcing a GridView to select the ID of a record inserted by a bound DetailsView

using System;
using System.Data.Common;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class PageName : System.Web.UI.Page
{
    protected void On_Inserted(
        object sender, SqlDataSourceStatusEventArgs e)
     {
        DbCommand command = e.Command;

        //Pass the SQL parameter returned to a C# string
        String NewID = 
            command.Parameters["@NewID"].Value.ToString();

        // Finding the data that was inserted:

            //Set the default variables
                //No row should be selected
                int sel = -1;
                //No page should be selected
                int pag = -1;

            for (int i = 0; i < GridView1.PageCount; i++)
            {
                //Go to page "i"
                GridView1.PageIndex = i;
                //Refresh data (Not sure if this is required)
                GridView1.DataBind();

                //Set the default row index to 0 (first row)
                int row = 0; 

                //Search through DataKeys for each row
                foreach (DataKey dk in GridView1.DataKeys )
                {
                    if (dk.Value.ToString().ToLower() == 
                         NewID.ToLower()
                        )
                    {
                        //Save the index of the matching row
                        sel = row;
                        //Save the index of the matching page
                        pag = i;
                    }
                    
                    //Move to the next row
                    row = row + 1;
                }
            }

        //Bind the GridView to the new data:
            //Refresh the GridView data source
            SQLDataSource1.DataBind();

            //Refresh Databinding of the GridView
            GridView1.DataBind();

            //Move to the correct page in the GridView
            GridView1.PageIndex = pag;

            //Select the row containing your data
            GridView1.SelectedIndex = sel;

            //Double-check DetailsView is on the right page
                //This is probably not required...
            DetailsView1.PageIndex = 0;

            //Change DetailsView to ReadOnly mode
            DetailsView1.ChangeMode(
                DetailsViewMode.ReadOnly);

            //Refresh the DetailsView to ensure it shows details
            //for the correct record
            DetailsView1.DataBind();
     }
}

The final piece that is required is to make sure that the function coded above actually runs when a new record is inserted. To do that, you’ll have to add some codes to your ASPX page manually. First, you need to find the SQL data source control that calls your stored procedure. It should look something like this:

<asp:SqlDataSource ID=”SQLDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:ConnectionString1 %>InsertCommand=”spAddEventAndReturnEventIDValue” InsertCommandType=”StoredProcedure” >

Just add the call to the new On_Inserted method by adding OnInserted=”On_Inserted” somewhere in the data source’s <asp:SqlDataSource> tag, and your application should work. The new code for SqlDataSource1 should look like this:

<asp:SqlDataSource ID=”SQLDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:ConnectionString1 %>InsertCommand=”spAddEventAndReturnEventIDValue” InsertCommandType=”StoredProcedure” OnInserted=”On_Inserted”>

Now, when you insert a new record via your DetailsView control, the new information will be stored in the SQL Server database, SQL Server will return the value of the Identity column for the new row, and your page will automatically set the GridView control to select the newly-inserted item. This is so much better than having to go back to the beginning, search for and select the record you just inserted, and page through the rest of the MultiView until you get to the section you need to work on!


That’s all for today…now get out there and Write the Web!

Leave a Reply

Your email address will not be published. Required fields are marked *