Re: Sample about how to retrieve a value from a store procedure

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Email wrote: 
hi
"Cacho" <jleyba@xxxxxxxxxxx> ha scritto nel messaggio
news:e1vZxIiDFHA.3928@xxxxxxxxxxxxxxxxxxxxxxx

Hi

Could anybody please give me a sample about how to retrieve a value from
a store procedure ?

I'm using VC++ 6 with ADO and MSDE.

SP will make an update and if it was successful will return 1 and 0 if
not...

I've been "goggling" but I couldn't find any useful sample code...

Thanks in advance

Javier 



// Get return value of stored procedure
//20050302 by masterz with VC6 SP6, Windows 2000 SP4, MSDE 2000
#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#import "C:\PROGRA~1\COMMON~1\System\ado\msado15.dll" \
rename( "EOF", "adoEOF" )
struct InitOle
{
InitOle() { ::CoInitialize(NULL); }
~InitOle() { ::CoUninitialize(); }
} _init_InitOle_;
void PrintProviderError(ADODB::_ConnectionPtr pConnection);

int main(int argc, char* argv[])
{
ADODB::_ConnectionPtr Conn1;
ADODB::_CommandPtr Cmd1;
ADODB::_ParameterPtr retParam= NULL;
ADODB::_ParameterPtr inParam=NULL;
_variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);
_variant_t vtEmpty2 (DISP_E_PARAMNOTFOUND, VT_ERROR);
_bstr_t bstrConnect( "driver={sql server};server=localhost;"
"Database=work;UID=sa;PWD=sa;" );
_bstr_t drop_proc("if exists (select * from dbo.sysobjects "
"where id = object_id(N'[dbo].[sp_return_test]') "
"and OBJECTPROPERTY(id, N'IsProcedure') = 1)"
" drop procedure [dbo].[sp_return_test]");
_bstr_t bstrCreate ("create proc sp_return_test( @InParam int )"
"as begin return (@InParam+@InParam) end;");
_bstr_t bstrSP(L"sp_return_test" );
try
{
_bstr_t bstrEmpty;
Conn1.CreateInstance( __uuidof( ADODB::Connection ) );
Cmd1.CreateInstance( __uuidof( ADODB::Command ) );
Conn1->ConnectionString = bstrConnect;
Conn1->Open( bstrConnect, bstrEmpty, bstrEmpty, -1 );
Conn1->Execute(drop_proc,NULL,ADODB::adCmdText);
Conn1->Execute(bstrCreate,NULL,ADODB::adCmdText);
printf("Create procedure OK\n");
Cmd1->ActiveConnection = Conn1;
Cmd1->CommandText = bstrSP;
Cmd1->CommandType = ADODB::adCmdStoredProc;
retParam=Cmd1->CreateParameter(_bstr_t("Return"),ADODB::adInteger,

ADODB::adParamReturnValue,sizeof(int));
Cmd1->Parameters->Append(retParam);
inParam = Cmd1->CreateParameter(_bstr_t("@InParam"),ADODB::adInteger,

ADODB::adParamInput,sizeof(int),

_variant_t( (long) 10 ));
Cmd1->Parameters->Append(inParam);
Cmd1->Execute(NULL,NULL,ADODB::adExecuteNoRecords);
for(long i=0;i<Cmd1->Parameters->GetCount();i++)
{
       printf("Parameter[%d]=%d\n",i,
(long)(Cmd1->Parameters->Item[i]->Value));
}
}
catch(_com_error &e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
printf("\nCOM error occurred, Source : %s \n Description : %s \n",
          (LPCSTR)bstrSource,(LPCSTR)bstrDescription);
PrintProviderError(Conn1);
}
system("pause");
return 0;
}
VOID PrintProviderError(ADODB::_ConnectionPtr pConnection)
{
ADODB::ErrorPtr pErr = NULL;
long nCount = 0;
long i = 0;
if( (pConnection->Errors->Count) > 0)
{
nCount = pConnection->Errors->Count;
for(i = 0; i < nCount; i++)
{
pErr = pConnection->Errors->GetItem(i);
printf("\n\t Error number: %x\t%s", pErr->Number,
          (LPCSTR)pErr->Description);
}
}
}
.

Relevant Pages

  • Sample about how to retrieve a value from a store procedure
    ... Could anybody please give me a sample about how to retrieve a value from ... I'm using VC++ 6 with ADO and MSDE. ...
    (microsoft.public.vc.database)
  • Re: DataType Text in MSSQL
    ... I beat my head up to figure out how to retrieve the value. ... Why does it not work if I use * to select all fields along with mytextField? ... >> use store procedure for this certain customer. ...
    (microsoft.public.vb.database.ado)
  • Function Return inside Store Procedure
    ... i have a store procedure that i use the return function. ... Inside the vb net i wnat to call that procedure and retrieve that value ... I´m using command. ...
    (microsoft.public.dotnet.general)