uselsx “*lsxodbc”
在NOTES中创建请销假表单，并创建一个“操作”，名为“登记”，当用户完成请假流程后此操作将出现。以下编码首先读取请假人的姓名和请假的时间，判断是否由此记录，如有则更新记录，如没有则插入记录。其编码为：
Sub Click(Source As Button) Dim workspace As New NotesUIWorkspace Dim uidoc As NotesUIdocument． Set uidoc = workspace.Currentdocument． Dim con As New ODBCConnection Dim qry As New ODBCQuery Dim result As New ODBCResultSet Dim firstName As String Dim lastName As String Dim msg As String '声明数据库对象 Set qry.Connection = con Set result.Query = qry con.silentmode=True Dim ts As Integer If con.ConnectTo("gwgl","sa","") Then yf=uidoc.fieldgettext("dn") ….. nf=uidoc.fieldgettext("nf") '取得NOTES 表单中域的值 Dim odate As Variant sta1=Cint(Right(uidoc.fieldgettext ("xj_sta1"),2)) end1=Cint(Right(uidoc.fieldgettext ("xj_end1"),2) '取得假期的开始和结束时间 schstr="select qxj_xm from qxj where qxj_xm='" & xm &"' and year='" & nf & "' and month='" & yf & "'" qry.sql=schstr result.execute If result.IsResultSetAvailable Then ‘判断是否存在该数据 Dim n As Integer Const qz="qxj_" For i=sta1 To end1 ym=qz & i schstr="update qxj set " & ym &"='" & lx & "' where qxj_xm=" & "'" & xm & "'" n=n+1 Messagebox schstr qry.sql=schstr result.execute Next schstr="update qxj set " & lx &"='" & n & "' where qxj_xm=" & "'" & xm & "'" qry.sql=schstr result.execute p=ts-n schstr="update qxj set qxj_sc='" & p & "' where qxj_xm=" & "'" & xm & "'" qry.sql=schstr result.execute Else '存在该数据进行插入操作 Dim intstr As String intstr="insert qxj (qxj_xm,year,month,qxj_sc) values('" & xm & "','" & nf & "','" & yf & "','" & ts & "')" qry.sql=intstr result.execute Const qz1="qxj_" For i=sta1 To end1 ym=qz1 & i schstr="update qxj set " & ym &"='" & lx & "' where qxj_xm=" & "'" & xm & "' and year='" & nf & "' and month='" & yf & "'" n=n+1 qry.sql=schstr result.execute schstr="update qxj set " & lx &"='" & n & "' where qxj_xm=" & "'" & xm & "' and year='" & nf & "' and month='" & yf & "'" qry.sql=schstr result.execute p=ts-n schstr="update qxj set qxj_sc='" & p & "' where qxj_xm=" & "'" & xm & "' and year='" & nf & "' and month='" & yf & "'" qry.sql=schstr result.execute Next End If End If End Sub
Sub Click(Source As Button) Dim workspace As New NotesUIWorkspace Dim uidoc As NotesUIdocument． Set uidoc = workspace.Currentdocument． Dim con As New ODBCConnection Dim qry As New ODBCQuery Dim result As New ODBCResultSet Dim firstName As String Dim lastName As String Dim msg As String Set qry.Connection = con Set result.Query = qry con.silentmode=True nf=uidoc.fieldgettext("nf") yf=uidoc.fieldgettext("yf") If con.ConnectTo("gwgl","sa","") Then '连接到数据库 qry.SQL = "SELECT * FROM qxj where year='" & nf & "'and month='" & yf & "'" '按下图表中用户输入的年份和月份取得的数据 result.Execute If result.IsResultSetAvailable Then Do result.NextRow qxj_xm = result.Getvalue("qxj_xm") st=st & Chr(10) & qxj_xm Call uidoc.fieldsettext("xm_1",st) tq = result.Getvalue("探亲") t= t & Chr(10) & tq Call uidoc.fieldsettext("tq",t) b = result.Getvalue("病") bing= bing & Chr(10) & b Call uidoc.fieldsettext("bing",bing) …………….. Loop Until result.IsEndOfData Call uidoc.save result.Close(DB_CLOSE) Else Messagebox “对不起，不能连接到数据库！！" Exit Sub End If con.Disconnect Else Messagebox "不能连接到数据源！！" '网络不通或其他原因不能 '连接到数据库给出提示 Exit Sub End If End Sub
