Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 790
  • Last Modified:

How do I go from FormView insert mode to FormView edit mode (get identity back from sql)

This is a master/detail situation.  I need to be able to open a page with a formview in insert mode, enter data, click the insert button, then retrieve the identity of the just added record, and change to edit mode so I can then add records in the detail grid that is also on that page.

I have read here that I must have the data source use a stored procedure that has a "Select Scope_Identity()" after the insert.  I understand how to do that. My insert is working fine.

***************************
I also read that in the FormView1_ItemInserted event I need to retrieve the Identity parameter.  THIS IS WHAT I NEED HELP WITH.  I cannot figure out the C# code to ask the sqldatasource for the identity it just asked for in the stored proc.
**************************

If I am on the right path here, please help with the c# code to retrieve the id.  If not, and there is a better way to do this, let me know.

Thanks, Mark
0
mastertrac
Asked:
mastertrac
  • 5
  • 4
  • 3
1 Solution
 
p_davisCommented:
if you are using a stored procedure you will have to tack on the the call for the identity as a nested select. "first insert sql; select scope_identity() as newID" and newId will be returned and can be captured in a datatable/dataset. i know that with our db we have to use select @@identity; for the most recent pk of the insert.

it has to be done with the insert statement otherwise the identity goes out of scope.
0
 
mastertracAuthor Commented:
Thanks.  I did not realize I needed the "as newID" part in the sql.  That said, I still do not get the part about how to capture it in a dataset.  I read something that lead me to believe it needed to happen in the ItemInserted event.  If that is the place to retrieve it, what is the CODE to retrieve it? I know how to create a dataset, but my brain is just not getting the part on how to put newID into the dataset.

Thanks again
0
 
p_davisCommented:
datatable dt = new datatable();

dt = <whatever code you use to run your stored procedure with the select identity tacked on>;
DataRow row = dt.rows[0];

row["newId"];(will be your new id).
i just went ahead and used the datatable as you would've had to traversed down to it anyway.
if your id is an int you will probably have to use
Convert.ToInt32(row["newID"]);
if string
row["newId"].ToString();
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mastertracAuthor Commented:
The thing that is still confusing me is that I thought the formview insert button runs the stored procedure in conjunction with the sqldatasource that is attached to it.  Does this mean I am not supposed to have code on the insert tab of the datasource and just execute the stored proc in the ItemInserted event?
0
 
p_davisCommented:
i am not sure of the best practices with your current setup. -- could you post some code showing how you do your current insert.
0
 
strickddCommented:
What you need to do is create and OnItemInserted event like so:

protected void FormViewDataSourceObj_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
      {
            int ID = e.OutputParameters["ID"];

                //Set the hidden field/control that the SELECT method for the edit form view uses to ID

               FormView.ChangeMode(Edit);
               FormView.DataBind();
      }
0
 
mastertracAuthor Commented:
strickdd: I am using an sqldatasource and a formview.  The only way I know to wire an event is to go to the lighting bolt for either the sqlDataSource or the FormView1 control.  I have wired both the "ItemInserted" event on the formview and the "Inserted" event of the sqldatasource.  Neither of these provide the "ObjectDataSourceStatusEventArgs" you mention above and e.OutputParameters is not available in the intellesense popup for either.  

Where do I wire the above event?

P_Davis: my aspx page code is as follows:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="userGroupForm.aspx.cs" Inherits="userGroupForm" StylesheetTheme="Blue" %>

<%@ Register Src="pbtHeader.ascx" TagName="pbtHeader" TagPrefix="uc1" %>

