Solved

SCOPE_IDENTITY() question.

Posted on 2004-09-20
23
1,273 Views
Last Modified: 2008-02-01
Hi all,

I've been through the existing questions on SCOPE_IDENTITY() and I can't quite get it to work.  Hoping someone could help.
I'm inserting field data into a SQL 2000 DB, and I want to immediately get that SCOPE_IDENTITY() value and display it.  

I have the following code, but now how do I display that value, a Response.write(NewID) isn't working......
 
---------------------------------------------------------------------------------------------------------------------------
 MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")" &_
        "SELECT SCOPE_IDENTITY() AS NewID;"
            
 If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close
----------------------------------------------------------------------------------------------------------------------------

Thanks in advance!
0
Comment
Question by:Rush_2112
  • 7
  • 6
  • 5
  • +4
23 Comments
 
LVL 19

Expert Comment

by:peh803
ID: 12105626
are you open to other solutions besides using SCOPE_IDENTITY()?
If so, you could do something like this to get the ID you just created...

<%
Dim rsInsert
Dim sSQL
Dim sNewId
set rsInsert = server.createobject("adodb.recordset")
sSQL = "SELECT * FROM " & MM_editTable
rsInsert.open sSQL, MM_editConnection, 1, 3, 1
rsInsert.addnew
rsInsert("field1") = sValue1
rsInsert("field2") = sValue2
rsInsert("field3") = sValue3
rsInsert("field4") = sValue4
rsInsert.update
rsInsert.requery
rsInsert.movelast
sNewId = rsInsert("MyID")
rsInsert.close
set rsInsert = nothing
response.write "My New ID is: " &sNewID
%>

peh803
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 12105707
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")" &_
        "SELECT SCOPE_IDENTITY() AS NewID;"
         
 If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    oRs =  MM_editCmd.Execute

' these code is in JavaScript, but I guess you easily convert it to VBScript
    while( ! oRs.State ) oRs = oRs.NextRecordset

     key = oRs(0).Value;
    oRs.close();
    return key;
' these code in JavaScript

    MM_editCmd.ActiveConnection.Close

0
 
LVL 1

Author Comment

by:Rush_2112
ID: 12105784
Error Type:
Microsoft VBScript runtime (0x800A01B6)
Object doesn't support this property or method: 'requery'
0
 
LVL 19

Expert Comment

by:peh803
ID: 12105821
0
 
LVL 1

Author Comment

by:Rush_2112
ID: 12105832
Did I do something wrong:

 MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")" &_
        "SELECT SCOPE_IDENTITY() AS NewID;"
            
 If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
MM_editCmd.requery
MM_editCmd.movelast
sNewId = MM_editCmd("MyID")
MM_editCmd.close
set MM_editCmd = nothing
response.write "My New ID is: " &sNewID
    MM_editCmd.ActiveConnection.Close
      
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12105837
Just as an aside, I thought that you needed a ; after the insert and before the select to separate the statements.

FtB
0
 
LVL 1

Author Comment

by:Rush_2112
ID: 12105880
How would that go?  When I do this:

  MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")" &_;"SELECT SCOPE_IDENTITY() AS NewID;"

It returns a syntax error
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12105958
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ");" &_
"SELECT SCOPE_IDENTITY() AS NewID;"
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 12109061
Also, change your ASP code to match the fact you are returning a recordset:

Dim rs

If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    ' Always a good idea to tell ADO explicitly what you want
    MM_editCmd.CommandType = 1                'adCmdText
    Set rs = MM_editCmd.Execute
    response.write "My New ID is: " & rs.Fields("NewID").Value
    rs.Close                                                   'Always a good idea to close it
    Set rs = Nothing                                       'Always a good idea to set it to nothing
    MM_editCmd.ActiveConnection.Close
    Set MM_editCmd = Nothing
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12109695
Of course,
all the code samples above assume that you have defined an auto-incremental int/bigint column in your table :

If it's not the case, you'll have to create a new col in Enterprise Manager
It should look like this :

