@@IDENTITY Programming Jitters

Hi Experts,
I have done this and it is working but am getting the jitters and it is
making me spend sleepless nights:
Dim rsMaster as New ADODB.Recordset
Dim tempRS as New ADODB.Recordset
cn.BeginTrans
rsMaster.AddNew
rsMaster.Fields!Date = txtDate.Text
rsMaster.Fields!Ref = txtRef.Text
...
rsMaster.Update
Set tempRS = cn.Execute("Select @.@.IDENTITY vno")
MastID= tempRs!vno
tempRs.Close
cn.CommitTrans
I am not using INSERT INTO because i've already done the Addnew Code and
have written many programs trapping all ado errors with error trap label and
using -2147xxxxxxxx Error Number
PLEASE help in this regar
FOUR CHEERS not THREE CHEERS to technet for the Help
Manish Sawjiani
Software Innovations & TrainingI am not sure what your question is. Are you simply looking for validation
that what you have done is OK? It looks OK except that you should be using
SCOPE_IDENTITY() and not @.@.IDENTITY. Check out BooksOnLine for the
differences if you are not sure.
Andrew J. Kelly SQL MVP
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:EB7F9EB8-2715-4FF5-9268-B99E57A83F97@.microsoft.com...
> Hi Experts,
> I have done this and it is working but am getting the jitters and it is
> making me spend sleepless nights:
> Dim rsMaster as New ADODB.Recordset
> Dim tempRS as New ADODB.Recordset
> cn.BeginTrans
> rsMaster.AddNew
> rsMaster.Fields!Date = txtDate.Text
> rsMaster.Fields!Ref = txtRef.Text
> ....
> rsMaster.Update
> Set tempRS = cn.Execute("Select @.@.IDENTITY vno")
> MastID= tempRs!vno
> tempRs.Close
> cn.CommitTrans
> I am not using INSERT INTO because i've already done the Addnew Code and
> have written many programs trapping all ado errors with error trap label
> and
> using -2147xxxxxxxx Error Number
> PLEASE help in this regar
> FOUR CHEERS not THREE CHEERS to technet for the Help
> Manish Sawjiani
> Software Innovations & Training
>|||Sir I want to be sure that the identity number i receive is OK and it will
not clash in a multi user sceneario using SQL Server 7.0
This number is important because i am going to create some transactions
against these and store it in the database.
THANKS a MILLION For your Help in Advance sir
"Andrew J. Kelly" wrote:

> I am not sure what your question is. Are you simply looking for validatio
n
> that what you have done is OK? It looks OK except that you should be usin
g
> SCOPE_IDENTITY() and not @.@.IDENTITY. Check out BooksOnLine for the
> differences if you are not sure.
> --
> Andrew J. Kelly SQL MVP
>
> "Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
> message news:EB7F9EB8-2715-4FF5-9268-B99E57A83F97@.microsoft.com...
>
>|||My recommendation is to create a stored procedure and call it from your
client app. I do not have time in this moment to post an example but if you
need one let me know.
HOW TO: Refer to a Just-Inserted Record in a SQL Server 7.0
http://support.microsoft.com/defaul...kb;en-us;319724
AMB
"Manish Sawjiani" wrote:
> Sir I want to be sure that the identity number i receive is OK and it will
> not clash in a multi user sceneario using SQL Server 7.0
> This number is important because i am going to create some transactions
> against these and store it in the database.
> THANKS a MILLION For your Help in Advance sir
> "Andrew J. Kelly" wrote:
>|||SCOPE_IDENTITY() and @.@.IDENTITY values apply only to the current connection
scope. These will not be affected by other connections.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:3B88EEFA-5989-4094-89A2-7FEBBF27D6FE@.microsoft.com...
> Sir I want to be sure that the identity number i receive is OK and it will
> not clash in a multi user sceneario using SQL Server 7.0
> This number is important because i am going to create some transactions
> against these and store it in the database.
> THANKS a MILLION For your Help in Advance sir
> "Andrew J. Kelly" wrote:
>|||As Dan pointed out they are scoped only to the current session so that is
not an issue. However if you have a trigger on the table you insert into
and it does an insert on a table with an IDENTITY column you may get the
wrong IDENTITY returned in 7.0. If you have that as a possibility I suggest
you use hand generated ID's instead. If this is a new application why are
you using 7.0?
Andrew J. Kelly SQL MVP
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:3B88EEFA-5989-4094-89A2-7FEBBF27D6FE@.microsoft.com...
> Sir I want to be sure that the identity number i receive is OK and it will
> not clash in a multi user sceneario using SQL Server 7.0
> This number is important because i am going to create some transactions
> against these and store it in the database.
> THANKS a MILLION For your Help in Advance sir
> "Andrew J. Kelly" wrote:
>|||SQL Server 7.0 doesn't have an equivalent of SQL Server 2000's
SCOPE_IDENTITY function.
IDENTITY should never be the only key of a table therefore a reliable method
to retrieve the IDENTITY for the last row inserted is to use an alternative
natural key of the table. In the following example I've assumed key_col is
the key:
/* Insert a row */
INSERT INTO YourTable (key_col, col1, ...)
VALUES (@.key_col_value, @.col1_value, ...)
/* Retrieve the IDENTITY */
SELECT @.identity_value = id_col
FROM YourTable
WHERE key_col = @.key_col
As others have indicated, you really should use TSQL stored-procs for all
data modifications in preference to updating a recordset unless you have an
exceptional reason to do otherwise.
David Portas
SQL Server MVP
--