borrowinformation.frm

实现图书信息管理

      Begin VB.Label Label3 
         Caption         =   "作者："
         Height          =   255
         Left            =   120
         TabIndex        =   5
         Top             =   1800
         Width           =   615
      End
      Begin VB.Label Label2 
         Caption         =   "书名："
         Height          =   255
         Left            =   120
         TabIndex        =   4
         Top             =   1080
         Width           =   615
      End
      Begin VB.Label Label1 
         Caption         =   "ISBN："
         Height          =   255
         Left            =   120
         TabIndex        =   1
         Top             =   360
         Width           =   615
      End
   End
   Begin VB.Label Label15 
      Caption         =   "*(填入的字符数不得超过10个)"
      ForeColor       =   &H000000FF&
      Height          =   255
      Left            =   1080
      TabIndex        =   35
      Top             =   5880
      Width           =   2535
   End
   Begin VB.Label Label14 
      Caption         =   "索书号："
      Height          =   255
      Left            =   240
      TabIndex        =   30
      Top             =   5520
      Width           =   735
   End
End
Attribute VB_Name = "Frmborrow"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim response

Private Sub Combo1_Change()
cmd.CommandText = "select * from BOOK where ISBN= " & "'" & Trim(Combo1.Text) & "'"
Set rs = cmd.Execute
If rs.EOF = False Then
Text1.Text = rs.Fields(1)
Text2.Text = rs.Fields(2)
Text3.Text = rs.Fields(3)
Text4.Text = rs.Fields(4)
Text5.Text = rs.Fields(5)
Text6.Text = rs.Fields(6)
Else
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
End If
End Sub

Private Sub Combo1_Click()
cmd.CommandText = "select * from BOOK where ISBN= " & "'" & Trim(Combo1.Text) & "'"
Set rs = cmd.Execute
Text1.Text = rs.Fields(1)
Text2.Text = rs.Fields(2)
Text3.Text = rs.Fields(3)
Text4.Text = rs.Fields(4)
Text5.Text = rs.Fields(5)
Text6.Text = rs.Fields(6)
End Sub


Private Sub Combo2_Change()
cmd.CommandText = "select * from XS where 借书证号= " & "'" & Trim(Combo2.Text) & "'"
Set rs = cmd.Execute
If rs.EOF = False Then
    Text7.Text = rs.Fields(1)
    Text8.Text = rs.Fields(2)
    If rs.Fields(3) = "男" Then
        Me.Option1.Value = True
    End If
    If rs.Fields(3) = "女" Then
        Me.Option2.Value = True
    End If
    Text9.Text = rs.Fields(4)
    Text10.Text = rs.Fields(5)
Else
    Text7.Text = ""
    Text8.Text = ""
    Text9.Text = ""
    Text10.Text = ""
    Option1.Value = False
    Option2.Value = False
End If
End Sub

Private Sub Combo2_Click()
cmd.CommandText = "select * from XS where 借书证号=" & "'" & Trim(Combo2.Text) & "'"
Set rs = cmd.Execute
Text7.Text = rs.Fields(1)
Text8.Text = rs.Fields(2)
If rs.Fields(3) = "男" Then
    Me.Option1.Value = True
End If
If rs.Fields(3) = "女" Then
    Me.Option2.Value = True
End If
Text9.Text = rs.Fields(4)
Text10.Text = rs.Fields(5)
End Sub

Private Sub Combo3_Change()
cmd.CommandText = "select * from JY where " & "索书号=" & "'" & Trim(Combo3.Text) & "'"
Set rs = cmd.Execute
If rs.EOF Then
Combo1.Text = ""
Combo2.Text = ""
Else
Combo1.Text = rs.Fields("ISBN")
cmd.CommandText = "select * from JY where " & "索书号=" & "'" & Trim(Combo3.Text) & "'"
Set rs = cmd.Execute
Combo2.Text = rs.Fields("借书证号")
End If
End Sub

Private Sub Combo3_Click()
cmd.CommandText = "select ISBN,借书证号 from JY where 索书号=" & "'" & Trim(Combo3.Text) & "'"
Set rs = cmd.Execute
Combo1.Text = rs.Fields("ISBN")
cmd.CommandText = "select ISBN,借书证号 from JY where 索书号=" & "'" & Trim(Combo3.Text) & "'"
Set rs = cmd.Execute
Combo2.Text = rs.Fields("借书证号")
End Sub

