Weird problem.

[Tom P.]

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.

The dataset is not getting populated. The dataset has a DataTable that
gets created and the DataTable has the columns named correctly
according to the final select but there's no data in the table.

I've tried SET NOCOUNT ON, I've tried READ UNCOMMITTED, I've tried
COMMITing after the insert...

I've tried just limiting the data to return the ID and I'll do another
select later but I can't even get the ID out.

I just don't get it.

If anyone has any ideas please let me know. If there's anything else
you need to know just ask.

Tom P.

 

[Peter Bromberg [C# MVP]]

Well,
obviously (or perhaps not so) there is something wrong with your code. Also,
I'm not familiar with "IDENT_CURRENT" - @@IDENTITY will do this for SQL
Server. Do you want to post a short-but-complete code sample so that you can
get some help?
Peter

 

[leeeyre]

Apparently the IDENT_CURRENT returns the last identity created by the server for any connection, bit pointless really, so you should use the @@IDENTITY.

If you post the SP I will take a look for you.

 

[Tom P.]

And there's your problem. Use SELECT @@IDENTITY or, even better, SELECT
SCOPE_IDENTITY()...

http://msdn.microsoft.com/en-us/library/ms190315(SQL.90).aspx

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.

 

[Tom P.]

In which case, you're using the wrong function - read the docs...


SET @InternalAcquisitionID = SCOPE_IDENTITY();

OK, someone's gonna have to explain that to me.

First off, it worked, thanks. (Dear God, thanks) But all that did is
confuse me even more.

IDENT_CURRENT was returning the correct identity. This is only now in
DEV so there are no others hitting the table to get confused with.
Besides, even if it wasn't returning the correct identity it should
have returned incorrect data, not nothing at all.

Does SCOPE_IDENTITY imply something about the transaction? Is that
what was happening? IDENT_CURRENT was abandonig the transaction or
something? But the identity it returned was correct... I've read both
articles and I don't get the difference. Please, if you could explain
better than MS I'd appreciate it.

And thanks again for the persistence in helping me.

Tom P.

 

[SAL]

The docs for IDENT_CURRENT:
Returns the last identity value generated for a specified table or view in
any session and any scope

That sounds like trouble to me but then, who am I right?

The docs say this for @@SCOPE_IDENTITY():
Returns the last identity value inserted into an identity column in the same
scope. A scope is a module: a stored procedure, trigger, function, or batch.
Therefore, two statements are in the same scope if they are in the same
stored procedure, function, or batch

Wow, sounds much safer to me...

S

In which case, you're using the wrong function - read the docs...


SET @InternalAcquisitionID = SCOPE_IDENTITY();

OK, someone's gonna have to explain that to me.

First off, it worked, thanks. (Dear God, thanks) But all that did is
confuse me even more.

IDENT_CURRENT was returning the correct identity. This is only now in
DEV so there are no others hitting the table to get confused with.
Besides, even if it wasn't returning the correct identity it should
have returned incorrect data, not nothing at all.

Does SCOPE_IDENTITY imply something about the transaction? Is that
what was happening? IDENT_CURRENT was abandonig the transaction or
something? But the identity it returned was correct... I've read both
articles and I don't get the difference. Please, if you could explain
better than MS I'd appreciate it.

And thanks again for the persistence in helping me.

Tom P.