Two years and still no answer.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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" direction:output 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>
<asp:Parameter 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
Eliyahu Goldin said:
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?
Click to expand...
Click to expand...
 
Thank you, but I believe SQL Server Profiler isn't available with SQL Server
Express, which is what I'm running.

Paul
 
FOUND IT!!!!!! REMOVE THE @ IN PARAMETER NAME!!!!!!!!!!!!!! IT
WORKS!!!!!!!!!!!!
<InsertParameters>
<asp:Parameter Name="AdrID" Type=Int32 Direction=Output />
</InsertParameters>
Click to expand...



Paul Buzza said:
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" direction:output 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>
<asp:Parameter 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
Eliyahu Goldin said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top