Private Sub Command1_Click()
If Trim(Combo1.Text) = "" Or Trim(Combo2.Text) = "" Or Trim(Text11.Text) = "" Or _
   Len(Trim(Text11.Text)) > 10 Then
      response = MsgBox("请按照要求输入完整的信息", vbOKOnly + vbExclamation, "注意")
Else
      Dim mycmd1 As New ADODB.Command
      Dim myrs1 As New ADODB.Recordset
      Dim mycmd2 As New ADODB.Command
      Dim myrs2 As New ADODB.Recordset
      Set mycmd1.ActiveConnection = cnn
      Set mycmd2.ActiveConnection = cnn
      
      mycmd1.CommandText = "select ISBN from BOOK where ISBN=" & "'" & Trim(Combo1.Text) & "'"
      mycmd2.CommandText = "select 借书证号 from XS where 借书证号=" & "'" & Trim(Combo2.Text) & "'"
      cmd.CommandText = "select 索书号 from JY where 索书号=" & "'" & Trim(Text11.Text) & "'"
      Set rs = cmd.Execute
      Set myrs1 = mycmd1.Execute
      Set myrs2 = mycmd2.Execute
      
      If Val(Text6.Text) = 0 Then
        response = MsgBox("这本书已经被借完", vbOKOnly + vbExclamation, "注意")
      ElseIf Val(Text10.Text) > 5 Then
        response = MsgBox("该同学的借书量是5本，已经不能再借", vbOKOnly + vbExclamation, "注意")
      ElseIf rs.EOF = False Then
        response = MsgBox("该索书号已经存在", vbOKOnly + vbExclamation, "注意")
      ElseIf myrs1.EOF Then
        response = MsgBox("该ISBN不存在", vbOKOnly + vbExclamation, "注意")
      ElseIf myrs2.EOF Then
        response = MsgBox("该借书证号不存在", vbOKOnly + vbExclamation, "注意")
      Else
        cmd.CommandText = "exec jy_insert " & "'" & Combo2.Text & "'" & "," & "'" & _
                         Combo1.Text & "'" & "," & "'" & Trim(Text11.Text) & "'"
        cmd.Execute
        response = MsgBox("借阅成功", vbOKOnly + vbExclamation, "恭喜您")
      End If
End If
End Sub

Private Sub Command2_Click()
If Combo3.Text = "" Then
    response = MsgBox("请给定索书号", vbOKOnly + vbExclamation, "注意")
Else
    cmd.CommandText = "select 索书号 from JY where 索书号=" & "'" & Trim(Combo3.Text) & "'"
    Set rs = cmd.Execute
    If rs.EOF = False Then
    cmd.CommandText = "exec jy_delete " & "'" & Trim(Combo3.Text) & "'"
    cmd.Execute
    response = Combo3.ListIndex
    Combo3.RemoveItem response
    response = MsgBox("还书成功", vbOKOnly + vbExclamation, "恭喜")
    Else
    response = MsgBox("索书号不存在", vbOKOnly + vbExclamation, "注意")
    End If
End If
End Sub

Private Sub Command3_Click()
Unload Me
End Sub

Private Sub Form_Load()
Set cmd.ActiveConnection = cnn

If bor = True Then  '借书
    cmd.CommandText = "select ISBN from BOOK"
    Set rs = cmd.Execute
    rs.MoveFirst
    Do While Not rs.EOF
        Combo1.AddItem rs.Fields(0)
        rs.MoveNext
    Loop
    
    
    cmd.CommandText = "select 借书证号 from XS"
    Set rs = cmd.Execute
    If rs.EOF = True Then
        response = MsgBox("XS表中没有学生", vbOKOnly + vbExclamation, "注意")
    Else
        rs.MoveFirst
        Do While Not rs.EOF
            Combo2.AddItem rs.Fields(0)
            rs.MoveNext
        Loop
    End If
End If

If bor = False Then   '还书
    Me.Frame1.Enabled = False
    Me.Frame2.Enabled = False
    
    cmd.CommandText = "select 索书号 from JY"
    Set rs = cmd.Execute
    If rs.EOF Then
        response = MsgBox("无人借书", vbOKOnly + vbExclamation, "注意")
    Else
        rs.MoveFirst
        Do While Not rs.EOF
            Combo3.AddItem rs.Fields(0)
            rs.MoveNext
        Loop
    End If
End If

Me.Text5.Enabled = False
Me.Text6.Enabled = False
Me.Text10.Enabled = False

End Sub