How To Invoke a Parameterized ADO Query Using VBA/C++/Java
来源:互联网 发布:11.4恒大淘宝的比赛 编辑:程序博客网 时间:2024/05/01 12:45
http://support.microsoft.com/?id=181734
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 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
#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 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. REFERENCES
For additional information, please see the following articles in the Microsoft Knowledge Base:
172403 SAMPLE: Adovb.exe Demonstrates How to Use ADO with Visual Basic
220152 Sample: ADOVC1 Simple ADO/VC++ Application
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 Service Pack 2 |
- How To Invoke a Parameterized ADO Query Using VBA/C++/Java
- How to Find a Blank Cell in Excel Using Vba
- illustrates how to perform a SELECT statement using ADO.NET
- How to invoke a JSF managed bean on a HTML DOM event using native JavaScript?
- How to invoke a trained TensorFlow model from Java programs
- How to invoke Java web service in ASP.net using C#
- How to read and save Images in a Sql Server Database using ADO.NET and C#
- How to make a Web crawler using Java?
- How to execute a query in varchar2 ?
- How to Improve Query Performance - A Checklist
- How to Call Java functions from C Using JNI
- How to Call Java Functions from C Using JNI
- Java Invoke C and C++ Using JNI
- How to Login Automatically into Website Using Excel VBA
- How to Submit a Form Using JavaScript
- how to build a codec using lib
- How to invoke a function from another .cpp source file
- How to avoid using() mistake in C#?
- 网络时代计算机病毒发展的新趋势
- 在rational rose的sequence图中表示条件逻辑。
- VC 7.0 中使用CDaoRecordset 类/使用Dao 方法操作数据库
- 今天开始用电驴(e-mule)
- 今天关于研究mysql的sql语句所积累的……
- How To Invoke a Parameterized ADO Query Using VBA/C++/Java
- 打印机十大共性故障的解决方法
- java初学者必读-经验总结
- 快速配置weblogic8.X的应用目录
- 国内破解站点大全!
- 高手教你一招:网络共享打印变零为整
- 小结面向对象的类的设计方式
- J2ME的个人见解
- 无聊一日