Return value

Results 1 to 2 of 2

Thread: Return value

  1. #1
    Join Date
    Dec 1969

    Default Return value

    hi,<BR><BR>i have created a stored procedure to insert value and get to last inserted value like this<BR><BR>---------------------------<BR>CREATE PROCEDURE sp_test <BR> @cid int, <BR> @cname varchar(15) <BR><BR> AS <BR><BR> insert into tCity (tCityID,CityName) <BR> VALUES (@cid,@cname) <BR> select a1=max(tCityID) from tCity <BR>-----------------------------------------<BR><BR>in asp page i use the following commands;<BR><BR> SOL="EXEC sp_test 35,&#039;ATLANTA&#039;" <BR> set myRS=dbx.execute(SOL) <BR> lastrecord=myRS("a1")<BR><BR>but it gives an error like this;<BR><BR> Error Type: <BR> ADODB.Recordset (0x800A0CC1) <BR> Item cannot be found in the collection corresponding to the requested name <BR>or ordinal. <BR> /vetonline/addVetQuestion.asp, line 34 <BR><BR>line 34 is the line of lastrecord=myRS("a1")<BR><BR><BR>Can anybody tell me why it does not return the last value to myRS("a1")<BR><BR>Regards

  2. #2
    Join Date
    Dec 1969

    Default RE: Return value

    as you are just returning one value there is no need to incur the extra overhead of a recordset, also you should use the @@IDENTITY rather than that select, a busy db could return the wrong value<BR><BR>CREATE PROCEDURE sp_test <BR>@cid int, <BR>@cname varchar(15),<BR>@newid integer output<BR><BR>AS <BR><BR>set nocount on<BR><BR>insert into tCity (tCityID,CityName) <BR>VALUES (@cid,@cname) <BR>select @newid = @@IDENTITY<BR><BR>set nocount off<BR><BR>asp<BR>set objCommand = server.createObject("adodb.command")<BR>set objConnection = server.createObject("adodb.connection")<BR>objConn &#039;your connection string<BR>with objCommand<BR>.parameters.append .createParameter("@cid", adInteger, adParamInput, , 35)<BR>.parameters.append .createParameter("@cityname", adVarChar, adParamInput, 15, "ATLANTA")<BR>.parameters.append .createParameter("@newid", adInteger, adParamOuput)<BR>.activeconnection = objConnection<BR>.execute ,, adExecuteNoRecords<BR>lastrecord = .parameters("@newid")<BR>end with<BR>objConnection.close<BR>set objConnection = nothing<BR>set objCommand = nothing<BR><BR>WARNING : this HAS NOT BEEN tested and is typed as is whilst I drink my tea on a break, but it **should** get your rockin&#039; &#039;n&#039; rollin&#039;

Posting Permissions

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