You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q185125
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
SUMMARY
In order to open a stored procedure within ActiveX Data Objects (ADO), youmust first open a Connection Object, then a Command Object, fill theParameters Collection with one parameter in the collection for eachparameter in the query, and then use the Command.Execute() method to openthe ADO Recordset.

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

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

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 thiscode.

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};" &amp; _             "server=server_name;" &amp; _             "Database=pubs;UID=uder_id;PWD=password;"   sCreate = "create proc sp_AdoTest( @InParam int, " &amp; _             "@OutParam int OUTPUT ) " &amp; _             "as "  &amp; _             "select @OutParam = @InParam + 10 " &amp; _             "SELECT * FROM Authors WHERE "   &amp; _             "State <> 'CA' "  &amp; _             "return @OutParam +10"   sDrop   = "if exists "  &amp; _             "(select * from sysobjects where "  &amp; _             "id = object_id('dbo.sp_AdoTest') and " &amp; _             "sysstat &amp; 0xf = 4)"  &amp; _             "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 &amp; 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 &amp; "RetVal Param = " &amp; Cmd1.Parameters(0).Value   Debug.print vbTab &amp; "Input  Param = " &amp; Cmd1.Parameters(1).Value   Debug.print vbTab &amp; "Output Param = " &amp; Cmd1.Parameters(2).Value				
For VBScript users, you would replace the Dim statements with equivalentCreateObject calls, such as:
   Set conn1 = CreateObject( "ADODB.Connection.1.5" )				
As ADO version 1.x is not binary compatible, it is helpful to specify whichversion 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 &amp; 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 &amp; recreate it.       Conn1->Execute( bstrDrop, &amp;vtEmpty, ADODB::adCmdText);       Conn1->Execute( bstrCreate, &amp;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( &amp;vtEmpty, &amp;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 classesgenerated by the MFC ClassWizard, or using straight COM programming, pleasesee the ADOVC sample referenced in the REFERENCES section.

CrackStrVariant is a function that stuffs the contents of a Variant into aCString (if possible) and is demonstrated in the Adovc.exe samplereferenced 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 &amp; 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 &amp; 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)                    { }				
REFERENCES
For additional information, please see the following articles in theMicrosoft Knowledge Base:

172403SAMPLE: Adovb.exe Demonstrates How to Use ADO with Visual Basic

220152FILE: Adovc.exe Demonstrates How To Use ADO with Visual C++
Properties

Article ID: 185125 - Last Review: 03/14/2005 17:30:27 - Revision: 1.2

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

  • kbarttypeinf kbcode kbdatabase kbhowto kbmdacnosweep KB185125
Feedback
;did=1&t=">ne;" onerror="var m=document.createElement('meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">