ASP.NET and @@IDENTITY

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

Guest

I'm trying to get the id of last row entered. The column is INT, but it
errors out saying 'Specified cast is not valid.'...

command.text = "SELECT @@IDENTITY FROM tablName";
SqlDataReader oRD = command.ExecuteReader();
while (oRD.Read())
{
Response.Write ("<BR> identiti is " + oReader.GetInt32(0).ToString());
}
What am I doing wrong?

Thnx
 
Mavrick.... If you are just trying to retrieve one value - typicallly you'd
want to opt for ExecuteScalar if for no other reason than it's cleaner.
Underneath the hood - the command object uses a DataReader so there's
nothing inherently wrong with the approach you used but as a rule of thumb,
I'd opt for ExcecuteScalar when I'm looking to get one value back. You can
also use an Output Parameter or ReturnValue to get what you want here.
Check out www.betav.com -> Articles -> MSDN and check out Managing an
@@Identity Crisis. I"m guessing your specific problem here isn't in the
logic that you're using per se - but are you definitely hitting the inside
of the while loop? At first glance I think you may have a problem if there
is no value that's returned but if that's the case, then the inside of the
loop should be getting hit anyway. Is the exception definitely being thrown
on the Response.Write line?
 
The data-type of @@IDENTITY is NUMERIC (decimal), not INT, that's why you're
getting an exception. You'll need to either call GetDecimal/GetSqlDecimal,
or cast it in the server "SELECT CONVERT(INT, @@IDENTITY)"

By the way, if you're using SQL Server 2000 or later, you should use
SCOPE_IDENTITY() instead of @@IDENTITY to be on the safe side (in case you
have triggers or similar things that could cause another identity to be
generated and overwrite the value in @@IDENTITY).

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
This works for me:
Using OleDB.., not Sql.. but I would imagine it should work the same...?

myCommand = new OleDbCommand("select @@identity",myConnection);
int newId = (int)myCommand.ExecuteScalar();
MessageBox.Show("New ID:" + newId.ToString());
 
There are a variety of issues I can see:

1. On the surface, you should use an ExecuteScalar(), which will "short
circuit" the Reader with a single value. This does not solve the @@IDENTITY
issue, however, as someone can insert a record between your insert and your
@@IDENTITY retrieve. This could end up with your app linking values to the
wrong record. Very nasty!

2. You do not have the IDENTITY value tied to the insert (alluded to above).
You need to INSERT and pull IDENTITY immediately after. The best method of
doing this is a stored procedure.

CREATE PROCEDURE TestProc
(
@IntValue int
, @SomeTextValue varchar(100)
)

BEGIN
INSERT INTO SomeTable (IntValue, SomeTextValue)
VALUES (@IntValue, @SomeTextValue)

-- Use instead of @@IDENTITY
SELECT SCOPE_IDENTITY()
END

GO

The above sproc is much safer than your direction, as SCOPE_IDENTITY pulls
the value that relates to the INSERT, even on a highly used system. Your
method will fail under scale. You will likely wish to further tweak the SQL,
as this is a small "sample". Your code to use this is something like:

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sprocName, conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param1 = new SqlParameter("@IntValue", intValue);
SqlParameter param2 = new SqlParameter("@SomeTextValue", someTextValue);

cmd.Parameters.Add(param1);
cmd.Parameters.Add(param2);

//Assumes an int type for the IDENTITY column
int identityValue;

try
{
conn.Open();
identityValue = cmd.ExecuteScalar();
}
finally
{
conn.Dispose();
}


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
I am not sure of that (decimal type), but I have not tested it, so I will
take your word for it. Overall, however, solving the problem requires
ensuring you get the correct IDENTITY value as much as it does getting the
right data type. The code example has an architectural issue as much as a
coding issue, IMO.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
This does not solve the @@IDENTITY
issue, however, as someone can insert a record between your insert and your
@@IDENTITY retrieve. This could end up with your app linking values to the
wrong record. Very nasty!
Click to expand...


Not true (I think). In my (limited I admit) testing, and with Access, not
SQL Server.
It would seem that Access holds an @@IDENTITY for each connection. I wrote
some code that added a new record then paused before reading the @@IDENITY,
then ran the application from two machines such that MachineB added a row
between MachineA writing its row and reading the @@IDENTITY. Both machines
still came up with the correct Identity.
Now surely if a 'baby' database like Access can get it right, a big grown up
DB like SQLServer should be able to manage it too...?

Then again...
 
Thanks all for your response and pointing various mistakes/corrections.

Pablo Castro answered what actually I was looking for. But I got bonuses
from rest of you guys and I do appreciate it.

Thnx, you guys are great...
 
Back
Top