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

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

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

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

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...

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.

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..

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()

Select allOpen in new window

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

Select allOpen in new window

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) ?

yes

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.

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.

>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.

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.

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.

did you try to follow suggestion in this KB article:

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

-- cheers

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 :)

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 !!!!!

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()

Select allOpen in new window

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()

Select allOpen in new window

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!

Increasing the points and this has gone beyond my expectation !

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 ?

>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...

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

Select allOpen in new window

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

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 ?

what if you did this:

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

Select allOpen in new window

no..I get the same error :(

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 !

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.

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

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.

Select allOpen in new window

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".

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