HOW TO: Retrieve an Identity Value from a Newly Inserted Record from SQL Server by Using Visual C# .NET

Article translations Article translations
Article ID: 320897 - View products that this article applies to.
This article was previously published under Q320897
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article explains how to retrieve the identity value when you add a record into a SQL Server table with an identity field.

Requirements


This sample uses the Northwind database in SQL Server and retrieves the identity values only for DataTables with no child tables. This technique can also be used with MSDE; however, the Northwind database is not included with MSDE. For information about how to use this procedure with MSDE, see the Troubleshooting section of this article.

A SQL Server identity field is an auto number field for which you can define an incremental value. For this reason, you cannot insert or update a value in this field as long as Identity_ insert is off, which is the default for a SQL Server identity field.

Sample

  1. Start Visual Studio .NET, and then create a new Visual C# Windows Application project:
    1. On the File menu, point to New, and then click Project.
    2. In the New Project dialog box, click Visual C# Projects under Project Types, and then click Windows Application under Templates.
  2. Drag a Button onto the form from the Windows Forms toolbox.
  3. At the top of the code window add the following line:
    using System.Data.SqlClient;
  4. Paste the following code onto the Button's click event:
    string stConnection = "server=(local);integrated security=sspi;database=northwind";
    System.Data.SqlClient.SqlConnection cnNorthwind = new SqlConnection(stConnection);
    string stSelect = "select EmployeeID,FirstName,LastName from Employees";
    System.Data.SqlClient.SqlCommand cmSelect = new SqlCommand(stSelect,cnNorthwind);
    
    string stInsert;
    stInsert = "Insert into employees (firstName,LastName) values(@FirstName,@LastName);select EmployeeID,FirstName,LastName from Employees where EmployeeID = @@identity ";
    System.Data.SqlClient.SqlCommand cmInsert  = new SqlCommand(stInsert,cnNorthwind);
    cmInsert.Parameters.Add(new SqlParameter("@firstName",System.Data.SqlDbType.VarChar,25,"FirstName"));
    cmInsert.Parameters.Add(new SqlParameter("@LastName", System.Data.SqlDbType.VarChar,25,"LastName"));
    
    System.Data.SqlClient.SqlDataAdapter daNorthwind = new SqlDataAdapter(cmSelect);
    daNorthwind.InsertCommand = cmInsert;
    System.Data.DataSet dsNorthwind = new DataSet();
    daNorthwind.Fill(dsNorthwind,"Employees");
    			
    System.Data.DataRow dr;
    dr = dsNorthwind.Tables["Employees"].NewRow();
    dr[1] = "John";
    dr[2] = "Doe";
    dsNorthwind.Tables["Employees"].Rows.Add(dr);
    
    daNorthwind.Update(dsNorthwind,"Employees");
    dsNorthwind.AcceptChanges();
    for (int i=0;i<dsNorthwind.Tables["Employees"].Rows.Count - 1 ; ++i)
       {
       Console.WriteLine ("EmployeeID: " +    dsNorthwind.Tables["Employees"].Rows[i][0].ToString());
       Console.WriteLine ("Employee FirstName: " +    dsNorthwind.Tables["Employees"].Rows[i][1].ToString());
       Console.WriteLine ("Employee LastName: " +    dsNorthwind.Tables["Employees"].Rows[i][2].ToString());
       }
    					
  5. Change the connection string to reflect your SQL Server or MSDE information.
  6. Run the application.
  7. Click the Button.

    The information for the identity field of the newly inserted record is displayed.

Troubleshooting

Before you use this procedure with MSDE, you must use Data Transformation Services (DTS) to import the Northwind database from SQL Server or Microsoft Access.

REFERENCES

For additional information about how to use DTS, click the article number below to view the article in the Microsoft Knowledge Base:
242377 How to Use Data Transformation Services (DTS)

For additional information about converting a Microsoft Access database to SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
237980 HOW TO: Convert an Access Database to SQL Server


Properties

Article ID: 320897 - Last Review: February 27, 2014 - Revision: 4.5
APPLIES TO
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET 1.1
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft Data Engine 1.0
Keywords: 
kbnosurvey kbarchive kbhowtomaster KB320897

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com