Roger Withnell wrote:
After inserting the record, I'm using "ident_curr ent
('table')" to establish the autonumber of the record.
Does it guarantee that the result will be the number of
the record I have just inserted
No. From BOL:
Returns the last identity value generated for a specified table in any
session and any scope.
So you may wind up getting the ID of a row inserted by some other process.
or could it be the number
of a record inserted meanwhile? If the latter, does
using "scope_identity ()" guarantee that it is the number
of my record?
Yes. Again from BOL:
[SCOPE_IDENTITY] ... Returns the last IDENTITY value inserted into an
IDENTITY column in the same scope. A scope is a module -- a stored
procedure, trigger, function, or batch. Thus, two statements are in the same
scope if they are in the same stored procedure, function, or batch.
I think this is pretty clear ...
If so, how do I issue it on the "same"
instance as the initial query?
As always, I suggest using a stored procedure:
Using Query Analyzer, run this script (adapt it to your table of course):
CREATE PROCEDURE InsRow (
@TextCol varchar(50),
@IntCol int) AS
SET NOCOUNT ON
INSERT INTO tblName(TextCol , IntCol)
VALUES(@TextCol ,@IntCol)
SELECT SCOPE_IDENTITY As [NewID]
Then in ASP:
dim rs, cn
Set cn= Server.CreateOb ject("ADODB.Con nection")
cn.Open strConnection
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
cn.InsRow "ABC",123,r s
Of course, this is inefficient, since it is using a heavy recordset object
to return a single value to the client. My preference is to use an output
parameter, retrieving its value using a Command object in ASP:
CREATE PROCEDURE InsRow (
@TextCol varchar(50),
@IntCol int,
@NewID int output) AS
SET NOCOUNT ON
INSERT INTO tblName(TextCol , IntCol)
VALUES(@TextCol ,@IntCol)
SET @NewID = SCOPE_IDENTITY
dim cn,cmd,newID, params
Set cn= Server.CreateOb ject("ADODB.Con nection")
cn.Open strConnection
Set cmd= Server.CreateOb ject("ADODB.Com mand")
With cmd
.CommandText = "InsRow"
.CommandType = adCmdStoredProc
Set .ActiveConnecti on = cn
Set params = .Parameters
params.Append .CreateParamete r("RETURN_VALUE ", adInteger, _
adParamReturnVa lue)
params.Append .CreateParamete r("@TextCol", adVarChar, _
adParamInput,50 ,"ABC")
params.Append .CreateParamete r("@IntCol", adInteger, _
adParamInput,,1 23)
params.Append .CreateParamete r("@NewID", adInteger, _
adParamOutput)
.Execute ,,adExecuteNoRe cords
End With
newID = params(3).value
Admittedly, it's more code, and it can be tricky to write, but it is more
efficient than using a recordset. The trickiness can be alleviated by using
a code generator, such as the one I wrote which is available here:
http://www.thrasherwebdesign.com/ind...asp&c=&a=clear
But, if you have some sort of phobia about using stored procedures, you can
send a string of batched commands to SQL Server so they all get executed in
the same scope. You can use what Bhaskardeep posted:
strSQL = "SET NOCOUNT ON INSERT INTO tblName(TextFie ld, NumberField)
VALUES('ABC', 123) SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"
Just substitute "SCOPE_IDENTITY " for "@@IDENTITY "
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
