NOTES中动态表格的实现

uselsx “*lsxodbc”

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