2 Replies Latest reply on Nov 12, 2006 11:11 PM by cf_dev2

    cfstoredproc

    Level 7
      I am trying to use cfstoreproc to invoke a stored procedure from a CFC.

      I pass in 2 variables into the cfprocparam but am getting this error:


      I dumped the 2 invokearguments to make sure they are working and they dump
      fine.
      Any ideas why this isn't working? (my first time trying to use stored
      procedures)

      Here is the invoke:
      <cfobject name="IQ" component="cfcs.IQ.IQweb">
      <cfinvoke component="#IQ#" method="NewPassword">
      <cfinvokeargument name="username" value="#GetInfo.ACCOUNT_ID#">
      <cfinvokeargument name="password" value="#stpassword#">
      </cfinvoke>


      Here is the CFC:
      <cfcomponent>
      <cffunction name="NewPassword" access="public" returntype="void">
      <cfargument name="username" type="string" required="yes">
      <cfargument name="password" type="string" required="yes">
      <cfstoredproc procedure="sp_web_changepassword" datasource="sql_abt">
      <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="@Account_ID"
      value="#arguments.username#" null="No">
      <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR"
      variable="@UnencryptedNewPassword" value="#arguments.password#" null="No">
      <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" variable="new_result"
      dbvarname>
      </cffunction>
      </cfcomponent>

      Here is the Stored Procedure:
      create procedure sp_web_changepassword
      @Account_ID varchar(8), @UnencryptedNewPassword Varchar(255), @return_code
      int OUTPUT
      as
      declare @EncryptedPassword varchar(255)
      -- initialize variables Return code to Account_ID error code
      select @return_code = 6, @EncryptedPassword = ' '
      if exists (select * from ABT_ACCOUNTS where Account_ID = @Account_ID)
      begin
      -- first encrypt entered password
      exec sp_encrypt_text @UnencryptedNewPassword, @EncryptedPassword OUTPUT
      -- update ABT_ACCOUNTS with new password
      UPDATE ABT_ACCOUNTS
      Set Password = @EncryptedPassword
      where Account_ID = @Account_ID
      -- return SUCCESS code
      select @return_code = 0
      end

      GO