Microsoft KB Archive/181734

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

Article ID: 181734

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5

-



This article was previously published under Q181734



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 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.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=admin;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 For VBScript users, you would replace the Dim statements with equivalent CreateObject calls, such as: Set conn1 = CreateObject( "ADODB.Connection.1.5" ) As ADO 1.x is not binary compatible, it is helpful to specify which version of ADO your script is referencing.

C++ Using #import
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;
 * 1) import "C:\Program Files\Common Files\System\ado\msado15.dll" \

// 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 classes generated by the Microsoft Foundation Class (MFC) ClassWizard, or using straight COM programming, please see the ADOVC sample referenced in the REFERENCES section.

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)                   { } In this case, the requery may be necessary after opening the recordset.

