frmbook1.frm

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

VERSION 5.00
Begin VB.Form frmBook1 
   Caption         =   "书籍信息"
   ClientHeight    =   3855
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   8205
   LinkTopic       =   "Form1"
   ScaleHeight     =   3855
   ScaleWidth      =   8205
   StartUpPosition =   3  '窗口缺省
   Begin VB.CommandButton cmdExit 
      Caption         =   "返回(&X)"
      Height          =   375
      Left            =   4200
      TabIndex        =   10
      Top             =   3000
      Width           =   975
   End
   Begin VB.CommandButton cmd 
      Caption         =   "保存(&S)"
      Height          =   375
      Left            =   2880
      TabIndex        =   9
      Top             =   3000
      Width           =   975
   End
   Begin VB.Frame Frame1 
      Caption         =   "书籍信息"
      Height          =   2055
      Left            =   360
      TabIndex        =   0
      Top             =   600
      Width           =   7455
      Begin VB.TextBox Text1 
         Height          =   270
         Index           =   5
         Left            =   960
         TabIndex        =   16
         Top             =   1440
         Width           =   2175
      End
      Begin VB.TextBox Text1 
         Height          =   270
         Index           =   4
         Left            =   4080
         TabIndex        =   15
         Top             =   1080
         Width           =   2535
      End
      Begin VB.TextBox Text1 
         Height          =   270
         Index           =   3
         Left            =   960
         TabIndex        =   14
         Top             =   1080
         Width           =   2175
      End
      Begin VB.TextBox Text1 
         Height          =   270
         Index           =   2
         Left            =   4080
         TabIndex        =   13
         Top             =   720
         Width           =   2535
      End
      Begin VB.TextBox Text1 
         Height          =   270
         Index           =   1
         Left            =   960
         TabIndex        =   12
         Top             =   720
         Width           =   2175
      End
      Begin VB.TextBox Text1 
         Height          =   270
         Index           =   0
         Left            =   4080
         TabIndex        =   11
         Top             =   360
         Width           =   2535
      End
      Begin VB.ComboBox Combo1 
         Height          =   300
         Left            =   960
         TabIndex        =   2
         Top             =   360
         Width           =   2175
      End
      Begin VB.Label Label7 
         Caption         =   "库存数"
         Height          =   255
         Left            =   120
         TabIndex        =   8
         Top             =   1440
         Width           =   735
      End
      Begin VB.Label Label6 
         Caption         =   "复本数"
         Height          =   255
         Left            =   3360
         TabIndex        =   7
         Top             =   1080
         Width           =   735
      End
      Begin VB.Label Label5 
         Caption         =   "   价 格"
         Height          =   255
         Left            =   120
         TabIndex        =   6
         Top             =   1080
         Width           =   735
      End
      Begin VB.Label Label4 
         Caption         =   "出版社"
         Height          =   255
         Left            =   3360
         TabIndex        =   5
         Top             =   720
         Width           =   735
      End
      Begin VB.Label Label3 
         Caption         =   "   作 者"
         Height          =   255
         Left            =   120
         TabIndex        =   4
         Top             =   720
         Width           =   735
      End
      Begin VB.Label Label2 
         Caption         =   "书  名"
         Height          =   255
         Left            =   3360
         TabIndex        =   3
         Top             =   360
         Width           =   735
      End
      Begin VB.Label Label1 
         Caption         =   "  ISBN"
         Height          =   255
         Left            =   240
         TabIndex        =   1
         Top             =   480
         Width           =   735
      End
   End
End
Attribute VB_Name = "frmBook1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public isbn As String


Private Sub cmd_Click()
    Dim iCount As Integer
    Dim sMeg As String
    Dim mrcc As ADODB.Recordset
    Dim msgtext As String
    Dim shum, zuozh, cbsh As String
    Dim isbn1 As String
    Dim jiag As Double
    Dim fbl, kcl As Integer
    Dim cnn As ADODB.Connection
    Dim cmddele As New ADODB.Command
    
 Select Case bflagadd
 Case 1
   If Trim(Combo1.Text & " ") = "" Then
           sMeg = "书籍的ISBN编号"
   End If
    For iCount = 0 To 5
        If Trim(Text1(iCount) & " ") = "" Then
         Select Case iCount
               Case 0
                    sMeg = "书名"
               Case 1
                    sMeg = "作者"
               Case 2
                    sMeg = "出版社"
               Case 3
                    sMeg = "价格"
               Case 4
                    sMeg = "复本数"
               Case 5
                    sMeg = "库存数"
          End Select
          sMeg = sMeg & "不能为空！"
           MsgBox sMeg, vbOKOnly + vbExclamation, "警告"
          Text1(iCount).SetFocus
          Exit Sub
       End If
   Next iCount
   isbn = Combo1.Text
   Combo1.AddItem Trim(isbn)
   shum = Text1(0).Text
   zuozh = Text1(1).Text
   cbsh = Text1(2).Text
   jiag = CDbl(Text1(3).Text)
   fbl = CInt(Text1(4).Text)
   kcl = CInt(Text1(5).Text)
   txtSQL = "select * from zzybook  where isbn='" & Trim(Combo1.Text) & "'"
    Set mrc = ExecuteSQL(txtSQL, msgtext)
    If mrc.EOF = False Then
      MsgBox "此书的ISBN编号已存在！", vbOKOnly + vbExclamation, "警告"
      Combo1.SetFocus
         mrc.Close
         Else
         Set cnn = New ADODB.Connection
   cnn.Open ConnectString
   cnn.Execute "exec book_insert '" & isbn & "','" & shum & "','" & zuozh & "','" & cbsh & "'," & jiag & "," & fbl & ", " & kcl
   cnn.Close
   MsgBox "数据已保存！", vbOKOnly + vbExclamation, "提示"
     Combo1.Text = ""
       For iCount = 0 To 5
        Text1(iCount).Text = ""
        Next iCount
  End If
 Case 2
  isbn1 = Combo1.Text
  If Trim(isbn) <> Trim(isbn1) Then
  MsgBox "书籍的ISBN编号不能修改", vbOKOnly + vbExclamation, "警告"
     Exit Sub
  End If
      shum = Text1(0).Text
      zuozh = Text1(1).Text
      cbsh = Text1(2).Text
      jiag = CDbl(Text1(3).Text)
       fbl = CInt(Text1(4).Text)
       kcl = CInt(Text1(5).Text)
       Set cnn = New ADODB.Connection
   cnn.Open ConnectString
   
   cnn.Execute "exec book_update '" & isbn & "','" & shum & "','" & zuozh & "','" & cbsh & "'," & jiag & ", " & fbl & ", " & kcl
   cnn.Close
   MsgBox "数据已更新！", vbOKOnly + vbExclamation, "提示"
       
