HOWTO: Invoke a Parameterized ADO Query using VBA/C++/Java

• ActiveX Data Objects (ADO), versions 1.0, 1.5

SUMMARY

In order to open a parameterized query within ActiveX Data Objects (ADO), you have to 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.

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

MORE INFORMATION

All of the code samples below assume an ODBC Data Source named "BIBLIO" that points to the Biblio.mdb file that ships with Visual Basic 5.0. A parameterized query against the Authors table is performed, returning a recordset with all records where the field Au_ID < 5, and using a parameterized SQL statement. These code snippets are abbreviated from the ADO samples listed in the REFERENCES section below.

VBA within Visual Basic or Microsoft Access and VBScript from ASP

   Dim Conn1 As ADODB.Connection
   Dim Cmd1 As ADODB.Command
   Dim Param1 As ADODB.Parameter
   Dim Rs1 As ADODB.Recordset

   Dim i As Integer

   ' Trap any error/exception.
   On Error Resume Next

   ' Create and Open Connection Object.
   Set Conn1 = New ADODB.Connection
   Conn1.ConnectionString = "DSN=Biblio;UID=adimin;PWD=;"
   Conn1.Open

   ' Create Command Object.
   Set Cmd1 = New ADODB.Command
   Cmd1.ActiveConnection = Conn1
   Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?"

   ' Create Parameter Object.
   Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5)
   Param1.Value = 5
   Cmd1.Parameters.Append Param1
   Set Param1 = Nothing

   ' Open Recordset Object.
   Set Rs1 = Cmd1.Execute()

   Set conn1 = CreateObject( "ADODB.Connection.1.5" )

C++ Using #import

   #import "C:\Program Files\Common Files\System\ado\msado15.dll" \
           rename( "EOF", "adoEOF" )
   ...
   _variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);
   _variant_t  vtEmpty2(DISP_E_PARAMNOTFOUND, VT_ERROR);
   ...
   ADODB::_ConnectionPtr  Conn1;
   ADODB::_CommandPtr     Cmd1;
   ADODB::_ParameterPtr   Param1;
   ADODB::_RecordsetPtr   Rs1;

   // Trap any error/exception.
   try
   {
       // Create and Open Connection Object.
       Conn1.CreateInstance( __uuidof( ADODB::Connection ) );
       Conn1->ConnectionString =
                           _bstr_t(L"DSN=Biblio;UID=adimin;PWD=;");
       Conn1->Open( _bstr_t(L""), _bstr_t(L""), _bstr_t(L""), -1 );

       // Create Command Object.
       Cmd1.CreateInstance( __uuidof( ADODB::Command ) );
       Cmd1->ActiveConnection = Conn1;
       Cmd1->CommandText = _bstr_t(L"SELECT * FROM Authors "
                                   L"WHERE Au_ID < ?");

       // Create Parameter Object.
       Param1 = Cmd1->CreateParameter( _bstr_t(L""),
                                       ADODB::adInteger,
                                       ADODB::adParamInput,
                                       -1,
                                       _variant_t( (long) 5) );
       Param1->Value = _variant_t( (long) 5 );
       Cmd1->Parameters->Append( Param1 );

       // Open Recordset Object.
       Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, ADODB::adCmdText );

   }

   catch( CException *e ) { e->Delete(); }
   catch(...)             {  }

Java

   msado15._Connection  Conn1   = new msado15.Connection();
   msado15._Command     Cmd1    = null;
   msado15._Recordset   Rs1     = new msado15.Recordset();
   boolean              bEOF;

   Variant              v1      = new Variant();
   Variant              v2      = new Variant();

   // Trap any error/exception.
   try
   {
       // Create and Open Connection Object.
       Conn1.putConnectionString( bstrAccessConnect );
       Conn1.Open( bstrEmpty, bstrEmpty, bstrEmpty, -1 );

       // Create Command Object.
       Cmd1= new msado15.Command();
       Cmd1.putActiveConnection( Conn1 );
       Cmd1.putCommandText( "SELECT * FROM Authors WHERE Au_ID < ?" );

       // Create Parameter Object.
       v1.putString( "P1" );
       v2.putInt( 5 );
       Cmd1.getParameters().Append(
            Cmd1.CreateParameter( v1.getString(),
                         msado15.DataTypeEnum.adInteger,
                         msado15.ParameterDirectionEnum.adParamInput,
                         0,
                         v2 ) );

       // Open Recordset Object.
       Rs1 = Cmd1.Execute( vtEmpty, vtEmpty2,
                           msado15.CommandTypeEnum.adCmdText );
       Rs1.Requery( -1 );

   }
   // Catch Blocks
   catch (com.ms.com.ComFailException e) { }
   catch(Exception e)                    { }

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q172403
   TITLE     : SAMPLE: Adovb.exe Demonstrates How to Use ADO with Visual
               Basic

   ARTICLE-ID: Q174565
   TITLE     : FILE: Adovc.exe Demonstrates How to Use ADO with Visual C++

Keywords          : kbcode adoall adoengdb adogenis adovb adovc adovj
Version           : WINDOWS:1.0,1.5
Platform          : WINDOWS
Issue type        : kbhowto