Returning values after an insert in SQL Server

Results 1 to 2 of 2

Thread: Returning values after an insert in SQL Server

  1. #1
    Join Date
    Dec 1969

    Default Returning values after an insert in SQL Server

    Hi,<BR>Can anyone help me with this problem? To help me explain the problem I&#039ll give some details...<BR><BR>The database contains a table called Login with rows called <BR>U_ID int (primary key)<BR>U_Name nvarhar(50)<BR>U_Password nvarchar(50)<BR><BR>I want to return the value of a primary key after an insert operation by using "@@IDENTITY"<BR><BR>The asp code I have problems with is (assuming "conn" is an open connection to an SQL server db)...<BR>----------------------<BR>DIM Ors,ident<BR><BR>SQL = "INSERT INTO Login(U_Name,U_Password) VALUES (&#039someusername&#039 , &#039somepassword&#039)"<BR><BR>SQL = SQL & " SELECT @@IDENTITY AS &#039ident&#039")<BR><BR>Set Ors = conn.Execute(SQL)<BR>ident = Ors("ident")<BR>----------------------<BR><BR>The error I get returned is:<BR><BR>Error Type:<BR>ADODB.Recordset (0x800A0CC1)<BR>Item cannot be found in the collection corresponding to the requested name or ordinal.<BR><BR>The &#039ident&#039 turns up ok in query analyzer - but not in an ASP page - can anyone think why?<BR><BR>Thanks in advance,<BR>Gareth.

  2. #2
    Join Date
    Dec 1969

    Default Here's the way I do it!!

    set dbcn=server.createobject("adodb.connection")<BR>db "dsn=yourdsn"<BR>set dbrs=Server.CreateObject("adodb.recordset")<BR>dbr s.activeconnection=dbcn<BR> "insert into login(U_Name,U_Password) values (&#039somename&#039,&#039somepw&#039)"<BR>dbrs.ipe n "select max(U_ID) as newid from login"<BR>userid=dbrs.Fields("newid")<BR>&#039***" newid" is a alias, and is not a field***<BR><BR>hope this helps!!

Posting Permissions

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