How do you use SCOPE_IDENTIY to retrieve last inserted value?

Question

0.00/5 (No votes)

See more:

VS2010

Hello, i am using the 3 tier architecture for my project. I am trying to retrieve the last inserted value into my database table using the SCOPE_IDENTITY.

Any idea how i can achieve this using SCOPE_IDENTITY AND the 3 tier architecture?

Posted 17-Jun-13 4:42am

Jerrell77

Add a Solution

Comments

Rob Philpott 17-Jun-13 11:37am

Please be more specific. Are you trying to do this from C# or in a stored proc. Basically you just select SCOPE_IDENTITY() and use something like ExecuteScalar to get that, but you need to make sure the scope is correct. Ideally do it in a stored proc and call from c#..

Jerrell77 17-Jun-13 12:00pm

Hi Sorry for being unclear. I'm trying to use SCOPE_IDENTITY() using SQL Cmd in the DAL as such :

public int getScope()
{
SqlConnection conn;
StringBuilder sql;
SqlCommand sqlCmd;
int result;

result = 0;
conn = dbConn.GetConnection();
sql = new StringBuilder();
sql.AppendLine("SELECT SCOPE_IDENTITY() FROM WP_advUploads");

try
{
conn.Open();
sqlCmd = new SqlCommand(sql.ToString(), conn);
result = Convert.ToInt32(sqlCmd.ExecuteScalar());

}
catch (Exception ex)
{
errMsg = ex.Message;
}
finally
{
conn.Close();
}
return result;
}

The Error Message was "Invalid column SCOPE_IDENTITY()"

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chris Ross 2 · Answer 1 · 2013-06-17T07:24:00

Unfortunately, you can't use Scope_Identity() like in your reply to Rob's comment above.

You should only call Scope_Identity() immediately after you've done an INSERT into a table that has an IDENTITY column - since its purpose is to get the identity value generated by the database engine for the inserted row.

As Rob says, doing it in a stored procedure is often the best way. If you use a stored procedure, you could do something like this:

C#

sqlCmd.CommandText = "sp_InsertIntoUploads";
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue(...);
.... for all values to store
sqlCmd.Parameters.AddWithValue("@Id", 0).Direction = ParameterDirection.Output;

var id = sqlCmd.ExecuteNonQuery();

And your stored procedure would look like this:

SQL

CREATE PROCEDURE dbo.sp_InsertIntoUploads
(
... values to put into the columns...,
@Id INT OUTPUT  -- returns the generated id
)
AS
BEGIN

INSERT INTO uploadsTable(columns...)
VALUES (values...)

SELECT @Id = Scope_Identity()

END
GO

If you prefer not to use OUTPUT parameters from the stored procedure you can use ExecuteScalar() and in the sproc use SELECT Scope_Identity().