ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 51202|回复: 135

[原创] 再讲ADO

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2016-4-1 21:35 | 显示全部楼层 |阅读模式
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖已被收录到知识树中,索引项:ADO技术
本帖最后由 hyefeifei 于 2016-5-16 19:27 编辑

  关于ado这个话题,曾试讲了一次,因效果不甚理想,致半途而废,自己水平有限固是主因,但ADO本身难讲,且用sql数据库的朋友相对不多,不能不说也是原因,此次再讲,不知结果如何,且讲且看吧,如果反响多些,或许多讲些。当然讲的过程,也是与网友交流学习的过程,希望大家多批评指正。

  此次采取以问题为驱动的方式来讲,闲话少叙,进入正题。

  一、如何把excel的数据写入数据库(1)

  例表:(取自本版网友提问贴)

   1.jpg

  程序代码:
Sub InsertFltab()
    Dim cmd As Object
    Dim arr
    Dim i As Long
    Connect "Test"
    Conn.Open
    arr = ThisWorkbook.Worksheets("Fltab").Range("a2:e29")
    Set cmd = CreateObject("adodb.command")
    With cmd
        .CommandText = "INSERT INTO fltab values(?,?,?,?,?)"
        Set .activeconnection = Conn
        For i = 1 To UBound(arr)
            cmd.Execute Parameters:=Array(arr(i, 1), arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 5)), _
                        Options:=adCmdText + adExecuteNoRecords
        Next
    End With
End Sub

  Connect "Test" → 为了不妨碍主要代码,把固定的连接数据库代码做成了单独的程序。附件中会有完整代码。它的意思是连接Test数据库,此前在此数据库上已经建了Fltab表,表的结构如图:
   2.jpg
  Conn.Open→因为在Connect程序中有Conn.close,所以这里要把它打开,另外,Conn在模块顶部声明为:Public Conn as object,各位打开附件一看就明白了。

  arr = ThisWorkbook.Worksheets("Fltab").Range("a2:e29")→这句就不用讲了吧。

  Set cmd = CreateObject("adodb.command")→可以说,在ado中,从来不用command对象,也能完成所有任务,但有时候使用它会简化程序。这里我使用了后期引用,纯属习惯。

  CommandText = "INSERT INTO fltab values(?,?,?,?,?)"→command的commandtext属性设置命令文本,这个属性是必须的,没命令内容,也就无所谓执行命令了。

  INSERT INTO fltab values(?,?,?,?,?)一句,完整应为:
  INSERT INTO fltab (ZY编号,序号,部件,零件,工序) values(?,?,?,?,?)

  这里把字段名省略了,如果你不是插入所有字段的值,比如你只想插入序号,工序这两个字段的值,其他字段都是空值,就不能省了,你必须告诉ado你插入的值对应哪个字段。

  Set .activeconnection = Conn→这个属性设置一个活动连接对象,以便与数据库通信
  cmd.Execute Parameters:=Array(arr(i, 1), arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 5)),Options:=adCmdText + adExecuteNoRecords
  Execute方法执行commandtext属性设置的命令,它有三个参数,咱们一个一个来讲:

  ①RecordsAffected,顾名思义,它返回的是你执行命令,影响了多少条记录。在这里,如果我们使用它,须先声明一个长整形的变量 dim affs as long,代码如下:
cmd.Execute RecordsAffected:=affs,Parameters:=Array(arr(i, 1), arr(i, 2),arr(i, 3),  arr(i, 4), arr(i, 5)),Options:=adCmdText + adExecuteNoRecords
在这里,它返回的始终是1,因为我们每次插入了一条记录。

  ②Parameters,它接受一个数组,这个数组每个值,对应前面的问号。

  ③Options,这个参数是一个位掩码形的参数,各位只须记住adCmdText +
