frmborrow1.frm

有读者和图书信息的添加查询,借还书等,先建立数据库和ODBC数据源

    Dim msgtext As String
    Dim ssh As String
    Dim jssj As Date
    Dim cnn As ADODB.Connection
    Dim cmddele As New ADODB.Command

 Select Case brflagadd
 Case 1
      If Trim(Combo1.Text & " ") = "" Then
           sMeg = "书籍的ISBN编号"
           MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
      End If
        If Trim(Combo2.Text & " ") = "" Then
           sMeg = "借书证号不能为空"
           MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
        End If
           If Trim(Text1(10).Text & " ") = "" Then
             sMeg = "索书号不能为空"
             MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
             Exit Sub
           Else
             txtSQL = "select * from zzyjy where 索书号='" & Trim(Text1(10).Text) & "'"
             Set mrcc = ExecuteSQL(txtSQL, msgtext)
              If mrcc.EOF = False Then
                 MsgBox "此索书号的书已借出！", vbOKOnly + vbExclamation, "警告"
                 mrcc.Close
                 Exit Sub
              End If
           End If
            isbn = Combo1.Text
            jszh = Combo2.Text
            ssh = Trim(Text1(10).Text)
            jssj = Date
            Set cnn = New ADODB.Connection
            cnn.Open ConnectString
            Set cmddele.ActiveConnection = cnn
            cmddele.CommandText = "jy_insert"
            cmddele.CommandType = adCmdStoredProc
            cmddele.Parameters.Append cmddele.CreateParameter("jszh", adChar, adParamInput, 8, jszh)
            cmddele.Parameters.Append cmddele.CreateParameter("isbn", adChar, adParamInput, 16, isbn)
            cmddele.Parameters.Append cmddele.CreateParameter("ssh", adChar, adParamInput, 10, ssh)
            cmddele.Parameters.Append cmddele.CreateParameter("jssj", adVarChar, adParamInput, 10, [jssj])
            cmddele.Parameters.Append cmddele.CreateParameter("flag", adInteger, adParamOutput)
            cmddele.Execute
            If cmddele("flag") > 0 Then
               MsgBox "借书成功！", vbOKOnly + vbExclamation, "提示"
               isbn = Combo1.Text
               txtSQL = "select *  from zzybook where isbn='" & isbn & "'"
               Set mrcc = ExecuteSQL(txtSQL, msgtext)
                  If Not mrcc.EOF Then
                     Text1(0).Text = mrcc.Fields(1)
                     Text1(1).Text = mrcc.Fields(2)
                     Text1(2).Text = mrcc.Fields(3)
                     Text1(3).Text = CDbl(mrcc.Fields(4))
                     Text1(4).Text = CInt(mrcc.Fields(5))
                     Text1(5).Text = CInt(mrcc.Fields(6))
                   End If
                mrcc.Close
                jszh = Combo2.Text
                txtSQL = "select *  from zzyxs where 借书证号='" & jszh & "'"
             Set mrcc = ExecuteSQL(txtSQL, msgtext)
                  If Not mrcc.EOF Then
                     Text1(6).Text = mrcc.Fields(1)
                     Text1(7).Text = mrcc.Fields(2)
                     Text1(8).Text = mrcc.Fields(4)
                     Text1(9).Text = CStr(mrcc.Fields(5))
                     If mrcc.Fields(3) Then
                        Option2.Value = True
                     Else
                        Option1.Value = True
                     End If
                  End If
             mrcc.Close
             Text1(10).Text = ""
             Text1(10).SetFocus
   
         Else
             MsgBox "无可借的书！", vbOKOnly + vbExclamation, "提示"
         End If
             cnn.Close
 Case 2
       ssh = Trim(Combo3.Text)
       Set cnn = New ADODB.Connection
       cnn.Open ConnectString
       Set cmddele.ActiveConnection = cnn
       cmddele.CommandText = "jy_delete"
       cmddele.CommandType = adCmdStoredProc
       cmddele.Parameters.Append cmddele.CreateParameter("ssh", adChar, adParamInput, 10, ssh)
       cmddele.Parameters.Append cmddele.CreateParameter("flag", adInteger, adParamOutput)
       cmddele.Execute
       If cmddele("flag") > 0 Then
          MsgBox "还书成功！", vbOKOnly + vbExclamation, "提示"
          For iCount = 0 To Combo3.ListCount - 1
            If Combo3.List(iCount) = Combo3.Text And Combo3.Text <> "" Then
               Combo3.RemoveItem iCount
               Combo3.Text = ""
            End If
          Next iCount
          For iCount = 0 To 9
               Text1(iCount).Text = ""
          Next iCount
           Text2.Text = ""
           Text3.Text = ""
        Else
            MsgBox "还书失败！", vbOKOnly + vbExclamation, "提示"
        End If
        cnn.Close
        End Select
