Obtaining return value from a stored procedure in ADO

[stevecvb]

I am having trouble obtaining the return value from a stored procedure using ADO. Below is a snippet of code without obtaining the value. Help is much appreciated.

_ConnectionPtr pConn;
_RecordsetPtr pRs;
_CommandPtr pCommand;

_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;

hr_db = pConn.CreateInstance(__uuidof(Connection));
hr_db = pRs.CreateInstance(__uuidof(Recordset));
hr_db= pCommand.CreateInstance(__uuidof( Command ));

CString str;
str.Empty();

str="DSN=Data";

_bstr_t strConn(str);
hr_db = pConn->Open(strConn, "sa", "", adConnectUnspecified);

pCommand->ActiveConnection=pConn;
pCommand->CommandText = "spdummysprt";
pCommand->Parameters->Append(pCommand->CreateParameter("@retval", adInteger, adParamOutput, 4));
pRs->Open(vNull, vNull, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc);

 

[williamu]

Hi Steve,

In order to get the value from the Stored Proc you first need to flush the resultset. I don't use ADO but the ODBC API, and this is how I would do it.

if (pData->Execute())
{
while (SQLMoreResults(pData->GetHandle()) != SQL_NO_DATA) ;
}

HTH.

William
Software Engineer
ICQ No. 56047340

 

[TGM]

Hi

Basically, assuming prs being the pointer to your recordset:

- scan your recordset
- use the two functions showed below


if ( prs->GetRecordCount() == 0L)
return FALSE;

try
{
// Move to First Record

prs->MoveFirst();

while( !prs->adoEOF)
{
lId = GetIntegerField( prs, "MemberId"
str = GetStringField( prs, "Name", strName);
prs->MoveNext();
}
}
catch( _com_error& e)
{
Show Error Message();
return FALSE;
}

CString GetStringField( _RecordsetPtr prs, CString strField, CString strResult)
{

_variant_t varStr;

// Get Field Data

varStr = prs->GetCollect(( char*) ((const char*) strField));

// Store it as a String

if (( varStr.vt != VT_NULL) && ( varStr.vt != VT_EMPTY))
{
strResult = ( char*) _bstr_t( varStr);
strResult.TrimLeft();
strResult.TrimRight();
}
else
strResult = "";

return strResult;
}

long GetIntegerField( _RecordsetPtr prs, CString strField)
{

_variant_t varLong;
long lResult = ADO_ERROR;

// Get LastName

varLong = prs->GetCollect(( char*) ((const char*) strField));

if ( varLong.vt != NULL)
{
// Note: for SQL, tests show that VT_I4 prevails
if ( varLong.vt == VT_I2)
lResult = ( long) V_I2( &varLong);
else if ( varLong.vt = VT_I4)
lResult = ( long) V_I4( &varLong);
}

return lResult;
}

HTH

Thierry