杂谈 ADO

hyefeifei

一、开始之前的闭言碎语

1. 此前曾经发过一个类模块的贴子，结果没啥反响，太监了，希望这个关于ADO的贴子能得到大家的批评和鼓励，使我坚持到结束。

2. 因水平有限，无法从头说起，只能一个每次说一个题目。

3. 本人环境是office2010+sql server2008

二、进入正题

第一个题目： ADO 如何参数化查询

1.  下面给出查询语句：

语句：

1. Select * from student

复制代码

查询结果：

stu_id	stu_No	stu_name	stu_sal	stu_sex	stu_wID
161	1003	白玉芬	2000	男	2
162	1004	陈国祥	1800	男	2
165	1007	邓海燕	2000	男	2
167	1009	高艳	1000	男	2
171	1013	明汉琴	2600	男	2
172	1014	沈亚杰	1000	女	1
173	1015	时扬	3400	男	1
174	1016	汤丽丽	2000	女	2
175	1017	王丹	3400	女	2
181	1023	张珊珊	2600	男	2
182	1024	郑永军	2600	男	1
185	1027	柏林	1800	女	1

（以上结果只截取部分）

如果我们要返回stu_sal字段2000-5000之间的记录该如何呢？

可以用下面语句就可以得到想要的结果：

语句：

1. select * from student where stu_sal between 2000 and 5000

复制代码

但是，如果我们想用参数代替2000和5000这两个具体的值呢？

我们可以这么办：

语句：

1. P1=2000
   
2. P2=5000
   
3. strSQL = "Select * from student where stu_sal between " & p1 & " and " & p2
   
4. rst.Open strSQL, conn, adOpenStatic, adLockBatchOptimistic, adCmdText

复制代码

这样就完成了参数化查询。

注意recordset的open方法有五个参数，以后有时间再讲，省略后三个在这里不影响结果。

2. 上面既然已完成了参数化查询，为什么还要接着讲呢？其实我也不是很清楚为啥，有人说是为了代码的好看简洁，希望看贴朋友自己思索。

我们利用command对象实现

语句：

1. P1=2000
   
2. P2=5000
   
3. strSQL=”select * from student where stu_sal between ? and ?”
   
4. Set cmd = CreateObject("adodb.command")
   
5. With cmd
   
6.      .activeconnection = conn
   
7.      .CommandType = adCmdText
   
8.      .CommandText = strSQL
   
9.      .Execute , Array(p1,p2)
   
10. End With
    
11. rst.Open cmd, , adOpenStatic, adLockBatchOptimistic, adCmdText

复制代码

说明：

execute 有三个参数

第一个参数基本不用；

第二个参数为一个数组，数组第1个元素对应查询语句第1个问号，第2个元素对应第2个问号，以此类推；

第三个参数为命令类型，因此前CommandType= adCmdText 这句已经设定了命令类型，故省略。

最后一句，即rst.open    ……这句，第一个参数为cmd,因cmd中已经指明了活动连接为conn，所以第二个参数必须省略，否则会出错，后面三个参数可有可无。

待续...

hyefeifei

3. 继续讲command对象

a Command有个参数对象（Parameter）

b利用 “创建参数” 方法（CreateParameter）即可创建该参数对象

c 但是，必须把“参数对象”（Parameter）放入 “参数集合”（Parameters）中，参数才可以被引用

d 如何把“参数对象”放入“参数集合”中呢？答案是用参数集合的 “追加” 方法（append）

e 把参数加入集合后，我们就可以用Parameters(0), Parameters(1)等，来引用参数了

a,b,c,d 四个步骤可用下面一个语句完成：

cmd.Parameters.append cmd.createparameter(,Type, Direction, Size)

下面我们看具体应用：

1. P1=2000
   
2. P2=5000
   
3. strSQL=”select * from student where stu_sal between ? and ?”
   
4. Set cmd = CreateObject("adodb.command")
   
5. With cmd
   
6. .activeconnection = conn
   
