Microsoft KB Archive/185125

= How To Invoke a Stored Procedure with ADO Query Using VBA/C++/Java =

Article ID: 185125

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q185125



SUMMARY
In order to open a stored procedure within ActiveX Data Objects (ADO), you must first open a Connection Object, then a Command Object, fill the Parameters Collection with one parameter in the collection for each parameter in the query, and then use the Command.Execute method to open the ADO Recordset.

You may optionally use the Parameters.Refresh method to populate the Parameters Collection for the stored procedure. In addition, if the stored procedure is returning output or return parameters, you need to close the recordset before checking the value of the output parameters.

This is demonstrated in the code snippets below that deletes (if it already exists) and then creates a stored procedure, sp_adoTest, on a SQL Server that has input, output, and return parameters, as well as returning a recordset.

This article demonstrates how to perform this operation using VBA/VBScript, C++, and Java.



MORE INFORMATION
These code snippets are abbreviated from the ADO samples listed in the "References" section. Please use the full ADO samples if you want to build and run this code.

VBA in Visual Basic or Microsoft Access & VBScript from ASP
Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Rs1 As ADODB.Recordset

Dim strTmp As String

Dim Connect As String Dim Drop As String Dim Create As String Dim sp as string

Dim i As Integer Dim l As Long

sConnect= "driver={sql server};" & _

"server=server_name;" & _ "Database=pubs;UID=uder_id;PWD=password;"

sCreate = "create proc sp_AdoTest( @InParam int, " & _

"@OutParam int OUTPUT ) " & _            "as "  & _             "select @OutParam = @InParam + 10 " & _             "SELECT * FROM Authors WHERE "   & _             "State <> 'CA' "  & _             "return @OutParam +10"

sDrop  = "if exists "  & _

"(select * from sysobjects where " & _             "id = object_id('dbo.sp_AdoTest') and " & _             "sysstat & 0xf = 4)"  & _ "drop procedure dbo.sp_AdoTest"

sSP    = "sp_Adotest"

' Establish connection. Set Conn1 = New ADODB.Connection Conn1.ConnectionString = sConnect Conn1.Open

' Drop procedure, if it exists & recreate it. Set Rs1 = Conn1.Execute(sDrop, l, adCmdText) Set Rs1 = Nothing

Set Rs1 = Conn1.Execute(sCreate, l, adCmdText) Set Rs1 = Nothing

' Open recordset. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "sp_AdoTest" Cmd1.CommandType = adCmdStoredProc Cmd1.Parameters.Refresh Cmd1.Parameters(1).Value = 10 Set Rs1 = Cmd1.Execute

' Process results from recordset, then close it. RS1.Close Set Rs1 = Nothing

' Get parameters (assumes you have a list box named List1).

Debug.print vbTab & "RetVal Param = " & Cmd1.Parameters(0).Value Debug.print vbTab & "Input Param = " & Cmd1.Parameters(1).Value Debug.print vbTab & "Output Param = " & Cmd1.Parameters(2).Value For VBScript users, you would replace the Dim statements with equivalent CreateObject calls, such as: Set conn1 = CreateObject( "ADODB.Connection.1.5" ) As ADO version 1.x is not binary compatible, it is helpful to specify which version of ADO your script is referencing.

C++ Using #import
#import "C:\Program Files\Common Files\System\ado\msado15.dll" \

_variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);

rename( "EOF", "adoEOF" )

...  ADODB::_ConnectionPtr  Conn1; ADODB::_CommandPtr    Cmd1; ADODB::_RecordsetPtr  Rs1;

_bstr_t   bstrConnect( L"driver={sql server};"

L"server=server_name;" L"Database=pubs;UID=user_id;PWD=password;" );

_bstr_t   bstrCreate ( L"create proc sp_AdoTest( @InParam int, "

L"@OutParam int OUTPUT ) "                          L"as "                           L"select @OutParam = @InParam + 10 "                           L"SELECT * FROM Authors WHERE "                           L"State <> 'CA' "                           L"return @OutParam +10" );

_bstr_t   bstrDrop   ( L"if exists "

L"(select * from sysobjects where "                          L"id = object_id('dbo.sp_AdoTest') and "                           L"sysstat & 0xf = 4)" L"drop procedure dbo.sp_AdoTest" );

_bstr_t   bstrSP     ( L"sp_Adotest" );

