现在我正在用VB写一个考勤系统,用于实验室考勤查询,可不知道怎么和数据库对比时间!
我的数据库是SQL2000!
考勤查询分正常和缺勤,迟到和早退,数据库里的时间包括上机时间和下机时间,内容为日期+时间。要求在表格里显示考勤情况,程序不难,可我是个初学者,不知道怎么写SQL存储过程,所以好无头绪。
很急~~~~~
高手能来帮帮忙吗,先谢谢了~~~~~
我的数据库是SQL2000!
考勤查询分正常和缺勤,迟到和早退,数据库里的时间包括上机时间和下机时间,内容为日期+时间。要求在表格里显示考勤情况,程序不难,可我是个初学者,不知道怎么写SQL存储过程,所以好无头绪。
很急~~~~~
高手能来帮帮忙吗,先谢谢了~~~~~
Private adocnn As ADODB.ConnectionPrivate Sub Command1_Click()
Dim onworktime As Date
Dim offwortime As Date
Dim timeon As Date
Dim timeoff As Date
Dim empno As String
Dim empname As String
Dim empstate As String
Dim sql1 As String
Dim rs1 As ADODB.Recordsetempstate = "正常"
onworktime = CDate("08:00")
offwortime = CDate("15:00")sql1 = "select on_date,on_time,off_time,emp_no,emp_name,empstate from checkon where on_date='" & _
CStr(Format(Now, "yyyy-mm-dd")) & "' "
rs1.Open sql1, adocnn, adOpenKeyset, adLockBatchOptimistic, adCmdText
timeon = rs1.Fields("on_time")
timeoff = rs1.Fields("off_time")
empno = rs1.Fields("emp_no")
empname = rs1.Fields("emp_name")
'empstate = rs1.Fields("emp_state")
'rs1.CloseIf (timeon > onworktime) Then
empstate = "迟到"
End IfIf (timeoff < offworktime) Then
empstate = "早退"
End If'sql2 = "update set emp_state='" & empstate & "' from checkon where emp_no='" & empno & "'"
'rs1.Open sql2, adocnn, adOpenKeyset, adLockBatchOptimistic, adCmdText
rs1.Fields("emp_state") = empstate
rs1.UpdateBatch
rs1.Close
End SubPrivate Sub Form_Load()
On Error GoTo ErrorHandler
Dim cnnstr As String
cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\mytest\vb\opctest\database\db1.mdb;" & "Persist Security Info=False"
If adocnn Is Nothing Then
Set adocnn = New ADODB.Connection
adocnn.ConnectionString = cnnstr
adocnn.Open
End IfExit Sub
ErrorHandler:
Set adocnn = Nothing
MsgBox "与ACCESS数据建立连接时出错Error: " & Err.Description, vbCritical, "ERROR"End Sub
on_date 当天日期 日期型
on_time 上班时间 日期型
off_time 下班时间 日期型
emp_no 员工编号 字符型(上面的程序是对应字符型,你可以修改,但注意类型一致)
emp_name 员工姓名 字符型
emp_state 状态 字符型
其实最好的做法是把checkon表做成对象,其有on_date,on_time,off_time,emp_no,emp_name,emp_state属性,这样更灵活些
Dim timeon As Date
Dim timeoff As Date
Dim empno As String
Dim empname As String
Dim empstate As String
还要在循环取数据和更新,具体改一下就行了。