7. .Parameters.append .createparameter(, adVarChar, adParamInput, 10) ‘创建第一个参数并加入参数集合
   
8. .Parameters.append .createparameter(, adVarChar, adParamInput, 10) ‘创建第二个参数并加入参数集合
   
9. .Parameters(0) = p1 ‘把参数集合中的第1个参数赋值2000，对应strSQL第1个问号
   
10. .Parameters(1) = p2 ‘把参数集合中的第2个参数赋值5000，对应strSQL第2个问号
    
11. .CommandType = adCmdText
    
12. .CommandText = strSQL
    
13. .Execute
    
14. End With
    
15. rst.Open cmd, , adOpenStatic, adLockBatchOptimistic, adCmdText

复制代码

说明：下面讲一下唯一需要讲的语句：createparameter(,Type, Direction, Size)

创建参数方法（createparameter）有五个参数，只需用示例中的三个即可，所以只讲这三个参数

Type:设置参数的数据类型，接受DataTypeEnum 中的值。

Direction:设置这个参数是输入参数还是输出参数，省略或设为1，即为输入参数，2为输出参数，注意上面的语句用的不是1，而是常量adparamInput这个常量，这个常量接受ParameterDirectionEnum中的某个值。

Size:有的参数象日期参数，数值参数没长度，可省略，有的参数如字符串参数有长度，此参数指定最大长度。

注意 ado所有参数都是长整型（Long），你可以把createparameter(, adVarChar, adParamInput, 10)写成：createparameter(, 200,1, 10)，但把参数全部写为常量，是个很好的习惯，那么上面所说的DataTypeEnum，ParameterDirectionEnum上哪里找呢？答案是见附件。

另外，我的习惯是全部后期引用，后期引用的问题在于，不能引用ado的内置常量，解决办法是建一个模块，在里面声明所有常量，或者你喜欢，也可以只声明用得着的常量，各位下载附件后，把声明复制到模块中即可。

补充：

上面.Parameters(0) = p1 .Parameters(1) = p2，两个参数的值都是整数

而我们创建参数的语句：

.Parameters.append .createparameter(, adVarChar, adParamInput, 10)

adVarChar 指明的是字符串，且最长为10

这是可以的，但我们当然可以把参数类型设为整型，整型没有长度一说，所以第三个参数可省略。

我们可用下面语句取代前面创建参数的语句：

.Parameters.append .createparameter(, adInteger, adParamInput)

先到这里，已经讲了三种实现参数化查询的方式，你认为该用哪种呢？

ADO常量附件： ado常量.rar (8.72 KB, 下载次数: 326)

2015-10-7 08:43 上传

点击文件名下载附件

zhouzhongchi

沙发？拜读大作。终于明白以前看到的M Query里的？原来是参数查询。
平时都用的& p1 & " and " & p2这样进行查询。不知Command对象适合什么情况下使用，什么情况必需使用？
楼主的类模块帖子受益非浅，对类模块有了初步认识。

wangqh8203

当初我也是用excel连接mysql数据库，整到吐血。论坛相关的教程也太少，谢谢发帖交流

hyefeifei

继续前，先给一个完整的代码，以免初学者疑惑：

1. Public conn As Object
   
2. Public rst As Object
   
3. Sub Connect()
   
4.     Dim strConn As String
   
5.     Const strIP As String = "你的sqlserver服务器ip"
   
6.     Set conn = CreateObject("adodb.Connection")
   
7.     Set rst = CreateObject("adodb.Recordset")
   
8.     conn.cursorlocation = adUseClient
   
9.     rst.cursorlocation = adUseClient
   
10.     strConn = "Provider=SQLOLEDB;" & _
    
11.               "Data Source=" & strIP & ";" & _
    
12.               "Initial Catalog=数据库名称;" & _
    
13.               "User ID=用户名称;Password=用户密码;"
    
14.     conn.Open strConn
    
15.     conn.Close
    
16. End Sub
    
17. 
    
18. Sub test()
    
