Yes, this method is good too.
I was using it as first for some time but now I prefer the recorset
method as it somewhat easier to use when you have a lengthy recordset or one
that is frequently modified in the development stage but some people will
argue that this involve a supplemental call to the SQL-Server; will
personnally I don't mind for most of my projects.
The procedure you havve described below can also lead to an error if
insert triggers are used, as they are inside the transaction and can change
the value of the @@identity before it is captured. Personnally, I have
never verified this last point as I don't use any kind of trigger anywhere
(I don't like them because they add some sort of "magic coding" to the
transact statements, with the effect of having the overall picture becoming
very hard to decrypt for others people. I prefer to have only one clear
layer of code above my data instead of two: one above and a second one
hidden below.)
More important, you shouldn't use the 'sp_" prefixe, as it has special
meaning for SQL-Server and will lead to less than efficient compiled code in
your case. Here the relevant excerpt from the BOL, it's in french but you
will find the same thing in the english version:
« Il est fortement recommandé de ne pas créer de procédures stockées avec le
préfixe sp_. SQL Server recherche toujours une procédure stockée en
commençant par sp_ dans l'ordre suivant :
1.. elle existe dans la base de données master ;
2.. ensuite, en fonction des éventuels identificateurs fournis (nom de
base de données ou propriétaire) ;
3.. enfin, avec dbo comme propriétaire si aucun propriétaire n'est
spécifié.
Par conséquent, bien qu'il puisse exister dans la base de données en cours
une procédure stockée créée par l'utilisateur ayant le préfixe sp_, la base
de données master est toujours analysée la première, même si la procédure
stockée est qualifiée avec le nom de la base de données. »
Finally, you are returning the @@identity in two different locations: in
the variable @num, who is declared as OUTPUT, and in the return value for
the procedure sp_Test with "return @Num". By itself it's not a bug, but it
can lead to some nasty bug to track down if one programmer, after seeing the
"return @Num", jump to the conclusion that no input parameter may have been
modified when the SP exit. By personnal experience, I have found - the hard
way - that every liberty degree you give is an apportunity for a bug to make
it its nest.
S. L.
TF said:
I use a stored procedure t insert a record, in the Sp I return the
@@identity, like
CREATE PROCEDURE sp_Test(@Num INT OUTPUT, @Label VARCHAR(255))
AS
SET nocount ON
BEGIN TRANSACTION
INSERT INTO Units (Unit) VALUES(@Label)
SELECT @Num = @@IDENTITY
COMMIT TRANSACTION
return @Num
Sylvain Lafontaine said:
When I don't need a stored procedure for some special treatment, using an
ADODB recordset is often an easier way to insert data and get back the
newid. For example, on a SQL-Server, use something like:
sql = " SELECT * FROM Reports WHERE 1=0 "
rs.Open sql, ADO_ConnectionObject, adOpenKeyset, adLockOptimistic,
adCmdText
rs.AddNew
rs ("Name") = NameOfClient
rs ("Phone") = PhoneOfClient
rs.Update
IdReport = rs ("IdReport") ' Autoincrement is here.
rs.close
Notice the utilisation of the "where 1=0" to get back an empty
recordset. If your recordset is not empty, you must use sometime a bookmark
to return to the record after the update:
bookmark = rs.bookmark
rs.Update
rs.bookmark = bookmark
IdReport = rs ("IdReport") ' Autoincrement is here.
Things may be different if you are using a server-side or client-side
recordset (rs.CursorLocation = adUseServer or adUseClient), if you are
on
an
Access database or if are using a recordset of another type then a KeySet.
Often, I don't get it right the first time. (In the above exemple, I have
dropped the code for the creation of the connection and the recordset
objects. Use whatever you want for this.)
If you use DAO instead of ADO, then some minor syntax changes are
needed, like the fact that the newid will be available before the update,
not after.
Access has also the equivalent of @@identity for inserted record but I
don
't
remember the exact syntax. Make a search on
Google for it.
The use of the "select @@identity as newID'" may be troublesome if you have
triggers on your database.
S. L.
Filips Benoit said:
Dear All,
I try this but it keeps returning zero !
Dim newid As Long
CurrentProject.Connection.Execute "INSERT INTO [ORDER] (ORD_P_ID,
ORD_CREATION_DATE) VALUES ('4004', CONVERT(DATETIME,'" & Year(Date) &
"-"
&
Month(Date) & "-" & Day(Date) & " 00:00:00', 102)); select @@identity as
'newID'"
Me.Requery
MsgBox newid >> returns 0
Msgbox DMax("ORD_ID","[ORDER]") >> returns the new id
My question: is it possible to return the newid into a VB-variable directly?
Filip
