borrow.txt

经过几个月的设计和开发

Option Explicit

Private Sub Command1_Click()               '借出图书
Dim sql, sql1, sql2 As String
Dim num As Integer
sql = "select * from record"
sql1 = "select * from worker where [工号]=?"
sql2 = "select * from book where [图书编码]=?"
Dim param1, param2, param3, param4 As ADODB.Parameter
Dim cmd1, cmd2, cmd3, cmd4 As ADODB.Command
Set cmd1 = New ADODB.Command
Set cmd2 = New ADODB.Command
Set cmd3 = New ADODB.Command
Set cmd4 = New ADODB.Command
Set param1 = New ADODB.Parameter
Set param2 = New ADODB.Parameter
Set param3 = New ADODB.Parameter
Set param4 = New ADODB.Parameter
'工号及书号检测
'检验工号及书号的合法性
    With param1
      .Direction = adParamInput
      .Type = adBSTR
      .Size = 8
      .Value = Text1.Text
    End With
    
    cmd1.Parameters.Append param1
    cmd1.CommandText = sql1
    cmd1.CommandType = adCmdText
   Set cmd1.ActiveConnection = con
   Set rst = cmd1.Execute
   If rst.RecordCount <= 0 Then
     frmMsg.Show
     frmMsg.notice.Visible = True
  frmMsg.Text1.Text = "没有此工号!"
     Exit Sub
   End If
   
    
    With param2
      .Direction = adParamInput
      .Type = adBSTR
      .Size = 8
      .Value = Text2.Text
    End With
    
    cmd2.Parameters.Append param2
    cmd2.CommandText = sql2
    cmd2.CommandType = adCmdText
   Set cmd2.ActiveConnection = con
   Set rst1 = cmd2.Execute
   If rst1.RecordCount <= 0 Then
      frmMsg.Show
      frmMsg.notice.Visible = True
  frmMsg.Text1.Text = "没有此图书编号!"
      Exit Sub
   End If
   
   sql2 = "select * from record where [图书编码]=?"
    rst1.Close
    With param3
      .Direction = adParamInput
      .Type = adBSTR
      .Size = 8
      .Value = Text2.Text
    End With
    
     cmd3.Parameters.Append param3
     cmd3.CommandText = sql2
     cmd3.CommandType = adCmdText
   Set cmd3.ActiveConnection = con
   Set rst1 = cmd3.Execute
   '图书状态检测
   If rst1.RecordCount > 0 Then
      frmMsg.Show
      frmMsg.notice.Visible = True
  frmMsg.Text1.Text = "此图书已经被借阅！"
      Exit Sub
    End If
    
    sql1 = "select * from record where [工号]=?"
    rst.Close
    With param4
      .Direction = adParamInput
      .Type = adBSTR
      .Size = 8
      .Value = Text1.Text
    End With
    
    cmd4.Parameters.Append param4
    cmd4.CommandText = sql1
    cmd4.CommandType = adCmdText
   Set cmd4.ActiveConnection = con
   Set rst = cmd4.Execute
    '规定可借阅图书数目检测
    If rst.RecordCount = 0 Then
        num = rst.RecordCount + 1
        Label3.Caption = str(num)
    ElseIf rst.RecordCount <= 3 Then
       num = rst.RecordCount + 1
       Label3.Caption = str(num)
    ElseIf rst.RecordCount > 3 Then
      frmMsg.Show
      frmMsg.notice.Visible = True
  frmMsg.Text1.Text = "借阅图书已超限（>4）!"
      Label3.Caption = str(rst.RecordCount)
      Exit Sub
    End If
       
    rst.Close
    rst.Open sql, con, adOpenDynamic, adLockOptimistic
  
    rst.AddNew
    rst![工号] = Text1.Text
    rst![图书编码] = Text2.Text
    rst![借阅时间] = Date
    rst.Update
    frmMsg.Show
    frmMsg.info.Visible = True
  frmMsg.Text1.Text = "借阅成功！"

End Sub

Private Sub Command2_Click()
Unload Me
End Sub

Private Sub Form_Load()
Dim sql1, sql2 As String
sql1 = "select * from book"
sql2 = "select * from worker"
Set rst = New ADODB.Recordset
Set rst1 = New ADODB.Recordset
Call connect_db
rst.Open sql2, con, adOpenDynamic, adLockOptimistic
rst1.Open sql1, con, adOpenDynamic, adLockOptimistic
End Sub


Private Sub Image1_Click()

End Sub

Private Sub xptopbuttons1_Click()
Unload Me
End Sub

Private Sub xptopbuttons2_Click()
Me.WindowState = 1
End Sub