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_CURR ENT" - @@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

"Tom P." <pa***********@ gmail.comwrote in message
news:58******** *************** ***********@m44 g2000hsc.google groups.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.

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.

[Mark Rae [MVP]]

"Peter Bromberg [C# MVP]" <pb*******@dont doit.yahoo.comw rote in message
news:A6******** *************** ***********@mic rosoft.com...

>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.

I'm not familiar with "IDENT_CURR ENT"

http://msdn.microsoft.com/en-us/libr...8(SQL.90).aspx
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Mark Rae [MVP]]

"Tom P." <pa***********@ gmail.comwrote in message
news:58******** *************** ***********@m44 g2000hsc.google groups.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 MVP
http://www.markrae.net

[Tom P.]

On Sep 21, 2:57*am, "Mark Rae [MVP]" <m...@markNOSPA Mrae.netwrote:

"Tom P." <padilla.he...@ gmail.comwrote in message

news:58******** *************** ***********@m44 g2000hsc.google groups.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\Padilla H].[spCreateAcquisi tion]
@FileUploadName varchar (50),
@AcquisitionID int
AS

SET NOCOUNT ON
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Variable Declarations
DECLARE @InternalAcquis itionID int;

--BEGIN TRAN

-- Insert initial values into table
INSERT INTO
Acquisition(
AcquisitionID,
[Filename],
AcquisitionStat usCode
)
VALUES
(
@AcquisitionID,
@FileUploadName ,
'U' );

--COMMIT

-- Get the Identity value that was last used in this table
SET @InternalAcquis itionID = IDENT_CURRENT(' Acquisition');

--Select the information back out for the entire row
SELECT
InternalAcquisi tionID,
AcquisitionID,
ServicerID,
'',
FileUploadDate,
TotalLoanCount,
LoansAssigned,
TotalLoanCount - LoansAssigned AS LoansUnassigned ,
TotalIndexes,
UnmatchedIndexe s,
TotalIndexes - UnmatchedIndexe s AS MatchedIndexes,
TotalProductCod es,
UnmatchedProduc tCodes,
TotalProductCod es - UnmatchedProduc tCodes AS MatchedProductC odes,
LastProcessedTi me,
Acquisition.Acq uisitionStatusC ode,
[Description],
[Filename]
FROM
Acquisition
INNER JOIN AcquisitionStat usCode
ON Acquisition.Acq uisitionStatusC ode =
AcquisitionStat usCode.Acquisit ionStatusCode
WHERE
InternalAcquisi tionID = @InternalAcquis itionID
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.

[Mark Rae [MVP]]

"Tom P." <pa***********@ gmail.comwrote in message
news:84******** *************** ***********@l42 g2000hsc.google groups.com...

>>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

Except I don't want just some random IDENTITY value I want the last
IDENTITY value that was inserted into this specific table.

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

SET @InternalAcquis itionID = IDENT_CURRENT(' Acquisition');

SET @InternalAcquis itionID = SCOPE_IDENTITY( );
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Tom P.]

On Sep 22, 7:22*am, "Mark Rae [MVP]" <m...@markNOSPA Mrae.netwrote:

"Tom P." <padilla.he...@ gmail.comwrote in message

news:84******** *************** ***********@l42 g2000hsc.google groups.com...

>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

Except I don't want just some random IDENTITY value I want the last
IDENTITY value that was inserted into this specific table.

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

SET @InternalAcquis itionID = IDENT_CURRENT(' Acquisition');

SET @InternalAcquis itionID = SCOPE_IDENTITY( );

--
Mark Rae
ASP.NET MVPhttp://www.markrae.net

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_IDENTIT Y():
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
"Tom P." <pa***********@ gmail.comwrote in message
news:1e******** *************** ***********@8g2 000hse.googlegr oups.com...
On Sep 22, 7:22 am, "Mark Rae [MVP]" <m...@markNOSPA Mrae.netwrote:

"Tom P." <padilla.he...@ gmail.comwrote in message

news:84******** *************** ***********@l42 g2000hsc.google groups.com...

>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

Except I don't want just some random IDENTITY value I want the last
IDENTITY value that was inserted into this specific table.

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

SET @InternalAcquis itionID = IDENT_CURRENT(' Acquisition');

SET @InternalAcquis itionID = SCOPE_IDENTITY( );

--
Mark Rae
ASP.NET MVPhttp://www.markrae.net

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.

[Mark Rae [MVP]]

"SAL" <SA*@nospam.nos pamwrote in message
news:%2******** ********@TK2MSF TNGP04.phx.gbl. ..

[please don't top-post]

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_IDENTIT Y():
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...

Absolutely correct.
--
Mark Rae
ASP.NET MVP
http://www.markrae.net