How can I get the primary key value of a record after inserting with DetailsView

Hi,

I would like to be able to get the primary key value of a record after it has been inserted into a table using DetailsView.  I am using the following event handler

protected void MissionaryDetails_ItemInserted(object sender, DetailsViewInsertedEventArgs e)

I have searched google and MSDN and can not find a solution that actually works for me.  Any suggestions would be greatly appreciated.

I don't know if this will help, but the SqlDataSource is configured as follows:

<asp:SqlDataSource ID="MissionaryDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:WBF_DataConnectionString1 %>" DeleteCommand="DELETE FROM [Missionary] WHERE [MissionaryID] = @MissionaryID" InsertCommand="INSERT INTO [Missionary] ([FirstName], [LastName], [MissionFieldID], [SpouseFirstName], [ApproveDate], [Email], [StreetAddress], [City], [State], [Zip], [CellPhone]) VALUES (@FirstName, @LastName, @MissionFieldID, @SpouseFirstName, @ApproveDate, @Email, @StreetAddress, @City, @State, @Zip, @CellPhone)" SelectCommand="SELECT [MissionaryID], [FirstName], [LastName], [MissionFieldID], [SpouseFirstName], [ApproveDate], [Email], [StreetAddress], [City], [State], [Zip], [CellPhone] FROM [Missionary] WHERE ([MissionaryID] = @MissionaryID)" UpdateCommand="UPDATE [Missionary] SET [FirstName] = @FirstName, [LastName] = @LastName, [MissionFieldID] = @MissionFieldID, [SpouseFirstName] = @SpouseFirstName, [ApproveDate] = @ApproveDate, [Email] = @Email, [StreetAddress] = @StreetAddress, [City] = @City, [State] = @State, [Zip] = @Zip, [CellPhone] = @CellPhone WHERE [MissionaryID] = @MissionaryID">
        <DeleteParameters>
            <asp:Parameter Name="MissionaryID" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="FirstName" Type="String" />
            <asp:Parameter Name="LastName" Type="String" />
            <asp:Parameter Name="MissionFieldID" Type="Int32" />
            <asp:Parameter Name="SpouseFirstName" Type="String" />
            <asp:Parameter DbType="Date" Name="ApproveDate" />
            <asp:Parameter Name="Email" Type="String" />
            <asp:Parameter Name="StreetAddress" Type="String" />
            <asp:Parameter Name="City" Type="String" />
            <asp:Parameter Name="State" Type="String" />
            <asp:Parameter Name="Zip" Type="String" />
            <asp:Parameter Name="CellPhone" Type="String" />
        </InsertParameters>
        <SelectParameters>
            <asp:QueryStringParameter Name="MissionaryID" QueryStringField="MissionaryID" Type="Int32" />
        </SelectParameters>
        <UpdateParameters>
            <asp:Parameter Name="FirstName" Type="String" />
            <asp:Parameter Name="LastName" Type="String" />
            <asp:Parameter Name="MissionFieldID" Type="Int32" />
            <asp:Parameter Name="SpouseFirstName" Type="String" />
            <asp:Parameter DbType="Date" Name="ApproveDate" />
            <asp:Parameter Name="Email" Type="String" />
            <asp:Parameter Name="StreetAddress" Type="String" />
            <asp:Parameter Name="City" Type="String" />
            <asp:Parameter Name="State" Type="String" />
            <asp:Parameter Name="Zip" Type="String" />
            <asp:Parameter Name="CellPhone" Type="String" />
            <asp:Parameter Name="MissionaryID" Type="Int32" />
        </UpdateParameters>
    </asp:SqlDataSource>

Open in new window


The primary key is autogenerated by the db server.  So I notice that there is not a parameter in the insert parameters for the record.  Should I have one to return the primary key?  If so, how do I write it?
DLAtkinsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anarki_jimbelSenior DeveloperCommented:
0
DLAtkinsonAuthor Commented:
I already looked at this (as I said, I spent quite a bit of time on google trying to find a solution myself).  The problem is that

protected void DetailsView1_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
{

e.ReturnValue does not exists, so that

int intKey;
int.TryParse(e.ReturnValue, out intKey);

can't work.  Now I could be missing something that causes DetailsViewInsertedEventArgs not to have a property ReturnValue.  If I am, this would be what I need help on.
0
DLAtkinsonAuthor Commented:
I figured it out.  I had to rewrite the insert command above and add a parameter to my insert parameters.

<asp:SqlDataSource ID="MissionaryDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:WBF_DataConnectionString1 %>" DeleteCommand="DELETE FROM [Missionary] WHERE [MissionaryID] = @MissionaryID" InsertCommand="INSERT INTO [Missionary] ([FirstName], [LastName], [MissionFieldID], [SpouseFirstName], [ApproveDate], [Email], [StreetAddress], [City], [State], [Zip], [CellPhone]) VALUES (@FirstName, @LastName, @MissionFieldID, @SpouseFirstName, @ApproveDate, @Email, @StreetAddress, @City, @State, @Zip, @CellPhone); SELECT @MissionaryID = SCOPE_IDENTITY();" SelectCommand="SELECT [MissionaryID], [FirstName], [LastName], [MissionFieldID], [SpouseFirstName], [ApproveDate], [Email], [StreetAddress], [City], [State], [Zip], [CellPhone] FROM [Missionary] WHERE ([MissionaryID] = @MissionaryID)" UpdateCommand="UPDATE [Missionary] SET [FirstName] = @FirstName, [LastName] = @LastName, [MissionFieldID] = @MissionFieldID, [SpouseFirstName] = @SpouseFirstName, [ApproveDate] = @ApproveDate, [Email] = @Email, [StreetAddress] = @StreetAddress, [City] = @City, [State] = @State, [Zip] = @Zip, [CellPhone] = @CellPhone WHERE [MissionaryID] = @MissionaryID" OnInserted="MissionaryDataSource_Inserted">
        <DeleteParameters>
            <asp:Parameter Name="MissionaryID" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="MissionaryID" Direction="Output" Type="Int32" />
            <asp:Parameter Name="FirstName" Type="String" />
            <asp:Parameter Name="LastName" Type="String" />
            <asp:Parameter Name="MissionFieldID" Type="Int32" />
            <asp:Parameter Name="SpouseFirstName" Type="String" />
            <asp:Parameter DbType="Date" Name="ApproveDate" />
            <asp:Parameter Name="Email" Type="String" />
            <asp:Parameter Name="StreetAddress" Type="String" />
            <asp:Parameter Name="City" Type="String" />
            <asp:Parameter Name="State" Type="String" />
            <asp:Parameter Name="Zip" Type="String" />
            <asp:Parameter Name="CellPhone" Type="String" />
        </InsertParameters>

Open in new window


Then inside the inserted event I had to grab the output parameter

protected void MissionaryDataSource_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
       String missionaryId = e.Command.Parameters["@MissionaryID"].Value.ToString();

Open in new window


I don't feel that I can give out any points on this (accept maybe to myself) as the only solution I received was from someone who appearantly ran a google search on my question and then sent me a link to a page that I had already found and determined it did not work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DLAtkinsonAuthor Commented:
The solution works and it took me a long time to figure out how to do this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.