Two years and still no answer.

[Guest]

I've seen this question posed for two years, and no one has answered. Is it
not possible to do? Is this the wrong newsgroup?

When using a FormView on an aspx page and SQL Server, how does one discover
the identity of the most recently inserted record?

 

[Eliyahu Goldin]

Run a batch:

insert ...;select scope_identity()


Make a SQLCommand object for this batch and run it with an ExecuteScalar
call.


--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net


"Paul Buzza, oldster using new tools"

 

[Guest]

Howdy,

Run SQL Server Profile and you'll find out for yourself (scope_identity())

 

[Guest]

sorry for typo, should be Sql Server Profiler

 

[Guest]

I'm operating just beyond my level of competence, as you can probably tell.
However, after 40 or so hours of research on this question, I have done the
following. Am I completely off the track on what I've done?

Here's what I have tried.....
--change in-line Insert in the SqlDataSource from Text to Stored Procedure
--added Insert parameter "AdrID" directionutput to the SqlDataSource
--added "AdrId" as an OUT parameter in the Stored Procedure
--try to retrieve the "AdrId" in the Inserted Event handler of the
SqlDataSource

The new record is successfully inserted, but I'm still getting an error:
"An SqlParameter with ParameterName '@AdrID' is not contained by this
SqlParameterCollection."

Relevant coding:

from MaintAdrDetail.aspx....

<asp:SqlDataSource ID="AdrDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:LWIF_SQLConnectionString %>"
InsertCommand="AdrInsert" InsertCommandType="StoredProcedure"
OldValuesParameterFormatString="original_{0}"
<InsertParameters>
<asparameter Name="@AdrID" Type=Int32 Direction=Output />
</InsertParameters>

from AdrInsert stored procedure....

ALTER PROCEDURE dbo.AdrInsert

@AdrFirstName nvarchar(25),
@AdrLastName nvarchar(50),
[clip]
@AdrOwnerID nvarchar(4),
@AdrNote nvarchar(MAX),
@AdrID int=0 OUTPUT
AS
INSERT INTO Addresses
(
[AdrFirstName],
[AdrLastName],
[clip]
[AdrOwnerID],
[AdrNote]
)
VALUES
(
@AdrFirstName,
@AdrLastName,
[clip]
@AdrOwnerID,
@AdrNote
);
SELECT @AdrID = @@IDENTITY

From MainAdrDetail.aspx.vb

Protected Sub AdrDataSource_Inserted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
AdrDataSource.Inserted
Session("AdrSelected") =
e.Command.Parameters("@AdrID").Value.ToString()
End Sub

Run a batch:

insert ...;select scope_identity()


Make a SQLCommand object for this batch and run it with an ExecuteScalar
call.


--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net


"Paul Buzza, oldster using new tools"

I've seen this question posed for two years, and no one has answered. Is
it
not possible to do? Is this the wrong newsgroup?

When using a FormView on an aspx page and SQL Server, how does one
discover
the identity of the most recently inserted record?

 

[Guest]

Thank you, but I believe SQL Server Profiler isn't available with SQL Server
Express, which is what I'm running.

Paul

 

[Guest]

FOUND IT!!!!!! REMOVE THE @ IN PARAMETER NAME!!!!!!!!!!!!!! IT
WORKS!!!!!!!!!!!!

<InsertParameters>
<asparameter Name="AdrID" Type=Int32 Direction=Output />
</InsertParameters>

I'm operating just beyond my level of competence, as you can probably tell.
However, after 40 or so hours of research on this question, I have done the
following. Am I completely off the track on what I've done?

Here's what I have tried.....
--change in-line Insert in the SqlDataSource from Text to Stored Procedure
--added Insert parameter "AdrID" directionutput to the SqlDataSource
--added "AdrId" as an OUT parameter in the Stored Procedure
--try to retrieve the "AdrId" in the Inserted Event handler of the
SqlDataSource

The new record is successfully inserted, but I'm still getting an error:
"An SqlParameter with ParameterName '@AdrID' is not contained by this
SqlParameterCollection."

Relevant coding:

from MaintAdrDetail.aspx....

<asp:SqlDataSource ID="AdrDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:LWIF_SQLConnectionString %>"
InsertCommand="AdrInsert" InsertCommandType="StoredProcedure"
OldValuesParameterFormatString="original_{0}"
<InsertParameters>
<asparameter Name="@AdrID" Type=Int32 Direction=Output />
</InsertParameters>

from AdrInsert stored procedure....

ALTER PROCEDURE dbo.AdrInsert

@AdrFirstName nvarchar(25),
@AdrLastName nvarchar(50),
[clip]
@AdrOwnerID nvarchar(4),
@AdrNote nvarchar(MAX),
@AdrID int=0 OUTPUT
AS
INSERT INTO Addresses
(
[AdrFirstName],
[AdrLastName],
[clip]
[AdrOwnerID],
[AdrNote]
)
VALUES
(
@AdrFirstName,
@AdrLastName,
[clip]
@AdrOwnerID,
@AdrNote
);
SELECT @AdrID = @@IDENTITY

From MainAdrDetail.aspx.vb

Protected Sub AdrDataSource_Inserted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
AdrDataSource.Inserted
Session("AdrSelected") =
e.Command.Parameters("@AdrID").Value.ToString()
End Sub

Run a batch:

insert ...;select scope_identity()


Make a SQLCommand object for this batch and run it with an ExecuteScalar
call.


--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net


"Paul Buzza, oldster using new tools"

I've seen this question posed for two years, and no one has answered. Is
it
not possible to do? Is this the wrong newsgroup?

When using a FormView on an aspx page and SQL Server, how does one
discover
the identity of the most recently inserted record?