2

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.

Improve this question
2
  • 1
    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 Commented Dec 15, 2014 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 Commented Dec 15, 2014 at 16:00

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I am inserting a new row in second form and saving it using the Save button on the nav bar
@envyM6 then... just SELECT SCOPE_IDENTITY() after the INSERT.
Mark I'm inserting the data using the grid view. not programmatically.
@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.
So what you are suggesting is to enter the data programmatically and include SELECT SCOPE_IDENTITY() ?
|
1

Get the ID with this line:

cmd.CommandText = "SELECT SCOPE_IDENTITY()";

Improve this answer

3 Comments

How can I store it in a variable?
@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
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.