<!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>User Group Members</title>
</head>
<body bottommargin="5" leftmargin="5" rightmargin="5" topmargin="5">
    <form id="form1" runat="server">
    <div>
        <uc1:pbtHeader ID="PbtHeader2" runat="server" />
        <table style="z-index: 100; left: 32px; position: absolute; top: 73px">
            <tr>
                <td class="bannerMain" style="width: 100px">
                    &nbsp;Edit User Group
                    <asp:ImageButton ID="ImageButton3" runat="server" ImageUrl="~/Img/help.gif" OnClick="ImageButton1_Click"
                        Style="z-index: 100; left: 248px; position: absolute; top: 7px" />
                </td>
            </tr>
            <tr>
                <td class="formBody">
                    <table>
                        <tr>
                            <td>
                                <asp:FormView ID="FormView1" runat="server" DataKeyNames="GroupID" DataSourceID="groupData"
                                    DefaultMode="Edit" OnItemUpdated="FormView1_ItemUpdated" OnItemInserted="FormView1_ItemInserted">
                                    <EditItemTemplate>
                                        <table>
                                            <tr>
                                                <td align="right">
                                                    Description:&nbsp;
                                                </td>
                                                <td align="left">
                                                    <asp:TextBox ID="DescriptionTextBox" runat="server" Text='<%# Bind("GroupName") %>'></asp:TextBox></td>
                                            </tr>
                                            <tr>
                                                <td>
                                                </td>
                                                <td align="left">
                                                    <asp:Button ID="saveButton" runat="server" CommandName="Update" Text="Save Group" />
                                                    <asp:Button ID="cancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
                                                        OnClick="cancelButton_Click" Text="Close" /></td>
                                            </tr>
                                        </table>
                                    </EditItemTemplate>
                                    <InsertItemTemplate><table>
                                        <tr>
                                            <td align="right">
                                                Description:&nbsp;
                                            </td>
                                            <td align="left">
                                                <asp:TextBox ID="DescriptionTextBox" runat="server" Text='<%# Bind("GroupName") %>'></asp:TextBox></td>
                                        </tr>
                                        <tr>
                                            <td>
                                            </td>
                                            <td align="left">
                                                <asp:Button ID="saveButton" runat="server" CommandName="Insert" Text="Insert Group" />
                                                <asp:Button ID="cancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
                                                        OnClick="cancelButton_Click" Text="Close" /></td>
                                        </tr>
                                    </table>
                                    </InsertItemTemplate>
                                    <ItemTemplate>
                                        GroupID:
                                        <asp:Label ID="GroupIDLabel" runat="server" Text='<%# Eval("GroupID") %>'></asp:Label><br />
                                        CompanyID:
                                        <asp:Label ID="CompanyIDLabel" runat="server" Text='<%# Bind("CompanyID") %>'></asp:Label><br />
                                        GroupName:
                                        <asp:Label ID="GroupNameLabel" runat="server" Text='<%# Bind("GroupName") %>'></asp:Label><br />
                                        <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"
                                            Text="Edit">
                                        </asp:LinkButton>
                                        <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"
                                            Text="Delete">
                                        </asp:LinkButton>
                                        <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
                                            Text="New">
                                        </asp:LinkButton>
                                    </ItemTemplate>
                                </asp:FormView>
                                </td>
                        </tr>
                        <tr>
                            <td align="left">
                                <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
                                    DataKeyNames="GroupMemberID" DataSourceID="groupMemberData">
                                    <Columns>
                                        <asp:BoundField DataField="uName" HeaderText="Member Name" ReadOnly="True" SortExpression="uName" >
                                            <ItemStyle HorizontalAlign="Left" />
                                            <HeaderStyle HorizontalAlign="Left" Width="180px" />
                                        </asp:BoundField>
                                        <asp:TemplateField>
                                            <ItemTemplate>
                                                <asp:LinkButton ID="delete" Runat="server"
                                                    OnClientClick="return confirm('Are you sure you want to Delete?');" CommandName="Delete">Delete
                                                </asp:LinkButton>
                                            </ItemTemplate>
                                            <ItemStyle Width="20px" />
                                        </asp:TemplateField>
                                    </Columns>
                                    <EmptyDataTemplate>
                                        <br />
                                        &nbsp;&nbsp; You have no group members to display.<br />
                                        <br />
                                    </EmptyDataTemplate>
                                </asp:GridView>
                                <table style="width: 259px">
                                    <tr>
                                        <td align="right">
                                <asp:ImageButton ID="ImageButton2" runat="server" ImageUrl="~/Img/AddRecord.gif"
                                    OnClick="LinkButton1_Click" /><asp:LinkButton ID="LinkButton1" runat="server" OnClick="LinkButton1_Click">Add New Group Member</asp:LinkButton></td>
                                    </tr>
                                </table>
                                &nbsp;&nbsp;</td>
                        </tr>
                    </table>
                </td>
            </tr>
        </table>
        <asp:SqlDataSource ID="groupData" runat="server" ConnectionString="<%$ ConnectionStrings:xMissionMSsql %>"
            DeleteCommand="DELETE FROM [Groups] WHERE [GroupID] = @GroupID"
            SelectCommand="SELECT * FROM [Groups] WHERE ([GroupID] = @GroupID)"
            UpdateCommand="UPDATE [Groups] SET [GroupName] = @GroupName WHERE [GroupID] = @GroupID"
            InsertCommand="Groups_Add" InsertCommandType="StoredProcedure" OnInserted="groupData_Inserted">
            <DeleteParameters>
                <asp:SessionParameter Name="GroupID" SessionField="passID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="GroupName" Type="String" />
                <asp:SessionParameter Name="GroupID" SessionField="passID" Type="Int32" />
            </UpdateParameters>
            <SelectParameters>
                <asp:SessionParameter Name="GroupID" SessionField="passID" Type="Int32" />
            </SelectParameters>
            <InsertParameters>
                <asp:SessionParameter Name="CompanyID" SessionField="UserCompanyID" />
                <asp:Parameter Name="GroupName" />
            </InsertParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="groupMemberData" runat="server" ConnectionString="<%$ ConnectionStrings:xMissionMSsql %>"
            SelectCommand="SELECT u.LastName + ', ' + u.FirstName AS uName, dbo.GroupMembers.GroupMemberID, dbo.GroupMembers.GroupID, dbo.GroupMembers.UserID FROM dbo.GroupMembers INNER JOIN dbo.Users AS u ON u.UserID = dbo.GroupMembers.UserID WHERE (dbo.GroupMembers.GroupID = @GroupID)"
            UpdateCommand="UPDATE [GroupMembers] SET [GroupID] = @GroupID, [UserID] = @UserID WHERE [GroupMemberID] = @GroupMemberID"
            DeleteCommand="DELETE FROM [GroupMembers] WHERE [GroupMemberID] = @GroupMemberID">
            <UpdateParameters>
                <asp:Parameter Name="GroupID" Type="Int32" />
                <asp:Parameter Name="UserID" Type="Int32" />
                <asp:Parameter Name="GroupMemberID" Type="Int32" />
            </UpdateParameters>
            <SelectParameters>
                <asp:SessionParameter Name="GroupID" SessionField="passID" Type="Int32" />
            </SelectParameters>
            <DeleteParameters>
                <asp:Parameter Name="GroupMemberID" Type="Int32" />
            </DeleteParameters>
        </asp:SqlDataSource>
   
    </div>
    </form>
