borrow.cls

VB和Oracle结合的程序

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Borrow"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Bid Int 编号
'2 BookNo  Varchar 50  书号
'3 Cardno  Varchar 50  借阅证编号
'4 Bdate Varchar 50 借出日期
'5 Rdate Varchar 50 应归还日期
'6 RRdate Varchar 50 实际归还日期或者图书挂失日期
'7 Forfeit Number 8,2 罚款金额
'8 Flag Number 借阅标记

Public Bid As Integer
Public BookNo As String
Public Cardno As String
Public Bdate As String
Public Rdate As String
Public RRdate As String
Public Forfeit As Long
Public Flag As Integer

Public Sub Init()
  Bid = 0
  BookNo = ""
  Cardno = ""
  Bdate = ""
  Rdate = ""
  RRdate = ""
  Forfeit = 0
  Flag = 0
End Sub

'删除
Public Sub Delete(ByVal TmpId As Long)
  DB_Connect
  
  SqlStmt = "DELETE FROM Borrow WHERE Bid=" + Trim(TmpId)
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Sub

Public Function GetInfo(ByVal TmpId As Long) As Boolean
  If TmpId <= 0 Then
    GetInfo = False
    Init
    Exit Function
  End If
  
  Bid = TmpId
  
  DB_Connect
  
  SqlStmt = "SELECT * FROM Borrow WHERE Bid=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetInfo = False
    Init
    Exit Function
  Else
    ColVal = String(50, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    BookNo = TrimStr(ColVal)
    
    ColVal = String(50, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Lench(ColVal), pcblench)
    Cardno = TrimStr(ColVal)
    
    ColVal = String(50, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
    Bdate = TrimStr(ColVal)
    
    ColVal = String(50, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Lench(ColVal), pcblench)
    Rdate = TrimStr(ColVal)
    
    ColVal = String(50, 0)
    Rc = SQLGetData(Hstmt, 6, 1, ColVal, Lench(ColVal), pcblench)
    RRdate = TrimStr(ColVal)
    
    ColVal = String(50, 0)
    Rc = SQLGetData(Hstmt, 7, 1, ColVal, Lench(ColVal), pcblench)
    Forfeit = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 8, 1, ColVal, Lench(ColVal), pcblench)
    Flag = Val(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  GetInfo = True
  DB_Disconnect
End Function

Public Function GetNewId() As Long
  Dim TmpId As Long
  '连接数据库
  DB_Connect
  '设置要执行的SQL语句
  SqlStmt = "SELECT Bid FROM Borrow ORDER BY Bid"
  '执行SQL语句
  OdbcExt (SqlStmt)
  '读取SELECT语句返回的查询结果
  i = 1
  Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    TmpId = Val(ColVal)
    If TmpId = i Then
      i = i + 1
    Else
      GetNewId = i
      Rc = SQLFreeStmt(Hstmt, SQL_DROP)
      DB_Disconnect
      Exit Function
    End If
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
  '将得到的新编号作为函数的返回值
  GetNewId = i
End Function

Public Sub Insert()
  Bid = GetNewId
  
  DB_Connect
  
  SqlStmt = "INSERT INTO Borrow Values(" + Trim(Bid) + ",'" + Trim(BookNo) _
     + "','" + Trim(Cardno) + "','" + Trim(Bdate) + "','" _
     + Trim(Rdate) + "','" + Trim(RRdate) + "',0,0)"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

Public Sub Update(ByVal OriId As Long)
  DB_Connect
  
  SqlStmt = "Update Borrow Set Cardno='" + Trim(Cardno) + "', Bdate='" _
          + Trim(Bdate) + "', Rdate='" + Trim(Rdate) + "',RRdate='" + Trim(RRdate) _
          + "' WHERE Bid=" + Trim(Str(OriId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

'图书归还日期更新
Public Sub UpdateReturn(ByVal OriId As Long)
  DB_Connect
  
  SqlStmt = "Update Borrow Set RRdate='" + Str(Date) + "',Flag=2 " _
          + "  WHERE Bid=" + Trim(Str(OriId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

'图书挂失罚款纪录及日期更新
Public Sub UpdateLost(ByVal OriId As Long, ByVal strVal As Long)
  DB_Connect
  
  SqlStmt = "Update Borrow Set RRdate='" + Str(Date) + "', Forfeit=" + Trim(strVal) _
          + ",Flag=3 WHERE Bid=" + Trim(Str(OriId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub


'更改借阅标志0-借阅,1-续借,2-归还,3-挂失
Public Sub UpdateFlag(ByVal OriId As Integer, ByVal FlagNum As Integer)
  DB_Connect
  
  SqlStmt = "Update Borrow Set Flag=" + Trim(FlagNum) + " WHERE Bid=" + Trim(OriId)
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub