main_jygl_js.frm

是基于C#语言的一个图书综合管理系统的开发案例,相当实用

         Top             =   165
         Width           =   1215
      End
      Begin VB.CommandButton CmdCancel 
         BackColor       =   &H00FF8080&
         Caption         =   "取消"
         Enabled         =   0   'False
         Height          =   390
         Left            =   2670
         Picture         =   "main_jygl_js.frx":309B
         TabIndex        =   10
         Top             =   165
         Width           =   1215
      End
      Begin VB.CommandButton CmdEnd 
         BackColor       =   &H00FF8080&
         Caption         =   "退出"
         Height          =   390
         Left            =   3885
         Picture         =   "main_jygl_js.frx":4279
         TabIndex        =   9
         Top             =   165
         Width           =   1215
      End
   End
   Begin VB.Label Label2 
      BackStyle       =   0  'Transparent
      Caption         =   "图 书 借 阅 登 记"
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   14.25
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H000000FF&
      Height          =   285
      Index           =   0
      Left            =   4035
      TabIndex        =   18
      Top             =   60
      Width           =   2655
   End
End
Attribute VB_Name = "main_jygl_js"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim s, Y, i     '声明变量
'声明数据集对象
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
Dim lsph As Integer     '声明一个整型变量
Private Sub Form_Load()
'自动识别路径
Adodc1.ConnectionString = "DSN=NBooks"
Adodc1.RecordSource = "select * from kcb"
Adodc1.Refresh
Adodc2.ConnectionString = "DSN=NBooks"
Adodc2.RecordSource = "select * from jsb"
Adodc2.Refresh
Adodc3.ConnectionString = "DSN=NBooks"
Adodc3.RecordSource = "select * from reads"
Adodc3.Refresh
Adodc4.ConnectionString = "DSN=NBooks"
Adodc4.RecordSource = "select * from reads where 到期日期> '" + Str(Date) + "' "
Adodc4.Refresh
 MS1.Rows = 102
 MS1.Cols = 10
 '定义MS1表格的列宽和表头信息
 s = Array("300", "990", "3000", "800", "1200", "800", "600", "1000", "1200", "800")
 Y = Array("xh", "条形码", "书名", "作者", "出版社", "单价", "数量", "金额", "图书类别", "备注")
 For i = 0 To 9
     MS1.ColWidth(i) = s(i)
     MS1.TextMatrix(0, i) = Y(i)
 Next i
 '定义MS1表格的行号
 For i = 1 To 101
     MS1.TextMatrix(i, 0) = i
 Next i
 rq.Caption = Date     '设置借书日期
 DataCombo1.Enabled = False
 Tjsr.Enabled = False
 MS1.Enabled = False
End Sub
Private Sub Form_Unload(Cancel As Integer)
 frm_main.Enabled = True
End Sub
Private Sub DataCombo1_Change()
  Adodc3.RecordSource = "select * from reads where 书证号='" + DataCombo1.text + "'"
  Adodc3.Refresh
  If Adodc3.Recordset.RecordCount > 0 Then   '如果记录数大于零
     jsr.Caption = Adodc3.Recordset.Fields("姓名")
  End If
End Sub
Private Sub Datacombo1_KeyDown(KeyCode As Integer, Shift As Integer)
 If KeyCode = vbKeyReturn Then Tjsr.SetFocus  '按回车键Tjsr获得焦点
End Sub
Private Sub Tjsr_KeyDown(KeyCode As Integer, Shift As Integer)
 If KeyCode = vbKeyReturn Then     '按回车键
    text1.Visible = True
    '到达第1行，第1列
    MS1.Row = 1
    MS1.Col = 1
    text1.SetFocus
 End If
End Sub
Private Sub MS1_Click()
 If MS1.Row >= 1 And MS1.TextMatrix(MS1.Row - 1, 2) <> "" Then     '在MS1表格第1行或大于第1行时
    text1.Visible = True     'text1可见
    text1.SetFocus
 End If
End Sub