19.     Dim strSQL As String
    
20.     Dim i As Long
    
21.     Dim cmd as object
    
22.     Dim p1, p2
    
23.     If conn Is Nothing Then Connect
    
24.     If conn.State = 0 Then conn.Open
    
25.     p1 = 1000
    
26.     p2 = 5000
    
27.     strSQL = "Select * from student where stu_sal between ? and ?"
    
28.     Set cmd = CreateObject("adodb.command")
    
29.     With cmd
    
30.         .activeconnection = conn
    
31.         .Parameters.append .createparameter(, adInteger, adParamInput)    '创建第一个参数并加入参数集合
    
32.         .Parameters.append .createparameter(, adInteger, adParamInput)    '创建第二个参数并加入参数集合
    
33.         .Parameters(0) = 1000   '把参数集合中的第1个参数赋值2000，对应strsql第1个问号
    
34.         .Parameters(1) = 4000   '把参数集合中的第2个参数赋值5000，对应strsql第2个问号
    
35.         .CommandType = adCmdText
    
36.         .CommandText = strSQL
    
37.         .Execute
    
38.     End With
    
39.     rst.Open cmd, , adOpenStatic, adLockBatchOptimistic, adCmdText
    
40.     Sheet1.Cells.Clear
    
41.     If Not rst.EOF Then
    
42.         For i = 0 To rst.Fields.Count - 1
    
43.             Sheet1.Cells(1, i + 1).Value = rst.Fields(i).Name
    
44.         Next
    
45.         Sheet1.Cells(2, 1).CopyFromRecordset rst
    
46.     End If
    
47.     rst.Close
    
48.     conn.Close
    
49. End Sub

复制代码

hyefeifei

我们继续：

4. 存储过程1

先来说一下如何在sql服务器上创建存储过程，很简单，用如下代码即可：

1. Sub CreateProc()
   
2. Dim strConn As String
   
3.     Dim strSQL As String
   
4.     Const strIP As String = "sqlserver IP"
   
5.     Set conn = CreateObject("adodb.Connection")
   
6.     Set rst = CreateObject("adodb.Recordset")
   
7.     strConn = "Provider=SQLOLEDB;" & _
   
8.               "Data Source=" & strIP & ";" & _
   
9.               "Initial Catalog=数据库名;" & _
   
10.               "User ID=用户名;Password=密码;"
    
11.     strSQL = "create proc GetUL" & _
    
12.              " @p1 int," & _
    
13.              " @p2 int" & _
    
14.              " as" & _
    
15.              " select * from student where stu_sal between @p1 and @p2"
    
16.     conn.Open strConn
    
17.     conn.Execute strSQL
    
18.     conn.Close
    
19. End Sub

复制代码

我们有了存储过程，那么，在vba里如何引用存储过程呢？

其实很简单，我们看前面2的代码：

With cmd

     .activeconnection = conn

     .CommandType = adCmdText

     .CommandText = strSQL

     .Execute , Array(p1,p2)

End With

把其中的命令类型改为：.CommandType= adCmdStoredProc

               命令文本改为： CommandText=”GetUL”   GetUL 为我们刚创建的存储过程名

即可，完整代码如下：

P1=2000

P2=5000

Set cmd = CreateObject("adodb.command")

With cmd

     .activeconnection = conn

     .CommandType = adCmdStoredProc

     .CommandText = ”GetUL”

     .Execute , Array(p1,p2)

End With

rst.Opencmd, , adOpenStatic, adLockBatchOptimistic

为了提高效率，你可以修改代码如下：

1. P1=2000
   
2. P2=5000
   
3. Set cmd = CreateObject("adodb.command")
   
4. With cmd
   
5.      .activeconnection = conn
   
6.      .CommandType = adCmdStoredProc
   
7.      .CommandText = ”GetUL”
   
8.      Set rst=.Execute(,Array(p1,p2))
   
9. End With

复制代码

待续......

hyefeifei

5. 存储过程2

我们看一下cmd 的commandType属性：

