SCOPE_IDENTITY from tableadapter schema

Posted on 2006-05-19
Last Modified: 2012-08-13
Hi All!

I am using a generated class from some tables into a visual studio 2005 project. Now, one of this tables uses a recently created id from another table. How can i get this value to insert it into this table?

I saw that the insert uses the scope_identity() but i couldnt see any way to recover this value.

thx in advance!
Question by:FLUXWIRE
    LVL 13

    Expert Comment

    Im not sure what you mean by "recover this value,

    --insert statement here.

    Declare   @identity int

        --INSERT INTO ...
        set @identity = SCOPE_IDENTITY()

       --Do whatever you want with @identity here.

    Your on target, do NOT use @@identity!

    Author Comment

    Hi. thx for your attention.

    this is how the insert is used by the tableadapter:

                this._adapter.InsertCommand = new System.Data.SqlClient.SqlCommand();
                this._adapter.InsertCommand.Connection = this.Connection;
                this._adapter.InsertCommand.CommandText = @"INSERT INTO [dbo].[OCORRENCIAS] ([id_tipo], [assunto], [dtpublicacao], [paginas], [documento]) VALUES (@id_tipo, @assunto, @dtpublicacao, @paginas, @documento);
    SELECT id_ocorrencia, id_tipo, assunto, dtpublicacao, paginas, documento FROM OCORRENCIAS WHERE (id_ocorrencia = SCOPE_IDENTITY())";
                this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
                this._adapter.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@id_tipo", System.Data.SqlDbType.Decimal, 0, System.Data.ParameterDirection.Input, 18, 0, "id_tipo", System.Data.DataRowVersion.Current, false, null, "", "", ""));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@assunto", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "assunto", System.Data.DataRowVersion.Current, false, null, "", "", ""));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@dtpublicacao", System.Data.SqlDbType.DateTime, 0, System.Data.ParameterDirection.Input, 0, 0, "dtpublicacao", System.Data.DataRowVersion.Current, false, null, "", "", ""));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@paginas", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "paginas", System.Data.DataRowVersion.Current, false, null, "", "", ""));
                this._adapter.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@documento", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "documento", System.Data.DataRowVersion.Current, false, null, "", "", ""));

    and this is the routine that i use to execute the query

            [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
            public virtual int Insert(decimal id_tipo, string assunto, System.Nullable<System.DateTime> dtpublicacao, string paginas, string documento) {
                this.Adapter.InsertCommand.Parameters[0].Value = ((decimal)(id_tipo));
                if ((assunto == null)) {
                    this.Adapter.InsertCommand.Parameters[1].Value = System.DBNull.Value;
                else {
                    this.Adapter.InsertCommand.Parameters[1].Value = ((string)(assunto));
                if ((dtpublicacao.HasValue == true)) {
                    this.Adapter.InsertCommand.Parameters[2].Value = ((System.DateTime)(dtpublicacao.Value));
                else {
                    this.Adapter.InsertCommand.Parameters[2].Value = System.DBNull.Value;
                if ((paginas == null)) {
                    this.Adapter.InsertCommand.Parameters[3].Value = System.DBNull.Value;
                else {
                    this.Adapter.InsertCommand.Parameters[3].Value = ((string)(paginas));
                if ((documento == null)) {
                    this.Adapter.InsertCommand.Parameters[4].Value = System.DBNull.Value;
                else {
                    this.Adapter.InsertCommand.Parameters[4].Value = ((string)(documento));
                System.Data.ConnectionState previousConnectionState = this.Adapter.InsertCommand.Connection.State;
                if (((this.Adapter.InsertCommand.Connection.State & System.Data.ConnectionState.Open)
                            != System.Data.ConnectionState.Open)) {
                try {
                    int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery();
                    return returnValue;
                finally {
                    if ((previousConnectionState == System.Data.ConnectionState.Closed)) {

    this is generated by the wizard.

    the insert routine returns the number of the rows affected.
    how can i get (sorry about the recover) the SCOPE_IDENTITY?

    Thx in advance!
    LVL 13

    Expert Comment

    you should without a doubt create a stored procedure for this..

    it would like like

    //pseudo tsql

    dbo.sprocname {
         @param1 int,
         @param2 varchar(50),
         @param3 datetime
       insert into mytable values(@param1, @param2);
       insert into mytable2 values(scope_identity(), @param3);
       //you could return scope_identity with return statement or out param.

    Author Comment

    k. let me ask you a workaround for it.
    in the generated tableadapter class, could i do this?
    1. create a new int variable, private,
    2. put another select in the same command text, like
    SET variable = SCOPE_IDENTITY()";
    3. return this variable in the insert method.

    if i could do this, i could use it. i dont know how could i do the second item.

    thx in advance.


    Author Comment

    i got it!

    after you use the adapter.update(); the related table has the last record. all i need to do is table.Rows[0][0] and i got the primary key.

    Look at commandtext. we have 2 commands. the first one is the insert and in the sequence, the select.


    Accepted Solution

    Closed, 500 points refunded.
    Community Support Moderator

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Article by: Ivo
    C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
    Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now