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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1399
  • Last Modified:

SCOPE_IDENTITY() question.

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
Rush_2112
Asked:
Rush_2112
  • 7
  • 6
  • 5
  • +4
2 Solutions
 
peh803Commented:
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
 
OMC2000Commented:
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
 
Rush_2112Author Commented:
Error Type:
Microsoft VBScript runtime (0x800A01B6)
Object doesn't support this property or method: 'requery'
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
peh803Commented:
0
 
Rush_2112Author Commented:
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
 
fritz_the_blankCommented:
Just as an aside, I thought that you needed a ; after the insert and before the select to separate the statements.

FtB
0
 
Rush_2112Author Commented:
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
 
fritz_the_blankCommented:
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ");" &_
"SELECT SCOPE_IDENTITY() AS NewID;"
0
 
Anthony PerkinsCommented:
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
 
HilaireCommented:
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
 
Rush_2112Author Commented:
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
 
Anthony PerkinsCommented:
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
 
Rush_2112Author Commented:
That was it!!  thank you so much!
0
 
fritz_the_blankCommented:
So you did need the semicolon like I suggested!!!! I've been robbed!!!!!!!!!!!!!!!!!

FtB

;->

0
 
Anthony PerkinsCommented:
fritz,

Sorry about that.  Do you need some points? :)
0
 
fritz_the_blankCommented:
Nah, I have enough! I was directing the comment more towards the person posting the question.

Thanks for the offer though,

FtB

0
 
Rush_2112Author Commented:
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
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
>> 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
 
Rush_2112Author Commented:
I'll agree to that!  Or he can just split them himself, whichever is easier for you guys, you're the experts!  :)
0
 
fritz_the_blankCommented:
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
 
bpbarberhelpCommented:
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
 
Anthony PerkinsCommented:
>>Any advise would be greatly appreciated<<
You will find experts more responsive if you post a new question.
0

Featured Post

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!

  • 7
  • 6
  • 5
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now