How to retrieve the primary key (identity key) value?

Hi,

I am updating a new record into my MSSQL 2005 database from my VBA code using the recordset.update method.
As soon as I update the record, I wish to retrieve the auto generated identity key value which is my primary key. How can i do this? Below is the code snippet of what I am doing but it returns a null all the time.

Thanks in Advance.
vConnection.ConnectionString = "Provider=SQLNCLI;Connection Timeout=90;Server=blablabla;Database=blablabla; Trusted_Connection=yes;"
vConnection.Open
 
                 vRecordSet.Open "[Test Sum RPT]", vConnection, , adLockOptimistic, adCmdTable
vRecordSet.AddNew
If vProjectID <> "" Then vRecordSet!project_id = vProjectID
                If vProjectName <> "" Then vRecordSet!Project_name = vProjectName
                If vProjectMgr <> "" Then vRecordSet!Project_manager_name = vProjectMgr
 
vRecordSet.Update
 
dim vreportid1 as string
 
 vreportid1 = vRecordSet!report_id ' ( I also tried vreportid1=vRecordSet("report_id") and also vreportid1=vRecordSet(0)
 
vRecordSet.Close

Open in new window

vdhavalaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
latest at this stage, it will be clear that using a stored procedure will make the code MUCH easier, in the end.

anyhow:
'vreportid1 = vRecordSet!report_id

vreportid1 = vConnection.Execute("select scope_identity() r").Fields("r").Value
0
chapmandewCommented:
an update doesn't generate an ID, an insert does.  at any rate, you can use any of the following

select scope_identity() --preferred

select @@identity

use the OUTPUT statement
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
chapmandew:

vRecordSet.AddNew
If vProjectID <> "" Then vRecordSet!project_id = vProjectID
                If vProjectName <> "" Then vRecordSet!Project_name = vProjectName
                If vProjectMgr <> "" Then vRecordSet!Project_manager_name = vProjectMgr
 
vRecordSet.Update


the recordset.update actually runs a INSERT, as previously there is a .AddNew for the recordset object...
FYI
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

vdhavalaAuthor Commented:
I'm sorry but Could you be a little more clear ? I tried

vreportid1 = vConnection.Execute("select scope_identity() r").Fields("r").Value

and it gives me an error, "Invalid use of Null"

What is r in the above statement ? do I have to replace it with report_id or something? sorry..I'm very bad at this...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you get a invalid use of null, then you have the problem that you HAVE to work with a stored procedure, as the ado .AddNew/.Update results in the non-usability of the SCOPE_IDENTITY() function.
OR
your table has no IDENTITY field?


the "r" is a alias for the column returned, so that the Fields("r") will fetch that field.

0
vdhavalaAuthor Commented:
My table has report_id set as primary key and is the identity coloumn also. After the update statement, I checked the database and it is adding a new record with a unique report_id value each time. But when I try to retrieve it is when I have the problem.

Regarding stored procedures, I have never done this before and so I dread it. how difficult is it? I will need your help if there is not other choice I am left with !

Please help..
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
plain easy, actually:
CREATE PROCEDURE dbo.InsertNewProject
( @ProjectName varchar(300)
, @ProjectMgr varchar(300)
, @ProjectID int OUTPUT
)
AS
 SET NOCOUNT ON
 INSERT INTO [Test Sum RPT] (ProjectName, ProjectMgr)
 VALUES( @ProjectName, @ProjectMgr)
 
 SET @ProjectID = SCOPE_IDENTITY()

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
second step: use that procedure in your vb code
dim cmd as adodb.command
 
set cmd = new adodb.command
set cmd.ActiveConnection = vConnection
cmd.Parameters.Refresh 'this is not the optimal method, but for starters is fine...
 
cmd.Parameters("@ProjectName").Value = vProjectName
cmd.Parameters("@ProjectMgr ").Value = vProjectMgr
 
cmd.Execute
 
vreportid1 = cmd.Parameters("@ProjectID ").Value

Open in new window