</body>
</html>

Thanks guys.  I know we are close here.
0
 
p_davisCommented:
sorry mastertrac, not quite sure how to implement in asp. i generally do winforms and have had minimal exposure to asp. i know that the method i suggested will work but not sure how you would implement with this.

good luck (hopefully other experts pick up on this thread).
0
 
strickddCommented:
I'm sorry, the code I gave you was for an ObjectDataSource and not a SqlDataSource. You should just have to change the event args to the proper type.
0
 
mastertracAuthor Commented:
Sorry for being sounding so dumb.  Although I have programmed for 20+ yrs I only picked up visual studio about 6 months ago.

I have done as you suggested and have wired two different events:
    protected void groupData_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {  
    }
    protected void FormView1_ItemInserted(object sender, FormViewInsertedEventArgs e)
    {  
    }

Neither of these have "ObjectDataSourceStatusEventArgs" as your code above.  If I put that in either of these event declarations inplace of the args that are there, it wont compile.  But neither of the above events gives me the".OutputParameters" option that you mention above when I type e. (dot).  the .OutPutParameters method is the key to this, but I cannot figure out how to gain access to it.
0
 
strickddCommented:
Try:

e.Command.Parameters
0
 
mastertracAuthor Commented:
strickdd:  Thanks a bunch.  Your help has led me to the solution.  Let me recap here for those who may read this later.  

There are three basic elements to accomplish this, the sql stored procedure, the datasource, and the "Inserted"event of the datasource.

First, you make a stored procedure in the sql--something like the following. NOTE the third parameter is an "Output" parameter:

ALTER PROCEDURE [dbo].[Groups_Add]
      -- Add the parameters for the stored procedure here
      @CompanyID int = 0,
      @GroupName Varchar(50),
      @NewGroupID int output
AS
BEGIN
      SET NOCOUNT ON;
      INSERT into Groups (CompanyID, GroupName) VALUES (@CompanyID, @GroupName);
      set @NewGroupID = SCOPE_IDENTITY();
END

The second element is SqlDataSource in you aspx source.  NOTE the "Direction" statement on the "NewGroupID" parameter:

        <asp:SqlDataSource ID="groupData" runat="server" ConnectionString="..."
            InsertCommand="Groups_Add" InsertCommandType="StoredProcedure" OnInserted="groupData_Inserted">
            <InsertParameters>
                <asp:SessionParameter Name="CompanyID" SessionField="UserCompanyID" />
                <asp:Parameter Name="GroupName" />
                <asp:Parameter Direction="Output" Name="NewGroupID" Type="Int32" />
            </InsertParameters>
        </asp:SqlDataSource>

The third element involves wiring the "Inserted" event on the SqlDataAdapter.  That code should be something like the following:

    protected void groupData_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        Session["passID"] = e.Command.Parameters[2].Value.ToString();
        Session["formMode"] = "edit";
        Response.Redirect("userGroupForm.aspx", false);
    }

Here I use a session variable to store the Identity that is being returned.  The parameters are 0 based so in my case a 2 is used to retrieve the value.  I also use a session variable to keep track of the mode I wish the page to be set to and then set the mode in the page load event.  The last thing I do above is reload the page.

The above may not be the best way to accomplish everything, but it seems to get the job done for me.

Thanks for all your help everyone!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now