Reading last inserted UID from database using SQL Command

Question

I've got two table as per diagram below and have some sample data on the table as well.

enter image description here

What I'm trying to achieve in my project is as follows. I've got 4 windows form in my project. First form with two buttons

enter image description here

New User has a click event of-

private void button1_Click(object sender, EventArgs e)
{
    NewUser nu = new NewUser();
    nu.Show();

}

Upon click, it brings up NewUser form enter image description here

The plan is to add data here for Users Table then click Save and Proceed(right now I'm using the default Save button on NAV bar) which will take the UID from Users Table and add it under column UID (which is foreign key in Address Table) and open form FormAddress where I can enter the data for the table. enter image description here

Click event for Save and Proceed is--

private void button1_Click(object sender, EventArgs e)
{
    FormAddress ad = new FormAddress();
    ad.Show();
    getdata();
}

Upon research I've learned that I need to use SCOPE_IDENTITY to get the value of UID from last data insertion and carry it over to next form. So I've created a method getdata() as follows and added it under button event Save and Proceed

The code I tried is

private void getdata()
{

    SqlConnection sqlConnection1 = new SqlConnection(@"mycon string is here;" + "Initial Catalog=Stack;Integrated Security=YES");
    SqlCommand cmd = new SqlCommand();
    SqlDataReader reader;

    cmd.CommandText = "SELECT * FROM Users";
    cmd.CommandType = CommandType.Text;
    cmd.Connection = sqlConnection1;

    sqlConnection1.Open();

    reader = cmd.ExecuteReader();
    // Data is accessible through the DataReader object here.

    cmd.CommandText = "SCOPE_IDENTITY();";

    sqlConnection1.Close();
}

I Just can't get my head to work and figure out how to get the SCOPE_IDENTITYand make it work. Please ask me before I get down-voted. I'll respond and reply/make changes as necessary. And I have zipped the project and SQL server file HERE if anyone wants to download them to run and test. I've used SQL Server 2014 and Visual Studio 2013.

`SCOPE_IDENTITY()` returns the last identity created in the scope of the correct batch, in other words the id of the record that was just inserted. You are not inserting a record, so it is not applicable. What do you actually want? The highest current `UID`? `SELECT MAX(UID) FROM Users` — Ben Robinson, Dec 15 '14 at 15:57
@BenRobinson In second form `NewUser` I am inserting a new row. What I want is the UID to carry over automatically to next form `Address` where I can carry on filling the data under the same UID as `Users Table` — envyM6, Dec 15 '14 at 16:00

score 3 · Accepted Answer · edited May 23 '17 at 12:05

3

If you haven't just done an INSERT on that connection, SCOPE_IDENTITY() is undefined. You can't display the new IDENTITY value in advance, because most databases are multi-user - thus this value cannot be known until you actually do the INSERT. It would be misleading to display a "probably going to be this" value - the user might write it down or use it in communication with someone, for example. You can only show this value after it exists in the database.

But: when you an INSERT, you can then immediately simply query SCOPE_IDENTITY() to see what the value was. More recently, you can use the OUTPUT clause of an INSERT operation - simpler, only one set of SQL operations, and it works with multiple rows : all wins.

edited May 23 '17 at 12:05

Community

1
1

answered Dec 15 '14 at 16:00

Marc Gravell

1,026,079
266
2,566
2,900

I am inserting a new row in second form and saving it using the Save button on the nav bar – envyM6 Dec 15 '14 at 16:03
@envyM6 then... just `SELECT SCOPE_IDENTITY()` after the `INSERT`. – Marc Gravell Dec 15 '14 at 16:04
Mark I'm inserting the data using the grid view. not programmatically. – envyM6 Dec 15 '14 at 16:05
@envyM6 re `getdata()` - since that is a separate connection (which, incidentally, should probably be using a `using` statement), it has no access to the `SCOPE_IDENTITY()` you want; that is contextual *to the individual connection*. You could re-query the underlying data and replace it, perhaps. – Marc Gravell Dec 15 '14 at 16:09
So what you are suggesting is to enter the data programmatically and include `SELECT SCOPE_IDENTITY()` ? – envyM6 Dec 15 '14 at 16:13
@envyM6 I'm merely observing that if you use binding tools extensively, you need to live with the limitations of those tools. If you can do that: *great*. If you can simply re-query the data after the insert: *good enough*. Refactoring it and re-writing all the data access after-the-fact is not trivial. – Marc Gravell Dec 15 '14 at 16:15
Man I need a Jargon Buster! – envyM6 Dec 15 '14 at 16:16

score 1 · Answer 2 · answered Dec 15 '14 at 16:07

1

Get the ID with this line:

cmd.CommandText = "SELECT SCOPE_IDENTITY()";

answered Dec 15 '14 at 16:07

i486

6,491
4
24
41

How can I store it in a variable? – envyM6 Dec 15 '14 at 16:09
@envyM6 `int id = Convert.ToInt32(cmd.ExecuteScalar());`, but again: this won't help unless you are a: on the same connection (you aren't), and b: you haven't done anything else on that connection – Marc Gravell Dec 15 '14 at 16:10
The problem with different connection can be solved easily. Simply, use one connection for whole session (after Login) and not Open/Close it for each query - use the same connection for the Identity query. I hope editor windows can work in this way, too. I think this is the better method for Desktop applications instead of closing connection for each query. Look that MS SQL Studio works in the same way. Even with 'sp_who' you can see your app in the list. – i486 Dec 15 '14 at 16:29

Reading last inserted UID from database using SQL Command

2 Answers2