try {

// Establish connection. Conn1.CreateInstance( __uuidof( ADODB::Connection ) ); Conn1->ConnectionString = bstrConnect; Conn1->Open( bstrEmpty, bstrEmpty, bstrEmpty, -1 );

// Drop procedure, if it exists & recreate it. Conn1->Execute( bstrDrop, &vtEmpty, ADODB::adCmdText); Conn1->Execute( bstrCreate, &vtEmpty, ADODB::adCmdText);

// Open recordset. Cmd1.CreateInstance( __uuidof( ADODB::Command ) ); Cmd1->ActiveConnection = Conn1; Cmd1->CommandText     = bstrSP; Cmd1->CommandType     = ADODB::adCmdStoredProc;

Cmd1->Parameters->Refresh; Cmd1->Parameters->Item[ _variant_t( (long) 1 ) ]->Value = _variant_t( (long) 10 );

Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, ADODB::adCmdUnknown );

// Process results from recordset, then close it. Rs1->Close;

// Get parameters. TRACE( "\tRetVal Param = %s",             CrackStrVariant( (tagVARIANT) Cmd1->Parameters->Item[ _variant_t( 0L ) ]- >Value ) ); TRACE( "\tInput Param = %s",              CrackStrVariant( (tagVARIANT) Cmd1->Parameters->Item[ _variant_t( 1L ) ]- >Value ) ); TRACE( "\tOutput Param = %s",             CrackStrVariant( (tagVARIANT) Cmd1->Parameters->Item[ _variant_t( 2L ) ]- >Value ) );

}  catch( CException *e ) { e->Delete; } catch(...)            {  } For a demonstration of how to use a Parameterized Query either with classes generated by the MFC ClassWizard, or using straight COM programming, please see the ADOVC sample referenced in the REFERENCES section.

CrackStrVariant is a function that stuffs the contents of a Variant into a CString (if possible) and is demonstrated in the Adovc.exe sample referenced below.

Java
msado15._Connection Conn1   = new msado15.Connection; msado15._Command    Cmd1    = null; msado15._Recordset  Rs1     = new msado15.Recordset;

Variant             v1 = new Variant; Variant             v2 = new Variant;

String bstrConnect = new String(

"driver={sql server};" + "server=CharlotteTown;Database=pubs;UID=sa;PWD=;");

String bstrCreate  = new String(

"create proc sp_AdoTest( @InParam int, " +              "@OutParam int OUTPUT ) " + "as " + "select @OutParam = @InParam + 10 " + "SELECT * FROM Authors WHERE " + "State <> 'CA' " + "return @OutParam +10" );

String bstrDrop    = new String(

"if exists " + "(select * from sysobjects where " +              "id = object_id('dbo.sp_AdoTest') and " +               "sysstat & 0xf = 4)" + "drop procedure dbo.sp_AdoTest" );

String bstrSP      = new String( "sp_Adotest" );

// Trap any error/exception. try {

// Establish connection. Conn1.putConnectionString( bstrConnect ); Conn1.Open( bstrEmpty, bstrEmpty, bstrEmpty, -1 );

// Drop procedure, if it exists & recreate it. vtEmpty.noParam; Conn1.Execute( bstrDrop, vtEmpty,                                      msado15.CommandTypeEnum.adCmdText); vtEmpty.noParam; Conn1.Execute( bstrCreate, vtEmpty,                                      msado15.CommandTypeEnum.adCmdText);

// Open recordset. Cmd1= new msado15.Command; Cmd1.putActiveConnection( Conn1 ); Cmd1.putCommandText    ( bstrSP ); Cmd1.putCommandType    (msado15.CommandTypeEnum.adCmdStoredProc);

Cmd1.getParameters.Refresh; v1.putInt( 1 ); v2.putInt( 10 ); Cmd1.getParameters.getItem( v1 ).putValue( v2 );

vtEmpty.noParam; vtEmpty2.noParam; Rs1 = Cmd1.Execute( vtEmpty, vtEmpty2,                                 msado15.CommandTypeEnum.adCmdUnknown );

// Process results from recordset, then close it. Rs1.Close;

// Get parameters (assumes you have a listbox named List1) v1.putInt( 0 ); List1.addItem( "   RetVal Param = " +                         Cmd1.getParameters.getItem( v1 ).getValue ); v1.putInt( 1 ); List1.addItem( "   Input  Param = " +                         Cmd1.getParameters.getItem( v1 ).getValue ); v1.putInt( 2 ); List1.addItem( "   Output Param = " +                         Cmd1.getParameters.getItem( v1 ).getValue ); }  // Catch Blocks catch (com.ms.com.ComFailException e) { } catch(Exception e)                   { }

