Corey Roth [MVP]

Look! It's a post that isn't about SharePoint!  Today, I want to talk about some of the issues I have run into when trying to update data using a GridView when bound to a LinqDataSource.  This data source has an EnableUpdate property, but unfortunately there is more to it than just that.  It works pretty well, but there are a few things that you have to do otherwise you will get the following error.

The Entity is not of the correct type.

In today's example, I required a stored procedure to do the update because I was updating multiple tables behind the scenes.  Although LINQ can do this, the complexity of the update was simpler if I did it via stored procedure.  The data I am displaying in my GridView is based off a database view.  The first thing to do is drag the View into your LINQ to SQL class.  You need to make sure your view includes a primary key.  You then need to select the column and mark it as a Primary Key in the LINQ to SQL designer.  After you have your view defined, drag the stored procedure for your update into the designer.  Then on the properties of your view's data class, you can set a default method for Insert, Update, and Delete.  In this case I am only worrying about Update.  If you click on Update, it will bring up a Configure Behavior window which will allow give you two choices.  Use Runtime will dynamically generate the CRUD logic at runtime.  In our case though, we will choose Customize and then pick our stored procedure out of the list.

After you have selected an update method, your LINQ to SQL class is good to go.  You are now ready to build your page.  Start by dragging a GridView and LinqDataSource onto your page.  On your LinqDataSource, set the EnableUpdate property to true.  Use the wizard to bind your LINQ to SQL database.   Pick the Entity named after your view but when configuring columns be sure and choose all columns (*).  If you select individual columns, it populates the Select property on the LinqDataSource and you will not be able to make updates.  At this point, you can bind your GridView to your LinqDataSource.  If you have followed all of the steps so far, the Enable Editing option should be available for you to check.  Check it and then the last thing you have to do is set the DataKeyNames property on the GridView.  Set it to the name of your primary key and you should be ready to make updates using  a GridView.

I always find that I spend more time than I care to setting up GridViews.  Hopefully this will help if you ever need to use LINQ to SQL with a GridView.  Also don't forget Tulsa TechFest this week where I will be talking about Code Access Security in SharePoint.