On Sep 21, 2:57*am, "Mark Rae [MVP]" <m...@markNOSPAMrae.netwrote:
"Tom P." <padilla.he...@gmail.comwrote in message
news:58**********************************@m44g2000 hsc.googlegroups.com...
I have a webpage that takes user data and sends it to a stored
procedure that inserts a row in a table then returns the ID of that
row for display.
The stored procedure does an insert then it uses IDENT_CURRENT to get
the identity of the row that was just inserted and selects the row
back out to display.
And there's your problem. Use SELECT @@IDENTITY or, even better, SELECT
SCOPE_IDENTITY()...
http://msdn.microsoft.com/en-us/libr...5(SQL.90).aspx
--
Mark Rae
ASP.NET MVPhttp://www.markrae.net
Except I don't want just some random IDENTITY value I want the last
IDENTITY value that was inserted into this specific table. And what
would that have to do with the data being returned?
In any case this is the stroed Proc that is executing but not
returning rows:
CREATE PROC [IMAGING\PadillaH].[spCreateAcquisition]
@FileUploadName varchar (50),
@AcquisitionID int
AS
SET NOCOUNT ON
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Variable Declarations
DECLARE @InternalAcquisitionID int;
--BEGIN TRAN
-- Insert initial values into table
INSERT INTO
Acquisition(
AcquisitionID,
[Filename],
AcquisitionStatusCode
)
VALUES
(
@AcquisitionID,
@FileUploadName,
'U' );
--COMMIT
-- Get the Identity value that was last used in this table
SET @InternalAcquisitionID = IDENT_CURRENT('Acquisition');
--Select the information back out for the entire row
SELECT
InternalAcquisitionID,
AcquisitionID,
ServicerID,
'',
FileUploadDate,
TotalLoanCount,
LoansAssigned,
TotalLoanCount - LoansAssigned AS LoansUnassigned,
TotalIndexes,
UnmatchedIndexes,
TotalIndexes - UnmatchedIndexes AS MatchedIndexes,
TotalProductCodes,
UnmatchedProductCodes,
TotalProductCodes - UnmatchedProductCodes AS MatchedProductCodes,
LastProcessedTime,
Acquisition.AcquisitionStatusCode,
[Description],
[Filename]
FROM
Acquisition
INNER JOIN AcquisitionStatusCode
ON Acquisition.AcquisitionStatusCode =
AcquisitionStatusCode.AcquisitionStatusCode
WHERE
InternalAcquisitionID = @InternalAcquisitionID
The insert happens, I've verified that. The select layout gets back to
the code, I've verified that. But there is no data row that gets back
to the code. The SQLServer is on a different box than the code is
running on. I have a page before this that is getting rows back from
this table so it CAN happen.
I've tried setting dirty reads (it's commented out above). I've tried
setting transactions and then commit (also commented out). I can't get
the data to come back from this procedure.
Tom P.
