bookinfo.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 = "BookInfo"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 BookNo  Varchar 50  书号
'2 BookName  Varchar 50  图书名称
'3 Btype  Number 50  图书分类
'3 Author  Varchar 50  作者
'4 Publisher Varchar 50  出版社
'5 Ptimes  Varchar 50  版次
'6 Bprice  Number 8,2 价格
'7 TotalNum Number 当前库存数量
'8 BStatus Number  图书状态


Public BookNo As String
Public BookName As String
Public Btype As Integer
Public Author As String
Public Publisher As String
Public Ptimes As String
Public Bprice As Single
Public TotalNum As Integer
Public BStatus As Integer

Public Sub Init()
  BookNo = 0
  BookName = ""
  Btype = 0
  Author = 0
  Publisher = ""
  Ptimes = ""
  Bprice = 0
  TotalNum = 0
  BStatus = 0
End Sub

'删除BookInfo数据
Public Sub Delete(ByVal TmpBookNo As String)
  DB_Connect
  
  SqlStmt = "Delete FROM BookInfo WHERE BookNo='" + Trim(TmpBookNo) + "'"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function GetName(ByVal TmpBookNo As String) As String
  If TmpBookNo <= 0 Then
    GetName = ""
    Exit Function
  End If
  
  DB_Connect
  
  SqlStmt = "SELECT BookName FROM BookInfo WHERE BookNo='" + Trim(TmpBookNo) + "'"
  OdbcExt (SqlStmt)

  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetName = ""
    Exit Function
  Else
    ColVal = String(100, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    GetName = TrimStr(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Function

Public Function GetNo(ByVal TmpName As String) As String
  If TmpName = "" Then
    GetId = 0
    Exit Function
  End If
  DB_Connect
  
  SqlStmt = "SELECT BookNo FROM BookInfo WHERE BookName='" + Trim(TmpName) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    GetNo = TrimStr(ColVal)
  Else
    GetNo = ""
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function
'得到当前库存数量
Public Function GetTotalNum(ByVal TmpBookNo As String) As String
  DB_Connect
  
  SqlStmt = "SELECT TotalNum FROM BookInfo WHERE BookNo='" + Trim(TmpBookNo) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    GetTotalNum = Val(ColVal)
  Else
    GetTotalNum = 0
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function


Public Function GetInfo(ByVal TmpBookNo As String) As Boolean
  If TmpBookNo = "" Then
    Init
    GetInfo = False
    Exit Function
  End If
  
  BookNo = TmpBookNo
  
  DB_Connect
  SqlStmt = "SELECT * FROM BookInfo WHERE BookNo='" + Trim(TmpBookNo) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetInfo = False
    Exit Function
  Else
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    BookName = TrimStr(ColVal)

    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Lench(ColVal), pcblench)
    Author = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
    Publisher = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Lench(ColVal), pcblench)
    Ptimes = TrimStr(ColVal)

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 6, 1, ColVal, Lench(ColVal), pcblench)
    Bprice = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 7, 1, ColVal, Lench(ColVal), pcblench)
    Btype = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 8, 1, ColVal, Lench(ColVal), pcblench)
    TotalNum = Val(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  GetInfo = True
  DB_Disconnect
End Function

Public Function In_DB(ByVal TmpBookName As String) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT BookNo FROM BookInfo WHERE BookName='" + Trim(TmpBookName) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    In_DB = True
  Else
    In_DB = False
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Function

Public Sub Insert()
  '连接数据库
  DB_Connect
  '设置INSERT语句
  SqlStmt = "INSERT INTO BookInfo (BookNo, BookName, Publisher," _
     + "Author, Ptimes, Bprice,Btype,TotalNum) Values('" + Trim(BookNo) + "','" _
     + Trim(BookName) + "','" + Trim(Publisher) + "','" + Trim(Author) _
     + "','" + Trim(Ptimes) + "'," + Trim(Bprice) + "," + Trim(Btype) + ",0)"
  '执行SQL语句
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Sub

Public Sub Update(ByVal OriBookNo As String)
  '连接数据库
  DB_Connect
  '设置UPDATE语句
  SqlStmt = "Update BookInfo Set BookNo='" + Trim(BookNo) _
          + "', BookName='" + Trim(BookName) + "',Publisher='" _
          + Trim(Publisher) + "',Author='" + Trim(Author) _
          + "',Ptimes='" + Trim(Ptimes) + "',Bprice=" + Trim(Bprice) + ",Btype=" + Trim(Btype) _
          + " WHERE BookNo='" + Trim(OriBookNo) + "'"
  '执行SQL语句
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Sub
'图书入库时更改图书基本库存数量
Public Sub UpdateStoreNum(ByVal OriBookNo As String, ByVal addNum As Integer)
  '连接数据库
  DB_Connect
  
  '设置UPDATE语句
  SqlStmt = "Update BookInfo Set TotalNum=TotalNum+" + Trim(addNum) _
          + " WHERE BookNo='" + Trim(OriBookNo) + "'"
  '执行SQL语句
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Sub

'图书盘点时更改当前图书库存数量
Public Sub UpdateTotalNum(ByVal OriBookNo As String, ByVal CountNum As Integer)
  '连接数据库
  DB_Connect
  
  '设置UPDATE语句
  SqlStmt = "Update BookInfo Set TotalNum=" + Trim(CountNum) _
          + " WHERE BookNo='" + Trim(OriBookNo) + "'"
  '执行SQL语句
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Sub