0
vdhavalaAuthor Commented:
In your first step @ProjectName varchar(300) @ProjectMgr varchar(300 etc, should these names and size be as per what is defined in the database ?

What I am asking is ProjectName should be a field in the database with size varchar(300) ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes
0
vdhavalaAuthor Commented:
Sorry angellll...If this question of mine irritates you. But I already have working code that inserts records into the database. This was the code I developed for MSAccess db. Now shifting to Stored procedure will change this entire code. Is there a way I can do this without changing the code ?

Sorry once again for this.
0
vdhavalaAuthor Commented:
Ok .. in the SP that you suggested..

INSERT INTO [Test Sum RPT] (ProjectName, ProjectMgr)
 VALUES( @ProjectName, @ProjectMgr)
 
 SET @ProjectID = SCOPE_IDENTITY()

what are the values ProjectName, ProjectMgr etc ? - Are they just some names or are they same as DB Names? Also @ProjectName,@ProjectMrg are same as what is specified above in the SP ?

Thanks for your patience.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is there a way I can do this without changing the code ?
sql server and access are playing in a different league. you cannot expect to write high-level code working for both in all cases (or any other db, for the matter)


Now, ProjectName should be Project_name (name of the field in the table)
ProjectMgr should be Project_manager_name (same).

the @variable names are free to be chosen, ie in the CREATE PROCEDURE header you define the names, and use them in the body.
0
DimitrisSenior Solution ArchitectCommented:
try this

SQL = "INSERT INTO TABLE MyTable (Col1) VALUES ('My Value'); SELECT IDENT_CURRENT('MyTable')"

dim rst as ADODB.Recordset
dim rstID_Value as ADODB.Recordset
SET rst = vConnection.Execute(SQL) -- here it executes the insert and the retriave of the identity column
set rstID_Value = rst.NextRecordset

Msgbox rstID_Value.Fields(0).Value -- and you have your ID

Please notice that you are performing an insert and not an update because you call Recordset.AddNew method.
0
Anthony PerkinsCommented:
You should learn the difference between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT()

<quote>
IDENT_CURRENT is similar to the Microsoft® SQL Server" 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
</quote>

In my view angeliii is correct and in your case you should be using SCOPE_IDENTITY() as IDENT_CURRENT() is global and too broad.
0
mironCommented:
did you try to follow suggestion in this KB article:

http://support.microsoft.com/kb/195224

-- cheers
0
DimitrisSenior Solution ArchitectCommented:
I sure agree with you that IDENT_CURRENT returns the last ID of the table across all scopes
BUT the issue is that you always run in a transaction....
So? who will be able to insert an other record to the table ?? you have already lock it all the others wait on the queue.
So IDENT_CURRENT is your identity .. and it is also safer because
If let's say you have a table named "Table1" and on that table you have created an audit trigger that inserts also the change action in a AUDIT database
The Audit table has also an AUTOINCREMENT column
So If you do the insert the trigger will run and it will insert a row in AUDIT Table.
So @@IDENTITY and SCOPE_IDENTITY() will return THE VALUE OF THE AUTOINCREMENT COLUMN OF THE AUDIT DATABASE!
So IDENT_CURRENT is safer...
And you never perform an insert without transaction...

Have a nice day, and as you can see i have did a lot of reading about this issue.
But thanks for the advice anyway :)
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
dankangr:
>  So? who will be able to insert an other record to the table ?? you have already lock it all the others wait on the queue.

 that's wrong. you can insert several people at the "same time", but that's not the real problem. it's the time between the INSERT and the SELECT to fetch the identity, during which other CAN do more inserts.

so, in short NEVER use IDENT_CURRENT !!!!!

0
vdhavalaAuthor Commented:
Angellll.....I tried with the stored proc and it gives me a syntax error near int. I am not able to figure out what is wrong. I am attaching the stored proc here. Please can you check for me ? Thanks in Advance.