End Sub

Private Sub cmdExit_Click()
            Unload Me
End Sub

Private Sub Combo1_Click()
 Dim msgtext As String
 strText = Combo1.Text
 isbn = Combo1.Text
 txtSQL = "select *  from zzybook where isbn='" & isbn & "'"
 Set mrc = ExecuteSQL(txtSQL, msgtext)
     If Not mrc.EOF Then
         Text1(0).Text = mrc.Fields(1)
         Text1(1).Text = mrc.Fields(2)
         Text1(2).Text = mrc.Fields(3)
         Text1(3).Text = CDbl(mrc.Fields(4))
         Text1(4).Text = CInt(mrc.Fields(5))
         Text1(5).Text = CInt(mrc.Fields(6))
      End If
             mrc.Close
Text1(10).Text = ""
Text(10).SetFocus
End Sub

Private Sub Combo2_Click()
        Dim msgtext As String
        jszh = Combo2.Text
        txtSQL = "select *  from zzyxs where 借书证号='" & jszh & "'"
        Set mrc = ExecuteSQL(txtSQL, msgtext)
                  If Not mrc.EOF Then
                     Text1(6).Text = mrc.Fields(1)
                     Text1(7).Text = mrc.Fields(2)
                     Text1(8).Text = mrc.Fields(3)
                     Text1(9).Text = CStr(mrc.Fields(5))
                     If mrc.Fields(3) Then
                     Option2.Value = True
                     Else
                      Option1.Value = True
                     End If
                   End If
             mrc.Close
           Text1(10).SetFocus
End Sub

Private Sub Combo3_Click()
        Dim ssh As String
        Dim msgtext As String
        If brflagadd = 2 Then
           ssh = Trim(Combo3.Text)
           txtSQL = "select zzybook.ISBN,书名,作者,出版社,价格,复本量,库存量,zzyxs.借书证号,姓名,专业名,性别,出生时间,借书量,索书号  from zzyjy,zzybook, zzyxs where zzyjy.借书证号=zzyxs.借书证号 and zzyjy.ISBN=zzybook.ISBN and 索书号='" & ssh & " '"
           Set mrc = ExecuteSQL(txtSQL, msgtext)
           ftag = 0
              Do While Not mrc.EOF
                 If ftag = 0 Then
                    ftag = 1
                     Text2.Text = mrc.Fields(0)
                     Text1(0).Text = mrc.Fields(1)
                     Text1(1).Text = mrc.Fields(2)
                     Text1(2).Text = mrc.Fields(3)
                     Text1(3).Text = CDbl(mrc.Fields(4))
                     Text1(4).Text = CInt(mrc.Fields(5))
                     Text1(5).Text = CInt(mrc.Fields(6))
                     Text3.Text = mrc.Fields(7)
                     Text1(6).Text = mrc.Fields(8)
                     Text1(7).Text = mrc.Fields(9)
                     Text1(8).Text = mrc.Fields(11)
                     Text1(9).Text = CStr(mrc.Fields(12))
                      If mrc.Fields(10) Then
                         Option2.Value = True
                      Else
                         Option1.Value = True
                      End If
                     
                 End If
                      mrc.MoveNext
              Loop
             mrc.Close
     End If
End Sub

