Retrieve Identity after Insert

I am a "newbie" and have been struggling with this for days! I have users enter their residence information and insert which generates houseid. I want to use/display that houseid on next page/step. I am VERY FRUSTRATED and would appreciate any assistance!

<

scriptrunat="server">ProtectedSub SqlDataSource1_Inserting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlDataSource1.Inserting

e.Command.Parameters(

"@.house").Size = 5EndSubProtectedSub SqlDataSource1_Inserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.InsertedDim house = e.Command.Parameters("@.house").Value

Response.Write(house)

EndSubProtectedSub Wizard1_FinishButtonClick(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.WizardNavigationEventArgs)EndSubProtectedSub SqlDataSource1_Selecting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)EndSub</script>

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:ic_registerConnectionString %>"oninserted="SqlDataSource1_Inserted"oninserting="SqlDataSource1_Inserting"DeleteCommand="DELETE FROM [household] WHERE [householdid] = @.original_householdid AND [housenum] = @.original_housenum AND [streeraddr] = @.original_streeraddr AND [aptnum] = @.original_aptnum AND [city] = @.original_city AND [state] = @.original_state AND [zipcode] = @.original_zipcode AND [HHPhone] = @.original_HHPhone AND [timedate] = @.original_timedate"InsertCommand="INSERT INTO [household] ([housenum], [streeraddr], [aptnum], [city], [state], [zipcode], [HHPhone], [timedate]) VALUES (@.housenum, @.streeraddr, @.aptnum, @.city, @.state, @.zipcode, @.HHPhone, { fn NOW() }); SELECT @.house = SCOPE_IDENTITY()"OldValuesParameterFormatString="original_{0}"SelectCommand="SELECT * FROM [household]"UpdateCommand="UPDATE [household] SET [housenum] = @.housenum, [streeraddr] = @.streeraddr, [aptnum] = @.aptnum, [city] = @.city, [state] = @.state, [zipcode] = @.zipcode, [HHPhone] = @.HHPhone, [timedate] = @.timedate WHERE [householdid] = @.original_householdid AND [housenum] = @.original_housenum AND [streeraddr] = @.original_streeraddr AND [aptnum] = @.original_aptnum AND [city] = @.original_city AND [state] = @.original_state AND [zipcode] = @.original_zipcode AND [HHPhone] = @.original_HHPhone AND [timedate] = @.original_timedate"OnSelecting="SqlDataSource1_Selecting"><InsertParameters><asp:ParameterName="housenum"Type="String"/><asp:ParameterName="streeraddr"Type="String"/><asp:ParameterName="aptnum"Type="String"/><asp:ParameterName="city"Type="String"/><asp:ParameterName="state"Type="String"/><asp:ParameterName="zipcode"Type="String"/><asp:ParameterName="HHPhone"Type="String"/><asp:ParameterType="String"Name="house"Direction="Output"/></InsertParameters>

Next question is this easier to do using a Wizard Control and DetailsView on a "step" or using seperate pages and FormView? Or does it matter?

Can anybody help me with this please?|||

I believe that you want to handle the Inserted event. Here's a snippet from the help that should lead you on the right direction.

Sub On_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
Dim command As DbCommand
command = e.Command

' The label displays the primary key of the recently inserted row.
Label1.Text = command.Parameters("@.PK_New").Value.ToString()

' Explicitly call DataBind to refresh the data
' and show the newly inserted row.
GridView1.DataBind()
End Sub 'On_Inserted

Retrieve ID of Last Insert (Scope_Identity)

(Newbie) Hi, I am trying to create in a session variable, the ID of the last inserted record. My reading suggests I should use Scope_Identity. I'm having trouble with the syntax/code structure. Also, is it good programming practise to directly assign the session variable e.g. "Session[var]=SqlDataSource.Select()"? The error I'm getting from my code below is "No overload for method SELECT takes 0 arguments". Thanks.

Session["snCoDeptRowID"] = SqlDataSource1.Select();

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT Scope_Identity"

</asp:SqlDataSource>

try

SelectCommand="SELECT Scope_Identity()"

|||

Thank you for the suggestion. Now I am having trouble with the following line of code which assigns the Scope_Identity() to a session variable. The error msg is: does not recognise the word "command". How can I get the value of the @.CoDeptRowID (Scope_Identity) into my session variable? Thanks

Session["snCoDeptRowID"] = Convert.ToInt32(e.command.parameters("@.CoDeptRowID").value);

|||

Check thate argument has a command object

only if its there in event argument you will be able to use it. And Check the Direction of the @.CoDeptRowId is set to Output in Command as well

|||

Hi,

There are many ways to get the SCOPE_IDENTITY() from stored procedure.

You can use SELECT SCOPE_IDENTITY() or RETURN SCOPE_IDENTITY() or SET @.PARAM = SCOPE_IDENTITY(). All is fine, but the way to get it is different.

SELECT will return the SCOPE_IDENTITY() as the first col and first row of a result set. RETURN will return is as a return parameter. While SET will set the value to a parameter and you have to specify it as an OUTPUT parameter when declaring it.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!