ls的ODBC使用问题。

davidland 2004-11-05 03:46:35

Sub Initialize
Dim session As notessession
Dim db As notesdatabase
Set session=New notessession
Set db=session.currentdatabase

Dim col As notesdocumentcollection '找到的新的关系型数据库
Dim search_doc As notesdocument '每个找到的文档

Dim Con1 As ODBCConnection 'ODBC连接
Dim qry1 As New ODBCQuery '查询对象
Dim result1 As New ODBCResultSet '记录集合
Set Con1=New ODBCConnection

'连接关系型数据库
Set qry1.Connection = Con1
Set result1.Query = qry1
If Con1.ConnectTo("yjsdata") Then
'流程库操作
'清空所有的关系型数据库中非报告书的数据
Call clearAll(qry1,result1)
'查找所有的药品登记表单
searchFormula$ = {Form = "HomeRegister"}
Set col=db.search(searchFormula$,Nothing,0)
Msgbox(Str(col.count))
i=0
If col.count>0 Then

Set search_doc=col.getfirstdocument
'Set search_doc=col.getnthdocument(484)
Do While (Not search_doc Is Nothing)
i=i+1
Print i
If search_doc.UniversalID<>"" Then
Call homeAddrow(search_doc,con1,qry1,result1,0)
End If
Set search_doc=col.getnextdocument(search_doc)
Loop
End If
con1.Disconnect
End If
End Sub

Sub homeAddrow(doc As notesdocument,con As ODBCConnection,qry As ODBCQuery,result As ODBCresultset,sign As Integer)
Dim mysql As String

'检查检品编号相同的文档是否已经记录
If checkJpbh(doc.web_jpbh(0),qry,result)=0 Then
mysql="Insert into home ("
'插入的字段
mysql=mysql & "ID," 'ID
mysql=mysql & "web_jpbh," '检品编号
mysql=mysql & "web_ypdl," '药品大类
mysql=mysql & "web_ypfl," '药品细类
mysql=mysql & "shoujianriqi," '收检日期
mysql=mysql & "web_checkname," '检品名称
mysql=mysql & "produce," '生产单位
mysql=mysql & "web_gydw," '供样单位
mysql=mysql & "web_type," '单位性质
mysql=mysql & "web_gg," '规格
mysql=mysql & "web_bz," '包装
mysql=mysql & "web_jx," '剂型
mysql=mysql & "web_ph," '批号
mysql=mysql & "web_jymddl," '检验目的大类
mysql=mysql & "web_jymd," '检验目的
mysql=mysql & "web_jyxm," '检验项目
mysql=mysql & "web_dxxm," '单项(检验项目)
mysql=mysql & "pzwh," '批准文号
mysql=mysql & "scd," '生产所在地
mysql=mysql & "web_jyyj," '检验依据
mysql=mysql & "web_lyqs," '留样签收
mysql=mysql & "tsly," '是否特殊留样
mysql=mysql & "web_jbr," '经办人
mysql=mysql & "sfjj," '缓急情况
mysql=mysql & "rndnumber," '缓急情况
mysql=mysql & "allJpsf," '检验费用
mysql=mysql & "allJpjj," '加急费用
mysql=mysql & "qtfy," '其他费用
mysql=mysql & "hjfy," '合计费用
mysql=mysql & "expResult," '检验结果
mysql=mysql & "chaoshi," '是否超时
mysql=mysql & "originalDocUNID," '检品登记UNID
If Cstr(doc.ksfinishdate(0))<>""Then
mysql=mysql & "ksfinishdate," '科室期限
End If
If sign=1 Then
mysql=mysql & "web_bgrq," '报告时间
mysql=mysql & "reportbgbh," '报告书编号
mysql=mysql & "reportgdbh," '归档编号
mysql=mysql & "不符合项目," '不符合项目
mysql=mysql & "reportUNID," '报告书UNID
End If
mysql=mysql & "sign)" '是否是报告书标志

'插入的值
mysql=mysql & " values('"
mysql=mysql & getID(doc.web_jpbh(0)) & "','" 'ID
mysql=mysql & doc.web_jpbh(0) & "','" '检品编号
mysql=mysql & doc.web_ypdl(0) & "','" '药品大类
mysql=mysql & doc.web_ypfl(0) & "'," '药品细类
mysql=mysql & "#" & doc.shoujianriqi(0) & "#,'" '收检日期
mysql=mysql & doc.web_checkname(0) & "','" '检品名称
mysql=mysql & doc.produce(0) & "','" '生产单位
mysql=mysql & doc.web_gydw(0) & "','" '供样单位
mysql=mysql & doc.web_type(0) & "','" '单位性质
mysql=mysql & doc.web_gg(0) & "','" '规格
mysql=mysql & doc.web_bz(0) & "','" '包装
mysql=mysql & doc.web_jx(0) & "','" '剂型
mysql=mysql & doc.web_ph(0) & "','" '批号
mysql=mysql & doc.web_jymddl(0) & "','" '检验目的大类
mysql=mysql & doc.web_jymd(0) & "','" '检验目的
mysql=mysql & tranjyxm(doc.web_jyxm(0)) & "','" '检验项目
mysql=mysql & doc.web_dxxm(0) & "','" '单项(检验项目)
mysql=mysql & doc.pzwh(0) & "','" '批准文号
mysql=mysql & doc.scd(0) & "','" '生产所在地
mysql=mysql & doc.web_jyyj(0) & "','" '检验依据
mysql=mysql & doc.web_lyqs(0) & "','" '留样签收
mysql=mysql & doc.tsly(0) & "','" '是否特殊留样
mysql=mysql & doc.web_jbr(0) & "','" '经办人
mysql=mysql & doc.sfjj(0) & "','" '缓急情况
mysql=mysql & doc.rndnumber(0) & "'," '随机号
mysql=mysql & doc.allJpsf(0) & "," '检验费用
mysql=mysql & doc.allJpjj(0) & "," '加急费用
mysql=mysql & doc.Qtfy(0) & "," '其他费用
mysql=mysql & doc.hjfy(0) & ",'" '合计费用
mysql=mysql & doc.expResult(0) & "','" '实验结果
mysql=mysql & doc.chaoshi(0) & "','" '是否超时
mysql=mysql & doc.originalDocUNID(0) & "'," '检品登记UNID
If Cstr(doc.ksfinishdate(0))<>""Then
mysql=mysql & "#" & doc.ksfinishdate(0) & "#," '科室期限
End If
If sign=1 Then
mysql=mysql & "#" & doc.web_bgrq(0) & "#,'" '报告时间
mysql=mysql & doc.reportbgbh(0) & "','" '报告书编号
mysql=mysql & doc.reportgdbh(0) & "','" '归档编号
mysql=mysql & doc.getitemvalue(".不符合项目")(0) & "','" '不符合项目
mysql=mysql & doc.UniversalID & "'," '报告书UNID
End If
mysql=mysql & "'" & Cstr(sign) &"')" '是否是报告书标志
'Msgbox mysql
qry.SQL=mysql
result.Execute
End If
End Sub
代理在客户端手动运行。当文档达到1044条时，弹出红色错误提示，接着客户端自动关闭。
请教高手如何解决。

...全文