Case 3
    Set cnn = New ADODB.Connection
   cnn.Open ConnectString
   Set cmddele.ActiveConnection = cnn
   cmddele.CommandText = "book_delete"
   cmddele.CommandType = adCmdStoredProc
   cmddele.Parameters.Append cmddele.CreateParameter("isbn", adChar, adParamInput, 16, isbn)
   cmddele.Parameters.Append cmddele.CreateParameter("flag", adInteger, adParamOutput)
   cmddele.Execute
   If cmddele("flag") = 0 Then
      MsgBox "记录已被删除！", vbOKOnly + vbExclamation, "提示"
      Combo1.Text = ""
      For iCount = 0 To Combo1.ListCount - 1
       If Combo1.List(iCount) = Combo1.Text And Combo1.Text <> "" Then
          Combo1.RemoveItem iCount
          Combo1.Text = ""
       End If
     Next iCount
       For iCount = 0 To 5
           Text1(iCount).Text = ""
                Next iCount
    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 strText, msgtext As String
            If (bflagadd = 2 Or bflagadd = 3 Or bflagadd = 4) Then
               strText = Combo1.Text
               isbn = Combo1.Text
               txtSQL = "select *  from zzybook  where isbn='" & strText & "'"
                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
              End If
              Combo1.SetFocus
End Sub

Private Sub Combo1_KeyPress(KeyAscii As Integer)
            Dim msgtext As String
            If KeyAscii = 13 And bflagadd = 1 Then
                 txtSQL = "select * from zzybook  where isbn='" & Trim(Combo1.Text) & "'"
                 Set mrc = ExecuteSQL(txtSQL, msgtext)
                 If mrc.EOF = False Then
                    MsgBox "此ISBN编号已存在！", vbOKOnly + vbExclamation, "警告"
                    Combo1.SetFocus
                     mrc.Close
                 Else
                    Text1(0).SetFocus
                 End If
            End If
End Sub

Private Sub Form_Activate()
        Dim msgtext As String
        Dim ftag As Integer

        txtSQL = "select *  from zzybook"
        Set mrc = ExecuteSQL(txtSQL, msgtext)
        ftag = 0
                   Do While Not mrc.EOF
                    Combo1.AddItem Trim(mrc.Fields(0))
                    If (bflagadd = 2 Or bflagadd = 3 Or bflagadd = 4) And 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
                Combo1.SetFocus
End Sub

Private Sub Form_Load()
 Select Case bflagadd
 Case 1
       Me.Caption = Me.Caption & "添加"
       cmd.Visible = True
       cmd.Enabled = True
       cmd.Caption = "保存 (&S)"
       
 Case 2
        Me.Caption = Me.Caption & "修改"
        cmd.Visible = True
        cmd.Enabled = True
        cmd.Caption = "更新 (&U)"
      
 Case 3
       Me.Caption = Me.Caption & "删除"
       cmd.Visible = True
       cmd.Enabled = True
       cmd.Caption = "删除 (&D)"
         
 Case 4
       Me.Caption = Me.Caption & "查询"
       cmd.Visible = False
       cmd.Enabled = False
 End Select
End Sub

Private Sub Text1_KeyDown(Index As Integer, KeyCode As Integer, Shift As Integer)
            Select Case Index
                      Case 0
                            If KeyCode = 13 Then
                               Text1(1).SetFocus
                            End If
                      Case 1
                            If KeyCode = 13 Then
                               Text1(2).SetFocus
                            End If
                      Case 2
                             If KeyCode = 13 Then
                                Text1(3).SetFocus
                             End If
                     Case 3
                             If KeyCode = 13 Then
                                 Text1(4).SetFocus
                             End If
                      Case 4
                             If KeyCode = 13 Then
                                 Text1(5).SetFocus
                             End If
           End Select
End Sub