How To Invoke a Parameterized ADO Query Using VBA/C++/Java

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.
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 theParameters Collection with one parameter in the collection for eachparameter in the query, and then use the Command.Execute() method to openthe ADO Recordset.

This article demonstrates how to perform this operation using VBA/VBScript,C++, and Java.
More information
All of the following code samples assume that an ODBC Data Source named "BIBLIO" that points to the Biblio.mdb file that ships with Visual Basic 5.0 and 6.0 exists. 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.

VBA within Visual Basic or Microsoft Access and VBScript from ASP

Dim Conn1 As ADODB.ConnectionDim Cmd1 As ADODB.CommandDim Param1 As ADODB.ParameterDim Rs1 As ADODB.RecordsetDim i As Integer' Trap any error/exception.On Error Resume Next' Create and Open Connection Object.Set Conn1 = New ADODB.ConnectionConn1.ConnectionString = "DSN=Biblio;UID=admin;PWD=;"Conn1.Open' Create Command Object.Set Cmd1 = New ADODB.CommandCmd1.ActiveConnection = Conn1Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?"' Create Parameter Object.Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5)Param1.Value = 5Cmd1.Parameters.Append Param1Set Param1 = Nothing' Open Recordset Object.Set Rs1 = Cmd1.Execute()				
For VBScript users, you would replace the Dim statements with equivalentCreateObject calls, such as:
   Set conn1 = CreateObject( "ADODB.Connection.1.5" )				
As ADO 1.x is not binary compatible, it is helpful to specify which versionof ADO your script is referencing.

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(...)             {  }   				
For a demonstration of how to use a Parameterized Query either with classesgenerated by the Microsoft Foundation Class (MFC) ClassWizard, or usingstraight COM programming, please see the ADOVC sample referenced in theREFERENCES section.


   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 ( e) { }   catch(Exception e)                    { }   				
In this case, the requery may be necessary after opening the recordset.
For additional information, please see the following articles in theMicrosoft Knowledge Base:
172403 SAMPLE: Adovb.exe Demonstrates How to Use ADO with Visual Basic
220152 Sample: ADOVC1 Simple ADO/VC++ Application

Αναγνωριστικό άρθρου: 181734 - Τελευταία αναθεώρηση: 06/22/2014 18:48:00 - Αναθεώρηση: 3.0

  • kbhowto kbmdacnosweep KB181734