adExecuteNoRecords 这个组合就可以了,adCmdText是说.CommandText属性的内容是sql语句,adExecuteNoRecords是说Execute方法不要返回记录。其他组合基本不必使用。

  位掩码:这里提到位掩码参数,精确解说各位可百度,这里举例说明一下:

  比如说一个对象有abcd四个类型,可用1,2,3,4 表示;又有三种颜色rgb(红绿蓝),可用1,2,3表示,那么我们可以用2个参数 参数甲=2,参数乙=3,表示b型蓝色,但是我们有没有可能使用一个参数来表示b型蓝色呢?当然可以,我们可用 参数丙=2+3来表示,但这里有一个问题,比如,我们要表示a型红色,a型为1,红色为1,我们用 参数丙=1+1 表示,但1+1为2,这里2表示的是b型,也表示绿色,这怎么办?答案在于如何设计参数的值,这里,我们这样设计参数 abcd四个类型 用 2的0次方,2的1次方,2的2次方,2的3次方表示abcd,rgb三种颜色,用2的4次方,2的5次方,2的6次方表示,这样 abcd对应1,2,4,8,rgb对应16,32,64,当我们如此设置了参数值,我们无论如何组合参数,都不会有重复,各位可以试试看。现在,我们要表示a型红色,就可以用 参数丙=1+16来表示,如果我们要表示b型黄色呢?稍懂颜色的人都知道,黄色为红+绿,所以我们可以用 参数丙=2+16+32来表示,2为b型,16为红色,32为绿色。

  说回Options,它可以是下面常量的组合:

adCmdText
1
用SQL语句给CommandText赋值
adCmdTable
2
用表名给CommandText赋值
adCmdStoredProc
4
用存储过程名CommandText赋值
adCmdUnknown
8
指出在CommandText中命令的类型未知
adAsyncExecute
16
异步地执行操作
adAsyncFetch
32
异步地获得记录
adAsyncFetchNonBlocking
64
异步地获得记录,不阻碍随后的操作
adExecuteNoRecords
128
指出CommandText不返回记录

  也许有的朋友注意到,我用的是后期引用,使用的却是adcmdtext这样的常量,实际上,我是把程序中使用的常量,都用 public const adCmdText as long=1这种方式做了声明,毕竟adCmdText这种常量用的久了可以记住,而1234这些数字,再怎么用也难以记住。但如果比如你在asp中用ado的话,那就只能用数字了,记得我在坛里发过ado常量列表,可位可以找找看。

  好了,各位运行以上程序,向数据库插入数据后,进到数据库,查看fltab表,会发现表如图所示:

   3.jpg

  大家看“ZY编号”一列,很显然有错误,为什么会发生错误,如何解决呢?加一条语句就可了,答案在下一讲,有兴趣可先百度一下,我们下次继续讲如何把excel的数据写入数据库。

  本讲附件:
再讲ADO1.rar (27.66 KB, 下载次数: 956)






评分

9

查看全部评分

TA的精华主题

TA的得分主题

发表于 2016-4-1 23:03 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
支持一下!

TA的精华主题

TA的得分主题

发表于 2016-4-1 23:23 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2016-4-2 00:03 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2016-4-2 07:54 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2016-4-2 09:14 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2016-4-3 06:13 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2016-4-3 08:32 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-4-3 10:06 | 显示全部楼层
本帖最后由 hyefeifei 于 2016-5-16 14:49 编辑

  上一讲虽然应者寥寥,还是继续一讲再看吧。

  在回答上讲未了提出的问题前,先讲点题外话:

  
1、虽然是以提出问题的方式讲解,但目的不是为了解决问题,而是为了讲ado一些重要的方法属性参数,问题只是引子,对问题给出的答案并不一定是最佳答案。

  
2、ado虽然可连excel表,但它的出现却是为了连各种数据库的,所以建议大家最好以excel+sql server的方法学习。

  
3、学习之前,应该搭建环境,强烈推荐vmware workstation 这款软件,它甚至可以搭建vsphere环境,强大之处各位可百度一下。有了它,建一个虚拟机,装上sql server2008,学ado就方便多了,如果没有它,也没有局域网环境,这个ado还真没学。

  
4、我之前说过,学vba,最重要的就是ado,为什么?因为通过ado+sql数据库 可以开发多用户程序,二是它可以大大简化vba程序,原先复杂的代码,仅用简单的sql语句代替就可以了,且它的效率一般来说比我们自己写的vba代码要高。其他的好处就不多言了。

  
5、现在电脑成本这么低,在单位弄一台机器做服务器不是很难的事情,如果实在挪腾不出一台电脑做sql服务器,也可以买一台nas,nas上装mysql。

  
题外话到这里。上讲在向数据库插入数据时,出现的问题如截图所示:

   2.jpg

  之所以数据库表的记录的值会被截掉,是因为ado在向数据库插入数据时,并不知道数据库表各字段的数据类型,所以它只好根据第一个插入的记录猜测,第一个记录 zy编号字段的插入值是“ZY9902-1010”,所以ado判断zy编号字段的数据类型是11位的字符串,后面再插入记录,超过11位的,都被截掉了。

  
