mssql query help in finding the primary key of the last data added on a table in a db

Question

0.00/5 (No votes)

See more:

VS2008

, +

I there any way to get the primary key value of the data we just added to the database table?? suppose we use the insert query:

string qry="insert into tbl_student(name,marksObtained,class,section) values("Arpan",50,"10","A")";

here the primary key is stud_id whose value is given automatically,
how can i retirve the value of stud_id which is recently updated to the database??
thank u for your help
Arpan

Posted 25-Aug-11 3:40am

amsainju

Add a Solution

2 solutions

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)

Raju Prajapati · Answer 1 · 2011-08-25T03:56:00

Solution 1

Try SELECT @@IDENTITY After the Insert/Update Query,
@@IDENTITY returns the value of first columns Last Inserted/Updated

Posted 25-Aug-11 3:56am

Raju Prajapati

Comments

amsainju 25-Aug-11 10:55am

public static void insertNewStudent(string name,string clas,string section )
{
int STATUS;
SqlConnection con = GetConnection.getcon();
string qry = "insert into tbl_student(stud_name,stud_class,stud_section) values( @name,@clas,@section)";

SqlCommand cmd = new SqlCommand(qry, con);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@clas", clas);
cmd.Parameters.AddWithValue("@section", section);
STATUS= cmd.ExecuteNonQuery();
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
public static string getId()
{
string stud_id=null;
SqlConnection con = GetConnection.getcon();
string qry = "select @@IDENTITY";
SqlCommand cmd = new SqlCommand(qry, con);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
stud_id = sdr. ; //??????????????
break;
}
if (con.State == ConnectionState.Open)
{
con.Close();

}
return stud_id;

}

am i doing it right?? i can't figure out how i can retrive the data from sdr..
help

Raju Prajapati 12-Sep-11 5:55am

SELECT @@IDENTITY write after insert query text and use cmd.ExecuteScalar() to return the ID. In place of writing another method return just return the ID in same method where your are inserting. But remember one thing that ExecuteScalar() returns object so you have to cast it properly as per your requirement

Wayne Gaylard · Answer 2 · 2011-08-25T04:08:00

Solution 2

You can use Last_Insert_ID() function to get the last auto increment id. What I do is add the extra query to my command like this

C#

MySqlCommand cmd = new MySqlCommand("INSERT INTO tbl_student(name,marksObtained,class,section) VALUES ('Arpan',50,'10','A'); LAST_INSERT_ID();", connection);
           int lastID = (int)cmd.ExecuteScalar();

Here[^] is the reference manual link.

Hope this helps

Posted 25-Aug-11 4:08am

Wayne Gaylard

v2

Comments

amsainju 25-Aug-11 10:37am

does it help with mssql..that was the reference for mysql

Wayne Gaylard 26-Aug-11 2:03am

My apologies. I read that as MySQL. Sorry!

amsainju 25-Aug-11 10:52am

it didn't work it said error near LAST_INSERT_ID