Private Sub MS1_entercell()
 Dim X, Y, p As String  '定义字符变量
 If MS1.CellWidth <= 0 Or MS1.CellHeight <= 0 Then Exit Sub
 X = MS1.TextMatrix(MS1.FixedRows, MS1.Col)
 Y = MS1.TextMatrix(MS1.Row, 0)
 If Y <> "" Then
    If MS1.Col - MS1.LeftCol <= 3 Then MS1.LeftCol = MS1.LeftCol + 1
    If MS1.CellWidth > 0 And MS1.CellHeight > 0 Then
       text1.Width = MS1.CellWidth:  text1.Height = MS1.CellHeight
       text1.Left = MS1.CellLeft + MS1.Left
       text1.Top = MS1.CellTop + MS1.Top
    End If
    X = MS1.TextMatrix(MS1.FixedRows, MS1.Col)
    Y = MS1.TextMatrix(MS1.Row, 0)
    p = MS1.TextMatrix(MS1.Row, MS1.Col)
    text1.text = MS1.text
    text1.SelStart = 0
    text1.SelLength = Len(text1.text)
    'Len 函数返回 Long，其中包含字符串内字符的数目，或是存储一变量所需的字节数。
 End If
End Sub
Private Sub MS1_RowColChange()     '格式化金额
  For i = 1 To 100
      If MS1.TextMatrix(i, 1) <> "" Then
         MS1.TextMatrix(i, 5) = Format(MS1.TextMatrix(i, 5), "#0.00")
         'Format 函数返回 Variant (String)，其中含有一个表达式，它是根据格式表达式中的指令来格式化的。
         MS1.TextMatrix(MS1.Row, 7) = Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5))
         MS1.TextMatrix(i, 7) = Format(MS1.TextMatrix(i, 7), "#0.00")
      End If
  Next i
End Sub

Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = vbKeyReturn Then   '按回车键
   Adodc1.RecordSource = "select * from kcb where 条形码='" + MS1.TextMatrix(MS1.Row, 1) + "'"
   Adodc1.Refresh
   If MS1.Col = 1 Then
    With Adodc1.Recordset
    If .RecordCount > 0 Then     '当记录大于零时
       '赋值给MS1表格
       If .Fields("条形码") <> "" Then MS1.TextMatrix(MS1.Row, 1) = .Fields("条形码")
       If .Fields("书名") <> "" Then MS1.TextMatrix(MS1.Row, 2) = .Fields("书名")
       If .Fields("作者") <> "" Then MS1.TextMatrix(MS1.Row, 3) = .Fields("作者")
       If .Fields("出版社") <> "" Then MS1.TextMatrix(MS1.Row, 4) = .Fields("出版社")
       If .Fields("单价") <> "" Then MS1.TextMatrix(MS1.Row, 5) = .Fields("单价")
       If .Fields("图书类别") <> "" Then MS1.TextMatrix(MS1.Row, 8) = .Fields("图书类别")
       MS1.TextMatrix(MS1.Row, 6) = "1"
       MS1.Col = 9
       text1.SetFocus
    End If
    End With
   End If
   If MS1.Col = 10 Then
      MS1.Row = MS1.Row + 1
      MS1.Col = 1
    Else
      If MS1.Col + 1 <= MS1.Cols - 1 Then
         MS1.Col = MS1.Col + 1
       Else
         If MS1.Row + 1 <= MS1.Rows - 1 Then
            MS1.Row = MS1.Row + 1
            MS1.Col = 1
         End If
      End If
   End If
  End If
  If KeyCode = vbKeyUp Then   '按KeyUp键
     If MS1.Row > 1 Then MS1.Row = MS1.Row - 1
  End If
  If KeyCode = vbKeyDown And (MS1.TextMatrix(MS1.Row, 2)) <> "" Then      '按KeyDown键
     If MS1.Row < 99 Then MS1.Row = MS1.Row + 1
  End If
  If KeyCode = vbKeyLeft Then    '按KeyLeft键
   If text1.text <> "" Then
      text1.SelStart = 0
      text1.SelLength = Len(text1.text)
      'Len 函数返回 Long，其中包含字符串内字符的数目，或是存储一变量所需的字节数。
   End If
   If MS1.Col - 8 <= MS1.Cols + 1 Then
      MS1.Col = MS1.Col - 1
      If MS1.Col = 0 Then MS1.Col = 1
     Else
      If MS1.Row + 1 <= MS1.Row - 1 Then
         MS1.Row = MS1.Row + 1
         MS1.Col = 1
      End If
   End If
  End If
  If KeyCode = vbKeyRight Then        '按KeyRight键
   If text1.text <> "" Then
      text1.SelStart = 0
      text1.SelLength = Len(text1.text)
   End If
   If MS1.Col + 1 <= MS1.Cols - 1 Then
      MS1.Col = MS1.Col + 1
     Else
      If MS1.Row + 1 <= MS1.Rows - 1 Then
         MS1.Row = MS1.Row + 1
         MS1.Col = 1
      End If
   End If
  End If
