I would like to change the default method of inserting data to make it use a stored procedure, plus I would like to get a return value from this stored procedure returned to the calling code.

Scenario is that on insert the default behaviour is to go to the list view, but I would like to get from the insert the autogenerated primary key and use this to re-direct to a different form showing the newly inserted record.

I see that I can create a partial class for the DataContext class and then create my own InsertTable method which will be called on the insert action, but this is a sub so it is not clear how to return a value to the Insert.aspx code behind class for processing.

I can override the DetailsView ItemInserting method, but to use this I would need to set the cancel property to true to prevent double insertion, and then the validation behaviour does not work.

So how would this be done?

Thanks

See LINQ to SQL (Part 7 - Updating our Database using Stored Procedures) and Stored Procedures (LINQ to SQL)

Steve,

Thanks for the reply, the article was very helpful, I can now use the stored procedure to insert the information with the returned value added to the passed in instance. However it is still not clear how to access this from the DetailsView_ItemInserted method.

In the DataContextClass the generated code is:

Private Sub InsertArticle(ByVal obj As Article)
  Dim p1 As System.Nullable(Of Integer) = obj.ArticleId
  Me.fgp_InsertArticle(p1, obj.HTMLPageName, obj.Title, obj.Summary, obj.PageContent)
  obj.ArticleId = p1.GetValueOrDefault
End Sub

this is called correctly when I click the Insert button on the detailsview object and the correct Id is returned to the InsertArticle routine but in the DetailsView_ItemInserted how to I get access to this value- looping through the values collection of the DetailsViewCommandEventArgs displays all the values of the fields as entered by the user but not this returned value. Even if I add a field for the ArticleID to the DetailsView it does not get the value from the InsertArticle routine. Generally how would I access the instance of the DataContextClass used by the DetailsView to perform this insert?

Thanks

You should now get an entity with the PK value in it now in the DetailsView_ItemInserted method.

Steve,

I'm not sure how I would reference it, but I have found that in DetailsDataSource_Inserted the eventargs contains a Result object which is the returned value, which can be cast to the object type and contains the returned ID.

Thanks for your help!

Sorry I was forgetting (I can't converet VB=C# in my head) you need to use the LinqDataSource's Inserted event see: 

Protected Sub DetailsDataSource_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceStatusEventArgs) Handles DetailsDataSource.Inserted
    If ((e.Exception Is Nothing) _
                OrElse e.ExceptionHandled) Then
        Response.Redirect(table.GetActionPath(PageAction.Details, e.Result))
    End If
End Sub

Does exactly what it says on the tin!