Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SCOPE_IDENTITY from tableadapter schema

Posted on 2006-05-19
Medium Priority
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
  • 3
  • 2
LVL 13

Expert Comment

ID: 16717225
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

ID: 16717620
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

ID: 16718203
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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 16718813
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

ID: 16728003
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

CetusMOD earned 0 total points
ID: 16823040
Closed, 500 points refunded.
Community Support Moderator

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

580 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