CREATE PROCEDURE dbo.InsertTestSumRecord
 (@project_id nvarchar(5) 
, @project_name nvarchar(50)
, @project_magr_name nvarchar(20)
, @preparedby_name nvarchar(20)
, @ite_num nvarchar(1)
, @rpt_date datetime
, @Recommendations smallint
, @reco_rea nvarchar(255)
, @reco_rea_2 nvarchar(255)
, @test_asse nvarchar(255)
, @test_asse_2 nvarchar(255)
, @reg_test_per smallint
, @ver_closed_quant smallint
, @ver_closed_com nvarchar(60)
, @def_quant smallint
, @def_comment nvarchar(60)
, @others_quant smallint
, @others_comment nvarchar(60)
, @test_exec_dur_estd smallint
, @test_exec_dur_act smallint
, @test_exec_dur_rea nvarchar(255)
, @Appl_name nvarchar(50)
, @del_center nvarchar(30)
, @test_sum_actual smallint
, @test_sum_plan smallint
, @test_sum_comm nvarchar(255)
, @Release_type nvarchar(30)
, @report_id int OUTPUT
)
AS
 SET NOCOUNT ON
 INSERT INTO [Test Summary RPT] (project_id,project_name,project_manager_name,preparedby_name,iteration_num,rpt_date,Recommendations,recommendation_reason,recommendation_reason_2 ,test_assessment, test_assessment_2,regression_test_percent , verified_closed_quantity,verified_closed_comment , deferred_quantity,deferred_comment , all_others_quantity,all_others_comment , test_execution_duration_estimated,test_execution_duration_actual, test_execution_duration_reason,Application_name , delivery_center, 
Reg_test_summ_actual,Reg_test_summ_planned,Reg_test_summ_comments,Release_type, report_id int OUTPUT)
 VALUES( @project_id,@project_name,@project_manager_name,@preparedby_name,@iteration_num,@rpt_date,@Recommendations,@recommendation_reason,@recommendation_reason_2 ,@test_assessment, @test_assessment_2,@regression_test_percent ,@verified_closed_quantity,@verified_closed_comment,@deferred_quantity,@deferred_comment,@all_others_quantity,@all_others_comment,@test_execution_duration_estimated,@test_execution_duration_actual,@test_execution_duration_reason,@Application_name,@delivery_center, 
@Reg_test_summ_actual,@Reg_test_summ_planned,@Reg_test_summ_comments,@Release_type,@report_id)
 
 SET @report_id = SCOPE_IDENTITY()

Open in new window

0
vdhavalaAuthor Commented:
Sorry..I pasted the wrong one. Here is the correct one that I am working on.
CREATE PROCEDURE dbo.InsertTestSummRecord
 (@project_id nvarchar(5) 
, @project_name nvarchar(50)
, @project_manager_name nvarchar(20)
, @preparedby_name nvarchar(20)
, @iteration_num nvarchar(1)
, @rpt_date datetime
, @Recommendations smallint
, @recommendation_reason nvarchar(255)
, @recommendation_reason_2 nvarchar(255)
, @test_assessment nvarchar(255)
, @test_assessment_2 nvarchar(255)
, @regression_test_percent smallint
, @verified_closed_quantity smallint
, @verified_closed_comment nvarchar(60)
, @deferred_quantity smallint
, @deferred_comment nvarchar(60)
, @all_others_quantity smallint
, @all_others_comment nvarchar(60)
, @test_execution_duration_estimated smallint
, @test_execution_duration_actual smallint
, @test_execution_duration_reason nvarchar(255)
, @Application_name nvarchar(50)
, @delivery_center nvarchar(30)
, @Reg_test_summ_actual smallint
, @Reg_test_summ_planned smallint
, @Reg_test_summ_comments nvarchar(255)
, @Release_type nvarchar(30)
, @report_id int OUTPUT
)
AS
 SET NOCOUNT ON
 INSERT INTO [Test Summary RPT] (project_id,project_name,project_manager_name,preparedby_name,iteration_num,rpt_date,Recommendations,recommendation_reason,recommendation_reason_2 ,test_assessment, test_assessment_2,regression_test_percent , verified_closed_quantity,verified_closed_comment , deferred_quantity,deferred_comment , all_others_quantity,all_others_comment , test_execution_duration_estimated,test_execution_duration_actual, test_execution_duration_reason,Application_name , delivery_center, 
Reg_test_summ_actual,Reg_test_summ_planned,Reg_test_summ_comments,Release_type, report_id int OUTPUT)
 VALUES( @project_id,@project_name,@project_manager_name,@preparedby_name,@iteration_num,@rpt_date,@Recommendations,@recommendation_reason,@recommendation_reason_2 ,@test_assessment, @test_assessment_2,@regression_test_percent ,@verified_closed_quantity,@verified_closed_comment,@deferred_quantity,@deferred_comment,@all_others_quantity,@all_others_comment,@test_execution_duration_estimated,@test_execution_duration_actual,@test_execution_duration_reason,@Application_name,@delivery_center, 
@Reg_test_summ_actual,@Reg_test_summ_planned,@Reg_test_summ_comments,@Release_type,@report_id)
 
 SET @report_id = SCOPE_IDENTITY()

Open in new window

0
vdhavalaAuthor Commented:
btw, report_id is my identity that I wish to retrieve after I insert a record.

