Error Using Return Value In Stored Procedure

Results 1 to 2 of 2

Thread: Error Using Return Value In Stored Procedure

  1. #1
    Join Date
    Dec 1969

    Default Error Using Return Value In Stored Procedure

    I am trying to use a return value in a stored procedure. I have used this code without the return value and it worked, but since adding the return value it gives me an error. <BR><BR>My ASP Code: <BR><BR>&#039;Create the stored procedure command <BR>Dim cmd <BR>Set cmd = Server.CreateObject("ADODB.Command") <BR>cmd.CommandText = "sp_policy" <BR>cmd.ActiveConnection = conn <BR>cmd.CommandType = adCmdStoredProc <BR><BR>&#039;Add the parameters. <BR>cmd.Parameters.Append = _ <BR>cmd.CreateParameter("@PolicyID",adInteger,adPa ramInput, ,PolicyID) <BR>cmd.Parameters.Append = _ <BR>cmd.CreateParameter("@PolicyTitle",adVarChar,a dParamInput,100,PolicyTitle) <BR>cmd.Parameters.Append = _ <BR>cmd.CreateParameter("@PolicyDate",adDate,adPar amInput, ,PolicyDate) <BR>cmd.Parameters.Append = _ <BR>cmd.CreateParameter("@AdminID",adInteger,adPar amInput, ,AdminID) <BR>cmd.Parameters.Append = _ <BR>cmd.CreateParameter("@FinalPolicyID", adInteger, adParamReturnValue, 0) <BR><BR>&#039;Run the stored procedure <BR>cmd.Execute , ,adExecuteNoRecords <BR>&#039;Get the PolicyID of the record. <BR><BR>FinalPolicyID = cmd.Parameters("@FinalPolicyID") <BR><BR>ASP Error: <BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039;80040e21&#039; <BR><BR>[Microsoft][ODBC SQL Server Driver]Optional feature not implemented <BR><BR>/usgonline/admin/policies/processors/policy.asp, line 66 <BR><BR><BR>And the SQL code, which i don&#039;t think you&#039;ll need but will include anyways: <BR><BR><BR>CREATE PROCEDURE sp_policy ( <BR>@PolicyID int, <BR>@PolicyTitle varchar(200), <BR>@PolicyDate datetime, <BR>@AdminID int <BR>) <BR>AS <BR>DECLARE @FinalPolicyID int <BR><BR>IF exists(SELECT PolicyID FROM USG_POLICIES WHERE PolicyID = @PolicyID) <BR>BEGIN <BR>UPDATE USG_POLICIES <BR>SET <BR>PolicyTitle = @PolicyTitle, <BR>PolicyDate = @PolicyDate, <BR>AdminID = @AdminID, <BR>AdminDate = GetDate() <BR>WHERE <BR>PolicyID = @PolicyID <BR>SET @FinalPolicyID = @PolicyID <BR>END <BR>ELSE <BR>BEGIN <BR>INSERT INTO USG_POLICIES ( <BR>PolicyTitle, <BR>PolicyDate, <BR>AdminID, <BR>AdminDate <BR>) <BR>VALUES( <BR>@PolicyTitle, <BR>@PolicyDate, <BR>@AdminID, <BR>GetDate() <BR>) <BR>SELECT @FinalPolicyID AS PolicyID FROM USG_POLICIES WHERE PolicyTitle = @PolicyTitle AND PolicyDate = @PolicyDate <BR>END <BR><BR>RETURN @FinalPolicyID <BR><BR><BR><BR>thanks, <BR>brian <BR>

  2. #2
    Join Date
    Dec 1969

    Default You may have found out by now ...

    one missing comma.<BR><BR>&#062;&#062;cmd.Parameters.Append = _ <BR>&#062;&#062;cmd.CreateParameter("@FinalPolicyI D", adInteger, adParamReturnValue, 0) <BR><BR>cmd.Parameters.Append = _ <BR>cmd.CreateParameter("@FinalPolicyID", adInteger, adParamReturnValue, , 0) <BR><BR>

Posting Permissions

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