那么,如何解决这个问题呢?解决方法之一是在ado插入记录前,先让ado到数据库上查一下各字段的数据类型,然后就可以正确插入了。

  
如何让ado到数据库上查字段的数据类型呢?使用Command的Parameters集合的Refresh方法就可以了,新程序如下:

Sub Insert1()
    Dim cmd As Object
    Dim arr
    Dim i As Long
    Connect "Test"
    Conn.Open
    arr = ThisWorkbook.Worksheets("Fltab").Range("a2:e29")
    Set cmd = CreateObject("adodb.command")
    With cmd
        .CommandText = "INSERT INTO fltab values(?,?,?,?,?)"
        Set .activeconnection = Conn
       .Parameters.Refresh                                                ‘此一句为新增
       For i = 1 To UBound(arr)
             cmd.Execute Parameters:=Array(arr(i, 1), arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 5)), _
                        Options:=adCmdText + adExecuteNoRecords
        Next
    End With
End Sub

  
.Parameters.Refresh 会向服务器查询各字段的数据类型,当程序执行到这一句时,ado会向sql服务器提交下面的sql语句:
       SET FMTONLY ON select * from fltab WHERE 1=2  SET FMTONLY OFF

  
这个sql语句只向客户端返回元数据,也就是我们所说的字段信息。有了各字段的信息了,再插入记录就不会出错了。

  
但是,这样做,需要我们在插入记录时,先访问服务器取回字段信息,而这是不必要的,因为做为程序开发者,我们自己是知道各字段的数据类型的,所以不必向数据库服务器取,由我们自己告诉ado各字段数据类型就可以了,这样做无疑会减少服务器的压力。
  
  那么,我们自己如何告诉ado各字段的数据类型呢?下一讲接着讲。



TA的精华主题

TA的得分主题

 楼主| 发表于 2016-4-3 16:09 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 hyefeifei 于 2016-4-3 16:11 编辑
microyip 发表于 2016-4-2 07:54
领教,大师,能解决我之前提出来的问题吗?《求教ODBC的UPDATE问题》http://club.excelhome.net/thread-126 ...

你的这个看了一下,没试你的程序,一般没这样做的,非从excel表更新数据库表的话,可用openrowset函数,
程序如下:
  1.     sSQL_Server =服务器IP
  2.     sSQL_User = 登录名
  3.     sSQL_Password =密码
  4.     Set Conn = CreateObject("adodb.connection")
  5.     strConn = "Provider=SQLOLEDB;" & _
  6.               "Data Source=" & sSQL_Server & ";" & _
  7.               "User ID=" & sSQL_User & ";Password=" & sSQL_Password
  8.     Conn.Open strConn
  9.     Conn.DefaultDatabase =数据库名
  10.     strSQL = "UPDATE a " & _
  11.              "SET    a.字段2 = 'ABCC' " & _
  12.              "FROM   SQLDB AS a " & _
  13.              "      ,OPENROWSET( " & _
  14.              "           'Microsoft.ACE.OLEDB.12.0' " & _
  15.              "          ,'Excel 12.0;HDR=Yes;IMEX=1;Database=excel文件的全路径' " & _
  16.              "          ,'select * from [测试数据表$k1:n]') AS b " & _
  17.              "WHERE  a.字段1 = b.字段1"
  18.     Conn.Execute strSQL
复制代码
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 15:50 , Processed in 0.069311 second(s), 11 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表