End Sub

Private Sub Text1_Change()
 MS1.text = text1.text     '赋值给MS1.text
 If MS1.Col = 5 Then MS1.TextMatrix(MS1.Row, 7) = Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5))
 If MS1.Col = 6 Then MS1.TextMatrix(MS1.Row, 7) = Val(MS1.TextMatrix(MS1.Row, 6)) * Val(MS1.TextMatrix(MS1.Row, 5))
 Dim a, B As Single
 For i = 1 To 101
     a = Val(MS1.TextMatrix(i, 7)) + a
     B = Val(MS1.TextMatrix(i, 6)) + B
     If MS1.TextMatrix(i, 1) <> "" Then pz.Caption = i
 Next i
 hjje.Caption = a
 hjsl.Caption = B     '计算合计金额，合计数量
End Sub

Private Sub CmdReg_Click()  '登记
 '查询所有借书数据,并按票号排序
 Adodc2.RecordSource = "select * from jsb order by 票号"
 Adodc2.Refresh
 '创建借书票号
 If Adodc2.Recordset.RecordCount > 0 Then
    If Not Adodc2.Recordset.EOF Then Adodc2.Recordset.MoveLast
    If Adodc2.Recordset.Fields("票号") <> "" Then
       lsph = Right(Trim(Adodc2.Recordset.Fields("票号")), 4) + 1
       'Trim 函数返回 Variant (String)，其中包含指定字符串的拷贝，没有前导和尾随空白 (Trim)。
       PH.Caption = Date & "js" & Format(lsph, "0000")
    End If
  Else
    PH.Caption = Date & "js" & "0001"
 End If
 '设置控件有效或无效
 text1.Enabled = True
 '确定文本框在MS1表格中的位置
 text1.Width = MS1.CellWidth
 text1.Height = MS1.CellHeight
 text1.Left = MS1.CellLeft + MS1.Left
 text1.Top = MS1.CellTop + MS1.Top
 '设置控件状态
 MS1.Enabled = True
 CmdSave.Enabled = True
 CmdCancel.Enabled = True
 CmdReg.Enabled = False
 MS1.Enabled = True
 DataCombo1.Enabled = True
 Tjsr.Enabled = True
End Sub

