ADO - Cannot access the Return Parameter of a stored procedure on SQL Server 2005

Hello,

I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.

To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.

try{

pCmd.CreateInstance((__uuidof(Command)));

pCmd->ActiveConnection = m_pConnection;

pCmd->CommandType = adCmdStoredProc;

pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");

............................ code here ........................................

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin));

VARIANT returnVal;

returnVal.vt = VT_I2;

returnVal.intVal = NULL;

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal));

//Get Return value by executing the command

//The return value should be the DB unique ID.

pCmd->Execute(NULL, NULL, adCmdStoredProc);

int uniqueId = returnVal.intVal;

//pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc);

//GetFieldValue(0,pRst,uniqueId);

printf("The DB unique ID is: %i",uniqueId);

return uniqueId;

}

Cheers,

Seth

You should not include returnVal into the call of CreateParameter. What happens is that compiler creates a temporary object which is useless to you.

Here is what you should do:

_ParameterPtr pReturnParam = pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t));

pCmd->Parameters->Append(pReturnParam);
pCmd->Execute(0,0,adCmdStoredProc);
variant_t value = pReturnParam->GetValue();