?
Solved

SCOPE_IDENTITY() question.

Posted on 2004-09-20
23
Medium Priority
?
1,368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 800 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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
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/…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

771 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