VB6 SQL参数化查询模块

最新推荐文章于 2019-11-06 07:58:03 发布

转载最新推荐文章于 2019-11-06 07:58:03 发布 · 238 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/xiaoguanqiu/archive/2013/05/09/3068859.html

本文介绍了一种在VB6环境下封装SQL查询的方法，通过自定义函数AdoOpenRS来简化带有多个参数的SQL语句的编写过程，提高代码的可读性和维护性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

　　　熟悉VB6的朋友在写带有多个参数的SQL语句的时候，查询脚本的参数是用&或+号连接起来吗，只有几个参数是尚可，当参数多时可读性就差多了。为了免去麻烦，因此做了此函数。

虽然ADD的Command对象有Parameters属性，但还是自己封装了带参数的查询方法，有部分参考了宁静兄的：

 1 Public Function AdoOpenRS(ByRef wADORecordSet As ADODB.Recordset, _
 2                                 sqlSELECTCommand As String, _
 3                        Optional Param As Variant = "", _
 4                        Optional ActiveADOConn As ADODB.Connection = Nothing, _
 5                        Optional ShowMsgBoxIfFail As Boolean = True, _
 6                        Optional CloseRSIfFail As Boolean = True, _
 7                        Optional SetRSNothingIfFail As Boolean = True, _
 8                        Optional sRetErrDesp As String = "") As Boolean
 9                        
10            'Param  參數數組
11 On Error GoTo errOpenRs
12           If Len(sqlSELECTCommand) = 0 Then Exit Function
13            
14           
15           If TypeName(Param) = "Variant()" Then
16             Dim i As Byte
17             For i = 1 To UBound(Param)
18               sqlSELECTCommand = Replace(sqlSELECTCommand, "?" & i, Param(i))
19             Next
20           End If
21             
22            wADORecordSet.CursorLocation = adUseClient '客户端游标
23         
24           If ActiveADOConn Is Nothing Then
25             wADORecordSet.Open sqlSELECTCommand, SQLConn, adOpenDynamic, adLockOptimistic
26           Else
27             wADORecordSet.Open sqlSELECTCommand, ActiveADOConn, adOpenDynamic, adLockOptimistic
28           End If
29         
30           Exit Function
31 errOpenRs:
32           If ShowMsgBoxIfFail Then MsgBox Err.Description, vbExclamation, "ErrOpenRs"
33           'If CloseRSIfFail Then wADORecordSet.Close
34           If SetRSNothingIfFail Then Set wADORecordSet = Nothing
35 End Function

　　　　　　调用例子：

 1     Dim strsql As String
 2     Dim Param(1 To 1) As Variant
 3     Dim rs As ADODB.Recordset
 4     Set rs = New ADODB.Recordset
 5 
 6     strsql = "select OrderID From [Order] " & _
 7              " where convert(nvarchar(7),OrderDate,120) = '?1'"
 8     Param(1) = Format(Now, "YYYY-MM")
 9 
10    AdoOpenRS rs, strsql, Param
11    
12

转载于:https://www.cnblogs.com/xiaoguanqiu/archive/2013/05/09/3068859.html