How Do I Correctly Use Scope_Identity C#

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

Hi all i have the following piece of code and cant seem to use SCOPE_IDENTITY correctly. I need to find the ID column so i can use it to insert data into my sql table.

C#

SqlCommand myCommand = new SqlCommand("INSERT INTO cas_user_ext(x_id, fullname, email, name) VALUES(@x_id, @fullname, @email, @name) SET @Identity = SCOPE_IDENTITY();",
sqlcon.Connection);
//int x_id = System.Convert.ToInt32(myCommand.ExecuteScalar());
decimal decimalx_id = (decimal)myCommand.ExecuteScalar();
int x_id = (int)decimalx_id;
myCommand.Parameters.Add("@Name", Environment.UserName);
myCommand.Parameters.Add("@identity", x_id);
myCommand.Parameters.Add("@fullname", textBox1.Text);
myCommand.Parameters.Add("@email", textBox2.Text);
//primarypin = myReader["primarypin"].ToString();
//secondarypin = myReader["secondarypin"].ToString();
myCommand.ExecuteNonQuery();

If anyone could please assist me. I have gone through countless help articles and read up on so many pages but i cant seem to get it working.

Posted 30-Sep-14 5:26am

Ivan Lubbe

Add a Solution

Comments

ZurdoDev 30-Sep-14 11:44am

See Solution 1. ExecuteNonQuery means you're not expecting to get anything back.

PIEBALDconsult 30-Sep-14 11:46am

I recommend you not use Identities because they cause too much trouble like this. It's better to know the ID of a record _before_ you INSERT it. Try using Sequences instead.

ZurdoDev 1-Oct-14 9:10am

Really? I always use IDs and never have any issue.

Ivan Lubbe 2-Oct-14 1:20am

Honestly i'm learning as i go along. I'm working for a MPS company and am working on some projects on the side and in my free time to make things easier for myself and the company. I appreciate everyone's assistance. I feel like a nuisance having posted so many questions on here.

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)

SteveyJDay · Answer 1 · 2014-09-30T05:31:00

Solution 1

You don't create the identity before the insert. SQL Server will handle that for your. Also don't insert the identity column.

Original Solution:

C#

using (SqlConnection sqlcon = new SqlConnection("Server")) {
	using (SqlCommand myCommand = new SqlCommand("INSERT INTO cas_user_ex(fullname, email, name) VALUES(@fullname, @email, @name); SELCET SCOPE_IDENTITY();", sqlcon)) {
		myCommand.Parameters.Add("@Name", Environment.UserName);
		myCommand.Parameters.Add("@fullname", textBox1.Text);
		myCommand.Parameters.Add("@email", textBox2.Text);

		int iNewRowIdentity = Convert.ToInt32(myCommand.ExecuteScalar());
		}
	}

Revised Solution:

C#

using (SqlCommand myCommand = new SqlCommand("INSERT INTO cas_user_ex(fullname, email, name) VALUES(@fullname, @email, @name); SELCET SCOPE_IDENTITY();", sqlcon.Connection)) {
	myCommand.Parameters.Add("@Name", Environment.UserName);
	myCommand.Parameters.Add("@fullname", textBox1.Text);
	myCommand.Parameters.Add("@email", textBox2.Text);

	int iNewRowIdentity = Convert.ToInt32(myCommand.ExecuteScalar());
	}

Posted 30-Sep-14 5:31am

SteveyJDay

Updated 1-Oct-14 3:08am

v4

Comments

Ivan Lubbe 1-Oct-14 2:41am

I'm getting an error with the above code "'SQLConnect:' type used in a using statement must be implicitly convertible to 'System.IDisposable'

Ivan Lubbe 1-Oct-14 3:07am

my sql connection is initiated by a class if that makes any difference?

SteveyJDay 1-Oct-14 9:10am

You can remove the SqlConnection using statement and use your class. Check out the revised solution.

Ivan Lubbe 2-Oct-14 1:17am

Thank you Jared, i will be testing it now :)

Ivan Lubbe 2-Oct-14 2:03am

Hi Jared,

I am getting the error below

System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'x_id', table 'eqcas.dbo.cas_user_ext'; column does not allow nulls. INSERT fails.
The statement has been terminated.

SteveyJDay 2-Oct-14 8:45am

Your x_id column should be an identity column. Use the script below to create an identity column on your table.

ALTER TABLE cas_user_ex ADD x_id_temp INT IDENTITY(1,1)
ALTER TABLE cas_user_ex DROP COLUMN x_id
sp_rename 'cas_user_ex.x_id', 'x_id_temp', 'COLUMN'

Documentation:
http://msdn.microsoft.com/en-us/library/ms186775.aspx

Ivan Lubbe 6-Oct-14 5:41am

Will this be part of the sql command?

SteveyJDay 6-Oct-14 9:07am

No this shouldn't be part of your code at all. You need to run this script(via SQL Management Studio) once against your table. This script creates the identity column. Your table needs an identity column to use SELCET SCOPE_IDENTITY()

Ivan Lubbe 6-Oct-14 10:55am

I have an identity column. x_id the problem i have is that i'm not sure how to increment when adding new rows to my current columns and incrementing the value of my x_id and adding it to the new rows. lets say my last row's x_id was 8 then obviously when i add a new row to my columns it will need to increment to 9 and add the data. So when i call on my data i will use x_id '9'. Sorry if my question is rather stupid but i cant really find any simple and clear tutorials on this. Thanks Stevey

SteveyJDay 6-Oct-14 11:40am

You may think you have an identity column but it is not setup that way in SQL Server. The reason I know this is because the error you posted above. SQL Server wouldn't be looking for a value if it was an identity column. SQL Server would know it needs to auto increment off the last value.

Your Error:
System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'x_id', table 'eqcas.dbo.cas_user_ext'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Ivan Lubbe 7-Oct-14 7:12am

Msg 5074, Level 16, State 1, Line 2
The object 'PK_cas_user_ext' is dependent on column 'x_id'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE DROP COLUMN x_id failed because one or more objects access this column.

Ivan Lubbe 7-Oct-14 7:13am

As far as i understand it is a identity column but extends over the other tables, the name changes but the values are the same.

Ivan Lubbe 7-Oct-14 7:15am

Primary key in that column is also set to true

Ivan Lubbe 7-Oct-14 2:42am

I am getting the error Incorrect syntax near 'sp_rename'

Ivan Lubbe 7-Oct-14 2:44am

Never mind i forgot to add EXEC

Ivan Lubbe 7-Oct-14 2:48am

Just a quick question. Will this rename the columns? Why i am asking is because the database is created by an application called Equitrac and is still being used by the application.

Ivan Lubbe 2-Oct-14 2:36am

Sorry Jared, let me just clarify a bit i am trying to insert 'new' rows of data to the database and columns. So the current ID column extends to lets say 8, so my the data i am trying to insert is to row 9 then. I am fairly new to c# and sql and any assistance would be appreciated.

SteveyJDay 2-Oct-14 8:52am

In most cases you want SQL Server to do the incrementing of the table id value (via the identity column). The other method is to select the top most ID of the table, add 1, and use that value in insert statement(I think this is what your original code was trying to do). This is bad practice because as the system grows two inserts could happen at the same time with both trying to use the same ID.