Get self-increment primary key value after SQL Server inserts data

Source: Internet
Author: User

Get through the SQL Server System self-brought function

String sql ="INSERT into goods values ('"+ TextBox1.Text +"',"+ TextBox2.Text +", 0,"+ TextBox3.Text +", ', '"+ DropDownList1.SelectedItem.Text +"', '"+ session["username"]. ToString () +"', 0, '"+ Textbox4.text +"' , ' failed '); select Scope_identity ()"; SqlConnection Conn=NewSqlConnection (s); SqlCommand cmd=NewSqlCommand (SQL,CONN); Conn. Open ();intids= Convert.ToInt32 (cmd. ExecuteScalar ());

Related content:

Scope_identity, Ident_current, and @ @IDENTITY, both return values inserted into the IDENTITY column.
Ident_current returns the identity value that was last generated for any session and for a particular table in any scope. Ident_current is not subject to scope and session restrictions, but is limited to the specified table. Ident_current returns the value generated for a specific table in any session and scope.
@ @IDENTITY Returns the identity value that was last generated for any table in all scopes of the current session.
Scope_identity returns the identity value that was last generated for the current session and any tables in the current scope
The scope_identity and @ @IDENTITY return the last identity value generated within any table in the current session. However, scope_identity only returns the value inserted into the current scope, and the @ @IDENTITY is not restricted to a specific scope.

For example, there are two tables T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted into the T1, the trigger is fired and a row is inserted in the T2. This example illustrates two scopes: one is an insert on T1 and the other is an insert on T2 as the result of a trigger.

Assuming that both T1 and T2 have an IDENTITY column, @ @IDENTITY and scope_identity will return different values at the end of the INSERT statement on T1.

@ @IDENTITY returns the last IDENTITY column value inserted into any scope in the current session, which is the value inserted into the T2.

Scope_identity () returns the IDENTITY value in the Insert T1, which is the last insert that occurs in the same scope. The function returns a NULL value if the scope_identity () function is invoked before the INSERT statement to the IDENTITY column occurs in the scope.

The values returned by Ident_current (' T1 ') and ident_current (' T2 ') are the last self-increment values of the two tables respectively.

Attached: basic knowledge of using @identity

1. The IDENTITY column cannot be updated directly by the user, it is automatically maintained by the system.

2. The column data type must be numeric: int, smallint, tinyint, decimal or numeric with scale 0.

3. The column cannot be null.

4. You cannot set a default value on this column.

5. Increments can only be cosmetic (for example: 1,2,-3). cannot be a decimal, nor can it be 0.

6. The base value (seed) can be set by the user and the default value is 1.

Understanding @ @IDENTITY

@ @IDENTITY Returns the value of the last inserted IDENTITY, including: Insert, SELECT into, or bulk copy. If you insert a record into another table that does not have an IDENTITY column, the system resets it to null. If more than one row of records is inserted into the IDENTITY table, the @ @IDENTITY represents the last generated value. If a trigger is triggered and the trigger executes an insert operation to another table with an IDENTITY column, @ @IDENTITY returns the value generated by the trigger. If the trigger inserts a table that does not contain an IDENTITY column, then the @ @IDENTITY will be null. If the insert operation fails, the @ @IDENTITY value will still increase, so the IDENTITY does not guarantee the continuity of the data.

@ @IDENTITY is a global variable for the current connection and is valid only for the current connection. That is, if the connection is disconnected and then reconnected, the @ @IDENTITY is null. For ADO, the @ @IDENTITY is meaningful during Connection object opening and closing, which is valid within the scope of the Connection object's existence. In an MTS component, the @ @IDENTITY is meaningful during this period, either from an open connection to an explicit close connection (connection.close) or until the setabort,setcomplete is called.

Get self-increment primary key value after SQL Server inserts data

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.