Once again..I am very new to this and am trying it for the first time. Appreciate you patience!
0
vdhavalaAuthor Commented:
Increasing the points and this has gone beyond my expectation !
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your code is slightly wrong;
 SET NOCOUNT ON
 INSERT INTO [Test Summary RPT] (project_id,project_name,project_manager_name,preparedby_name,iteration_num,rpt_date,Recommendations,recommendation_reason,recommendation_reason_2 ,test_assessment, test_assessment_2,regression_test_percent , verified_closed_quantity,verified_closed_comment , deferred_quantity,deferred_comment , all_others_quantity,all_others_comment , test_execution_duration_estimated,test_execution_duration_actual, test_execution_duration_reason,Application_name , delivery_center, 
Reg_test_summ_actual,Reg_test_summ_planned,Reg_test_summ_comments,Release_type)
 VALUES( @project_id,@project_name,@project_manager_name,@preparedby_name,@iteration_num,@rpt_date,@Recommendations,@recommendation_reason,@recommendation_reason_2 ,@test_assessment, @test_assessment_2,@regression_test_percent ,@verified_closed_quantity,@verified_closed_comment,@deferred_quantity,@deferred_comment,@all_others_quantity,@all_others_comment,@test_execution_duration_estimated,@test_execution_duration_actual,@test_execution_duration_reason,@Application_name,@delivery_center, 
@Reg_test_summ_actual,@Reg_test_summ_planned,@Reg_test_summ_comments,@Release_type)
 
 SET @report_id = SCOPE_IDENTITY()

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vdhavalaAuthor Commented:
Ok..It executed successfully in my Query Window. So now the SP is there in my SQL Server right? Or Do I have to do anything else to save it in my Server ?

Can I now go ahead and call it from my VB code now that it is executed ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>So now the SP is there in my SQL Server right?
yes. hopefully in the correct db?!

>Can I now go ahead and call it from my VB code now that it is executed ?
yes, that should be possible.
you might need to grant permissions, but if you connect with a "dbo" account...
0
vdhavalaAuthor Commented:
Three queries:

1) When I say cmd.execute, how does it know that I have to execute the stored procedure ? Where am I specifying the name ?

2) I tried executing the VB code and it gives me an error "item cannot be found in collection". Attaching the code for your reference. Please can you check if i am doing it right ?

3) in the DB where do I look to see if the SP is stored ? W\hen I reexecuted, it gave me an error that a SP with this name already exists but I was not able to locate it.

Thanks again for your patience.
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = vConnection
cmd.Parameters.Refresh 
 
cmd.Parameters("@project_id").Value = vProjectID
cmd.Parameters("@project_name").Value = vProjectName
cmd.Parameters("@project_manager_name").Value = vProjectMgr
cmd.Parameters("@preparedby_name").Value = vPreparedby
cmd.Parameters("@iteration_num").Value = vIteration
cmd.Parameters("@rpt_date").Value = vCurDate
cmd.Parameters("@Recommendations").Value = vReco4
cmd.Parameters("@recommendation_reason").Value = vRecoReason1
cmd.Parameters("@recommendation_reason_2").Value = vRecoReason2
cmd.Parameters("@test_assessment").Value = vTestAssessment1
cmd.Parameters("@test_assessment_2").Value = vTestAssessment2
cmd.Parameters("@regression_test_percent").Value = vRegTestPercent
cmd.Parameters("@verified_closed_quantity").Value = vVerClosedQty
cmd.Parameters("@verified_closed_comment").Value = vVerClosedComment
cmd.Parameters("@deferred_quantity").Value = vDeferredQuantity
cmd.Parameters("@deferred_comment").Value = vDeferredComments
cmd.Parameters("@all_others_quantity").Value = vAllOtherQuantity
cmd.Parameters("@all_others_comment").Value = vAllOtherComments
cmd.Parameters("@test_execution_duration_estimated").Value = vTestDurationEstimated
cmd.Parameters("@test_execution_duration_actual").Value = vTestDurationActual
cmd.Parameters("@test_execution_duration_reason").Value = vTestExecDurReason
cmd.Parameters("@Application_name").Value = vApplicationName
cmd.Parameters("@delivery_center").Value = vDeliveryCenter
cmd.Parameters("@Reg_test_summ_actual").Value = vRegTestSummActual
cmd.Parameters("@Reg_test_summ_planned").Value = vRegTestSummPlanned
cmd.Parameters("@Reg_test_summ_comments").Value = vRegTestSummComments
cmd.Parameters("@Release_type").Value = vReleaseType
 
 
cmd.Execute
 
vreportid1 = cmd.Parameters("@report_id ").Value
    
       vRecordSet.Close

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1)  there was that line missing:

dim cmd as adodb.command
 
set cmd = new adodb.command
set cmd.ActiveConnection = vConnection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = 'dbo.InsertTestSummRecord'

cmd.Parameters.Refresh 'this is not the optimal method, but for starters is fine...
 