CommandTypeEnum  Values
Constant	Value	Description
adCmdText	1	The query will not be modified by ADO.
adCmdTable	2	ADO will append "select * from " to the query.
adCmdStoredProc	4	ADO will format the query as a call to a stored procedure; for  example: {? = CALL MyProc (?)}.
adCmdUnknown	8	Default value; ADO will try different methods of executing the  query until the query succeeds.
adCmdFile	256	Indicates that the CommandText property refers to a filename. Not  applicable to the Command object. Applicable to the Open method on the  Recordset object.
adCmdTableDirect	512	ADO will use an optional but advanced set of OLE DB API calls to  retrieve all rows and columns from the table name specified. Not applicable  to the Command object. Applicable to the Open method on the Recordset object.

（上面的图表来自ADO编程技术一书，我已经把书转成word文档（见附件），各位下载打开后，点视图-导航窗格，可见各级标题已经做好了）

当该属性设为adCmdStoredProc时，实际上ado是把命令转成{? = CALL MyProc (?)}的方式提交服务器。

所以我们可以直接用下面的语句进行查询：

"{Call GetUL('2000','5000')}"  此语句查询2000-5000之间的记录

但是我们需要把2000，5000，替换成参数

完整语句如下：

1. p1 = 2000
   
2. p2 = 5000
   
3. Set cmd = CreateObject("adodb.command")
   
4.     With cmd
   
5.         .activeconnection = conn
   
6.         .CommandType = adCmdText              ‘这里的命令类型，就不要用存储过程了，而应该用文本类型
   
7.         .CommandText = "{Call GetUL('" & p1 & "','" & p2 & "')}"
   
8.         Set rst=.Execute
   
9.     End With

复制代码

ado编程技术附件： ADO编程技术.rar (1.32 MB, 下载次数: 538)

2015-10-7 11:20 上传

点击文件名下载附件

hyefeifei

6. 存储过程3

我们可以修改一下5. 中的代码，一样可以正常运行：

语句如下：

1. p1 = 2000
   
2. p2 = 5000
   
3. Set cmd = CreateObject("adodb.command")
   
4.     With cmd
   
5.         .activeconnection = conn
   
6.         .CommandType =adCmdStoredProc      ‘注意此处的命令类型
   
7.         .CommandText ="GetUL('" & p1 & "','" & p2 &"')"
   
8.         Set rst=.Execute
   
9.     End With

复制代码

7. 存储过程 4

我们知道，sql通过exec 来执行存储过程，所以我们用vba把sql语句直接发给服务器，代码是：

Exec GetUL '2000','5000'

我们把2000，5000用p1,p2替换，完整语句如下：

1. With cmd
   
2.        .activeconnection = conn
   
3.        .CommandType = adCmdText              ‘这里要用文本类型
   
4.        .CommandText = "Exec GetUL '"& p1 & "','" & p2 & "'"
   
5.        Setrst= .Execute
   
6. End With

复制代码

8. 存储过程5

最后，我们看一下ado引用存储过程的标准代码：

1. With cmd
   
2.       .activeconnection = conn
   
3.       .Parameters.append .createparameter(,adInteger, adParamInput)   ' 创建第一个参数并加入参数集合
   
4.       .Parameters.append .createparameter(,adInteger, adParamInput)   ' 创建第二个参数并加入参数集合
   
5.       .Parameters(0) =p1                                                              ' 把参数集合中的第1个参数赋值2000，对应存储过程第1个参数
   
6.       .Parameters(1) =p2                                                              ' 把参数集合中的第2个参数赋值5000，对应存储过程第2个参数
   
7.       .CommandType = adCmdStoredProc
   
8.       .CommandText = "GetUL"
   
9.       Set rst=.Execute
   
10. End With

复制代码

----完

下一次预计讲一下临时表或者插入更新删除

岗纯一郎

楼主可否帮我解决一下问题？
http://club.excelhome.net/thread-1233800-1-1.html

gtwlren

这个例子不错