Private Sub CmdSave_Click()  '保存
Adodc4.RecordSource = "select * from reads where 到期日期<'" & Str(Date) & "' and 书证号='" & DataCombo1.text & "'"
Adodc4.Refresh
If Adodc4.Recordset.RecordCount <> 0 Then MsgBox "书证已到期，不能借书": Unload Me
 For i = 1 To 101
     With MS1
     If .TextMatrix(i, 1) <> "" Then
        Set rs2 = New ADODB.Recordset
        rs2.Open "select * from kcb where 条形码='" + .TextMatrix(i, 1) + "'", Cnn, adOpenKeyset, adLockOptimistic
        If rs2.RecordCount > 0 Then    '如果记录数大于零
            If .TextMatrix(i, 1) <> "" And .TextMatrix(i, 2) <> "" And .TextMatrix(i, 3) <> "" And .TextMatrix(i, 4) <> "" _
            And .TextMatrix(i, 5) <> "" And .TextMatrix(i, 6) <> "" And .TextMatrix(i, 7) <> "" And .TextMatrix(i, 8) <> "" _
            And DataCombo1.text <> "" And jsr.Caption <> "" And Tjsr.text <> "" And _
            rq.Caption <> "" And PH.Caption <> "" Then
            If Val(rs2.Fields("现存数量")) - Val(.TextMatrix(i, 6)) < 0 Then
                rs2.Close
                 '清空数据
                For Z = 1 To 101
                    For X = 1 To 9
                        MS1.TextMatrix(Z, X) = ""
                    Next X
                Next Z
                Tjsr.text = ""
                pz.Caption = ""
                hjsl.Caption = ""
                hjje.Caption = ""
                text1.Visible = False   '设置控件不可见
                '设置控件状态
                MS1.Enabled = False
                CmdSave.Enabled = False
                CmdReg.Enabled = True
                CmdCancel.Enabled = False
                MsgBox "库存不足,不能进行借阅！", , "图书综合管理系统"
                Exit Sub
            End If
                Cnn.Execute ("insert into jsb(条形码,书名,作者,出版社,单价,借出数量,金额,图书类别,备注,书证号,借书人," & _
                "借书经手人,借书日期,票号,操作员,是否归还) values('" & .TextMatrix(i, 1) & "','" & .TextMatrix(i, 2) & _
                "','" & .TextMatrix(i, 3) & "','" & .TextMatrix(i, 4) & "','" & .TextMatrix(i, 5) & "','" & _
                .TextMatrix(i, 6) & "','" & .TextMatrix(i, 7) & "','" & .TextMatrix(i, 8) & "','" & .TextMatrix(i, 9) & _
                " ','" & Trim(DataCombo1.text) & "','" & jsr.Caption & "','" & Tjsr.text & "','" & rq.Caption & _
                "','" & PH.Caption & "','" & frm_main.St1.Panels(3).text & "','否') ")
           '更新"kcb"表中的"库存"及"库存金额"
                Cnn.Execute "update kcb set 现存数量='" & Val(rs2.Fields("现存数量")) - Val(.TextMatrix(i, 6)) & "'," & _
                "借出次数='" & Val(rs2.Fields("借出次数")) + 1 & "'where 条形码='" + .TextMatrix(i, 1) + "'"
            Else
                MsgBox "库存无此图书,不能进行借阅！", , "图书综合管理系统"
                .Clear
                Exit Sub
            End If
            rs2.Close
            Dim s As Integer
            s = Val(.TextMatrix(i, 6)) + s
            Set rs3 = New ADODB.Recordset
            rs3.Open "select * from reads where 书证号='" + DataCombo1.text + "'", Cnn, adOpenKeyset, adLockOptimistic
            If rs3.RecordCount > 0 Then
                '更新"kcb"表中的"库存"及"库存金额"
                Cnn.Execute "update reads set 已借书数='" & Val(rs3.Fields("已借书数")) + s & "' where 书证号='" + DataCombo1.text + "'"
'                rs3.Fields("已借书数") = Val(rs3.Fields("已借书数")) + s
'                rs3.Update
                rs3.Close
            Else
                MsgBox "此读者不存在！", , "图书综合管理系统"
                Exit Sub
            End If
        End If
    End If
    End With

 Next i
 '清空数据
 For i = 1 To 101
     For j = 1 To 9
         MS1.TextMatrix(i, j) = ""
     Next j
 Next i
 Tjsr.text = ""
 pz.Caption = ""
 hjsl.Caption = ""
 hjje.Caption = ""
 text1.Visible = False   '设置控件不可见
 '设置控件状态
 MS1.Enabled = False
 CmdSave.Enabled = False
 CmdReg.Enabled = True
 CmdCancel.Enabled = False
End Sub
Private Sub CmdCancel_Click()      '取消操作
 '清空控件
 Tjsr.text = ""
 pz.Caption = ""
 hjsl.Caption = ""
 hjje.Caption = ""
 For i = 1 To 100
 For j = 1 To 9
   MS1.TextMatrix(i, j) = ""
 Next j
 Next i
 '设置控件状态
 text1.Enabled = False
 MS1.Enabled = False
 CmdSave.Enabled = False
 CmdCancel.Enabled = False
 CmdReg.Enabled = True
 CmdReg.SetFocus
End Sub
Private Sub CmdEnd_Click()  '退出
 frm_main.Enabled = True
 Unload Me
End Sub