cmd.Parameters("@ProjectName").Value = vProjectName
cmd.Parameters("@ProjectMgr ").Value = vProjectMgr
 
cmd.Execute
 
vreportid1 = cmd.Parameters("@ProjectID ").Value
0
vdhavalaAuthor Commented:
Ok..Atlast..my SP executed and am able to add a record through my VB code. However, I still cant retrieve the Primary key value. It says " Item cannot be found in the collection corresponding to the requested name or ordinal" at the line

vreportid1 = cmd.Parameters("@report_id ").Value


my unique identifier column name is report_id and I have used this in the SP also for  SET @report_id = SCOPE_IDENTITY()

Is this correct? What is going wrong here ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what if you did this:
vreportid1 = cmd.Parameters("@report_id").Value
 
or:
 
 
vreportid1 = cmd.Parameters("report_id").Value

Open in new window

0
vdhavalaAuthor Commented:
no..I get the same error :(
0
vdhavalaAuthor Commented:
Ok..Got it. I had a space before the closing quotes. Thanks a ton ! You were really so patient. I wish there were points more than 500 that I could assign for your help, knowledge and patience.

Regards !
0
Anthony PerkinsCommented:
dankangr,

>>Have a nice day, and as you can see i have did a lot of reading about this issue.
But thanks for the advice anyway :)
<<
I am sorry you took it personally, but my comment was directed at the author of this thread, who I believe is trying to learn to do it correctly.
0
DimitrisSenior Solution ArchitectCommented:
angelIII ==>
have you ever work with money???
when the transaction is important??? when you don't wont to loose a single row?
when you don't want to have hangups in the middle of a multi insert statement???
Probably no. Because if you have worked you would know that a transaction is required.
And my friend from there on is just performance, write optimized queries don't lock your tables for a lot of time etc....
But as you say never user IDENT_CURRENT(TableName) then try to do an audit table and an audit trigger and try to get the ID ...
You will use IDENT_CURRENT because scope_identity will have the ID of the audit table :)
(This is called Historic Data Importance just to know, you need to keep track of everything because the records IS MONEY)

acperkins==> No i didn't took it personally don't worry, and i think that it is mater of different perspectives about how to develop an application and the importance of it's data, I work with major applications Banking mostly (not e-banking, Bank Organisations) and the significant of the transaction and all the logic is very important.
My prespective :)

But it's good to have discusions. you learn somthing each time. :-)

Thank you both and no offence taken and I'd like to think no offence given (If so then i'm sorry i didn't wanted to I just always support my point of view sometimes with a quite strong way )

Cheers

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
dankangr:

  yes, I work with money, I am a "billing engineer", ie all applications managed/developed around the invoicing stuff for all our customers.

 and I know/agree that transactions are important. however, transaction, unless you used the most limiting transaction isolation level (serializable), ident_current is the wrong choice.

 >But as you say never user IDENT_CURRENT(TableName) then try to do an audit table and an audit trigger and try to get the ID ...
 no. that was the case with @@IDENTITY, but NOT with SCOPE_IDENTITY() function.
  please read up the documentation, ie test out this script:


create table data ( id int identity(1,1) , value varchar(10))
go
create table audit ( audit_id int identity(1001,1), data_id int, data_value varchar(10))
go
create trigger data_audit on data for insert, update
as
 insert into audit ( data_id, data_value )
  select id, value from inserted
go
 
insert into data (value) values( 'row1')
select @@identity, scope_identity(), ident_current('data')
 
go
drop table data
go
drop table audit
 
 
result:
1001	1	1
 
@@identity returned the id of the audit table, scope_identity worked correctly.
I added the ident_current() function to show that is does here, in the single user setting, return the same value.

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
next test: start 2 query windows, an run the following 2 steps:


set transaction isolation level serializable
begin transaction
insert into data (value) values( 'row1')


when that is done on both windows, run this line:

select @@identity, scope_identity(), ident_current('data')

results, in window 1:
1001   1      2      

results, in window 2:
1002      2      2

as you can see, the transaction did NOT prevent ident_current to get the WRONG identity in window 1, ie the identity value generated by window 2.

Please update your "docs".
0
Anthony PerkinsCommented:
dankangr,

>>But as you say never user IDENT_CURRENT(TableName) then try to do an audit table and an audit trigger and try to get the ID ...
You will use IDENT_CURRENT because scope_identity will have the ID of the audit table<<

This is plainly incorrect.  You are clearly confusing SCOPE_IDENTITY() with @@IDENTITY
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.