Private Sub Form_Activate()
Dim msgtext As String
Dim ftag As Integer
Dim jszh1 As String
Dim isbn1 As String
If jyflag = 0 Then
   jyflag = 1
 Select Case brflagadd
 Case 1
     Combo1.Visible = True
     Combo2.Visible = True
     Text2.Visible = False
     Text3.Visible = False
     Text1(10).Visible = True
     Text1(10).Text = ""
        txtSQL = "select *  from zzybook"
        Set mrc = ExecuteSQL(txtSQL, msgtext)
        ftag = 0
               
                Do While Not mrc.EOF
                    Combo1.AddItem Trim(mrc.Fields(0))
                    If ftag = 0 Then
                      ftag = 1
                      Combo1.Text = mrc.Fields(0)
                      isbn = Combo1.Text
                      Text1(0).Text = mrc.Fields(1)
                      Text1(1).Text = mrc.Fields(2)
                      Text1(2).Text = mrc.Fields(3)
                      Text1(3).Text = CDbl(mrc.Fields(4))
                      Text1(4).Text = CInt(mrc.Fields(5))
                      Text1(5).Text = CInt(mrc.Fields(6))
                    End If
                    mrc.MoveNext
                Loop
        mrc.Close
        txtSQL = "select *  from zzyxs"
        Set mrc = ExecuteSQL(txtSQL, msgtext)
        ftag = 0
                If Not mrc.EOF Then
                   Do While Not mrc.EOF
                    Combo2.AddItem Trim(mrc.Fields(0))
                    If ftag = 0 Then
                       ftag = 1
                       Combo2.Text = mrc.Fields(0)
                       jszh = Combo1.Text
                       Text1(6).Text = mrc.Fields(1)
                       Text1(7).Text = mrc.Fields(2)
                       Text1(8).Text = mrc.Fields(4)
                       Text1(9).Text = CStr(mrc.Fields(5))
                         If mrc.Fields(3) Then
                            Option2.Value = True
                         Else
                             Option1.Value = True
                         End If
                     End If
                     mrc.MoveNext
                   Loop
                End If
             mrc.Close
             Combo3.Visible = False
             Text1(10).SetFocus
Case 2
 Combo1.Visible = False
 Combo2.Visible = False
 Text2.Visible = True
 Text3.Visible = True
 Text1(10).Visible = False
 Combo3.Visible = True
 txtSQL = "select zzybook.ISBN,书名,作者,出版社,价格,复本量,库存量,zzyxs.借书证号,姓名,专业名,性别,出生时间,借书量,索书号  from zzyjy,zzybook,zzyxs where zzyjy.借书证号=zzyxs.借书证号 and zzyjy.ISBN=zzybook.ISBN"
        Set mrc = ExecuteSQL(txtSQL, msgtext)
        ftag = 0
               Do While Not mrc.EOF
                    Combo3.AddItem Trim(mrc.Fields(13))
                    If ftag = 0 Then
                       Combo3.Text = Trim(mrc.Fields(13))
                       ftag = 1
                       Text2.Text = mrc.Fields(0)
                       Text1(0).Text = mrc.Fields(1)
                       Text1(1).Text = mrc.Fields(2)
                       Text1(2).Text = mrc.Fields(3)
                       Text1(3).Text = CDbl(mrc.Fields(4))
                       Text1(4).Text = CInt(mrc.Fields(5))
                       Text1(5).Text = CInt(mrc.Fields(6))
                       Text3.Text = mrc.Fields(7)
                       Text1(6).Text = mrc.Fields(8)
                       Text1(7).Text = mrc.Fields(9)
                       Text1(8).Text = mrc.Fields(11)
                       Text1(9).Text = CStr(mrc.Fields(12))
                         If mrc.Fields(10) Then
                            Option2.Value = True
                         Else
                             Option1.Value = True
                         End If
                   End If
                   mrc.MoveNext
                Loop
                mrc.Close
          End Select
End If
End Sub

Private Sub Form_Load()
 jyflag = 0
 Select Case brflagadd
 Case 1
       Me.Caption = Me.Caption & "借书"
       cmd.Visible = True
       cmd.Enabled = True
       cmd.Caption = "借书(&B)"
      
 Case 2
      Me.Caption = Me.Caption & "还书"
       cmd.Visible = True
       cmd.Enabled = True
       cmd.Caption = "还书(&R)"
 End Select
End Sub