Name    Datatype          Properties
ID         int (or bigint)    identity=yes

You can also create the new col in Query Analyser
Alter table <YourTable> add ID int identity

identity defaults to identity(1,1) ie start numbering at 1, increment by 1
You can use
Alter table <YourTable> add ID int identity(100, 2) if it's more convenient for you (start at 100, increment by 2)

Maybe you have already created this column, if so, please forgive me for this off-topic post. I thought this was worth a check ...

Hilaire
0
 
LVL 1

Author Comment

by:Rush_2112
ID: 12111442
For ACPERKINS especially (But anyone else too!)  **Increasing points!**

Getting the folowing error message after adding that code:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

Here's the code as I have it:
-----------------------------------------

MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ");" &_
"SELECT SCOPE_IDENTITY() AS NewID;"

 Dim rs

If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    ' Always a good idea to tell ADO explicitly what you want
    MM_editCmd.CommandType = 1                'adCmdText
    Set rs = MM_editCmd.Execute
    response.write "My New ID is: " & rs.Fields("NewID").Value
    rs.Close                                                   'Always a good idea to close it
    Set rs = Nothing                                       'Always a good idea to set it to nothing
    MM_editCmd.ActiveConnection.Close
    Set MM_editCmd = Nothing
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 12112236
Change this:
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ");" &_
"SELECT SCOPE_IDENTITY() AS NewID;"

To:
MM_editQuery = "SET NOCOUNT ON;insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ");" &_
"SELECT SCOPE_IDENTITY() AS NewID;"
0
 
LVL 1

Author Comment

by:Rush_2112
ID: 12112642
That was it!!  thank you so much!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12114515
So you did need the semicolon like I suggested!!!! I've been robbed!!!!!!!!!!!!!!!!!

FtB

;->

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12115172
fritz,

Sorry about that.  Do you need some points? :)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12115252
Nah, I have enough! I was directing the comment more towards the person posting the question.

Thanks for the offer though,

FtB

0
 
LVL 1

Author Comment

by:Rush_2112
ID: 12115432
Ack!  I'm sorry, I meant to split the points between you 2, looks like I split them between 2 posts from the same guy!  Sorry again, I'm new here! :(
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12115467
>>Nah, I have enough!<<

I know.  I was only kidding with you.  But you are right the points should have been at least split.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12115484
>> I'm sorry, I meant to split the points between you 2<<
Fritz, can do that for you, he is pretty much God here :)  Just ask to have the question re-opened.
0
 
LVL 1

Author Comment

by:Rush_2112
ID: 12115633
I'll agree to that!  Or he can just split them himself, whichever is easier for you guys, you're the experts!  :)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12115956
It's all fine--I was just feeling left out is all.

I am glad that your problem is solved.

See you around,

FtB
0
 

Expert Comment

by:bpbarberhelp
ID: 14136337
I am VERY new to .asp and SQL programming.  I am currently working on a web-app project and need to find a method in which to referrence the last number in my UniqueID field of table 1 and then increment that number and INSERT it into my UniqueID field.  Here is the problem that I am experiencing.  I (to the best of my knowledge) cannot use autonumber with an insert command because this field is going to be used in a related table and referenced to Inner join these 2 tables.  If I use Autonumber data type in the UniqueID field of table1 and enter this value into table2 as a number data type I can not join the 2 tables using this field due to the difference in datatypes.

Therefore I am hoping to find a method so that I may leave the UniqueID field in table 1 a number data type; query the last number in this field, increment it and Insert it into a new data row.  I have tried unsuccessfuly to use Scope_Identity() and @@Identity() unsuccessfully. I am currently creating this page using an access DB.

Any advise would be greatly appreciated.  Please try to keep it as simple as possible so that I have some hopes of understanding things.

Thank You
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14136355
>>Any advise would be greatly appreciated<<
You will find experts more responsive if you post a new question.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

10 Experts available now in Live!

Get 1:1 Help Now