zachvaldez
asked on
Get the last inserted ID
I'd like to get the last inserted Id from a table and display it using C#?
Caveat: Using it separetly can lead to concurrency issues.
So the question is: What is your use-case?
So the question is: What is your use-case?
If you are using SQL 2008 (or higher), you could also add it as an output clause in your insert statement; e.g. -
-saige-
string command = "insert into tables (columns) output inserted.identitycol values (...);")
SqlConnection conn = new SqlConnection(myConnString);
SqlCommand cmd = new SqlCommand(command, conn);
//usually done in a try catch, this is "dirty"
conn.Open();
int lastId = (int)cmd.ExecuteScalar();
conn.Close();
// do something with lastId.
-saige-
ASKER
How can you put this in a class separate from the page that will execute it?
And how will it be called by a button. Is it possible to separate the 2 process or procedure.
One that adds or insert the record then another class to execute it immediately when no error occurs.
And how will it be called by a button. Is it possible to separate the 2 process or procedure.
One that adds or insert the record then another class to execute it immediately when no error occurs.
Please define "page". Sounds like ASP.NET. In this case you should take a look at ASP.NET MVC.
Otherwise make yourself familiar with layered architecture.
A simple shortcut is using Entity Framework.
Also take a look at the MVVM pattern. This is also used when using WPF/XAML applications.
Otherwise make yourself familiar with layered architecture.
A simple shortcut is using Entity Framework.
Also take a look at the MVVM pattern. This is also used when using WPF/XAML applications.
ASKER
This is my Stored Proc. Can you just modify this SP and append the proposed solution? I think I will have a better understanding.
The core is..
Thanks,
The core is..
INSERT INTO PERSONTABLE
LASTNAME,
FIRSTNAME,
HOMEPHONE,
CELLPHONE,
LASTUPDATED,
LASTUPDATEDBY
VALUES
(
@lastname,
@firstname,
@homephone,
@cellphone,
convert(varchar(10),getdate(),101)
@lastupdatedby
Thanks,
ASKER
Btw, when I added this in the class,
"int lastId = (int)cmd.ExecuteScalar();"
I get error object not set reference.....
"int lastId = (int)cmd.ExecuteScalar();"
I get error object not set reference.....
ASKER
Here's the class
{
System.Data.SqlClient.Sqlcommand myCommand = default(System.Data.SqlClient.Sqlcommand);
mycommand=new System.Data.SqlClient.SqlCommand();
myConnection.connectionString=sqlstring;
myconnection.open();
myCommand.CommandText="InserttblPerson";
myCommand.CommandType= System.Data.CommandType.StoredProcedure;
myCommand.Parameters.Clear();
myCommand.Parameters.Add(new SqlParameter("@lastname",_Lastname);
myCommand.Parameters.Add(new SqlParameter("@firstname",_firstname);
..... and so forth
myCommand.ExecuteNonQuery();
SqlDataReader dReader = mycommand.ExecuteReader(commandBehaviour.CloseConnection);
return dReader;
}
My question is where will I add the ExecuteScalar result and how will I output it from a calling procedure
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The data Reader is fine. Saige is correct in using SCOPE_IDENTITY() instead of @@identity (triggers and what not).
Essentially you could just add:
to your stored procedure
then your datareader would be:
Essentially you could just add:
select SCOPE_IDENTITY() LastId
to your stored procedure
then your datareader would be:
dReader["lastId"];
ASKER
This is a new approach for me and to adopt such awesome technique!
THANKS!!!
THANKS!!!
You can use an ExecuteScalar if you're using a normal connection.
eg:
Open in new window