@@IDENTITY Does not work in asp ?!?

Results 1 to 3 of 3

Thread: @@IDENTITY Does not work in asp ?!?

  1. #1
    Join Date
    Dec 1969

    Default @@IDENTITY Does not work in asp ?!?

    Hi I have just read an article about how to best get the keyID of a new record. First insert etc then Execute another statement selecting the @@IDENTITY from the table.<BR><BR>I have tried this but the recordset is empty ?<BR><BR>Here is the important part of my code....<BR><BR><BR>Insert into Users (&#039Blah&#039) Values (&#039Blah&#039)<BR><BR>Set MyRS = MyConn.execute("Select @@IDENTITY from Users")<BR><BR>intID = MyRS(0)<BR><BR>&#039test if it worked<BR><BR>Response.write intID &#060;-- This is empty why is this.<BR><BR>I am using IIS5 with SQL 7. If I run the same kind of thing in Query anylizer it does seem to work except is says something like 24 rows affected and does not give the output for each row a heading.<BR><BR>Please help <BR><BR>Thanks <BR><BR>Dwane Lumley

  2. #2
    Garth Guest

    Default RE: @@IDENTITY Does not work in asp ?!?

    I&#039m not sure why that approach isn&#039t working for you, but you can use the code listed below to see how to retrieve the value using the command object.<BR><BR><BR>Garth<BR>www.SQLBook.com<BR><BR >--SQL Script<BR>USE tempdb<BR>go<BR>CREATE TABLE Test<BR>(<BR> Col1 smallint IDENTITY,<BR> Col2 varchar(30)<BR>)<BR>go<BR>CREATE PROCEDURE ps_Test_OutputExample<BR>@Col2 varchar(30),<BR>@ReturnValue smallint OUTPUT<BR>AS<BR>SET NOCOUNT ON<BR><BR>INSERT Test VALUES (@Col2)<BR><BR>SET @ReturnValue = @@IDENTITY<BR>go<BR><BR><BR>--ASP Script<BR>&#060;!--#include file="adovbs.inc"--&#062;<BR>&#060;%<BR> On Error Resume Next<BR> Set objConn = server.CreateObject("ADODB.Connection")<BR> REM DSN-Less OLE-DB<BR> objConn.Open "Provider=SQLOLEDB;Server=EAGLESQL2000_1;Database= tempdb;Uid=sa;Pwd=password"<BR><BR> REM DSN-Less ODBC<BR> REM objConn.Open "Driver={SQL Server};Server=ace;Database=Northwind;Uid=sa;Pwd=" <BR><BR> Set cmd1 = server.CreateObject("ADODB.Command")<BR> cmd1.ActiveConnection = objConn<BR> cmd1.CommandType = adCmdStoredProc<BR> cmd1.CommandText = "ps_Test_OutputExample"<BR> cmd1.Parameters.Append cmd1.CreateParameter("TableName",adVarChar,adParam Input,30,"Testing")<BR> cmd1.Parameters.Append cmd1.CreateParameter("ReturnValue",adTinyint,adPar amOutput)<BR> cmd1.Execute<BR><BR> response.write cmd1.Parameters("ReturnValue")<BR>%&#062;

  3. #3
    Join Date
    Dec 1969

    Default RE: @@IDENTITY Does not work in asp ?!?

    It&#039s not working because you didn&#039t have a compound statement<BR><BR>you can&#039t execute one, move on and expect the identity to syill be there, you need the inSERT and the SELECT in the same command batch, separated by semicolons<BR><BR>there&#039s an article at http://www.infinitemonkeys.ws/infinitemonkeys/ which expands on compound SQL approaches - called &#039save yourself SQL hassles&#039<BR><BR>j

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts