modfunc.bas

sql的实例源码

Attribute VB_Name = "modFunc"
'将单引号“'”替换成双引号“"”
Public Function StrReplace(ByVal paraStr As String) As String
  '将'替换成''
  StrReplace = Trim(Replace(paraStr, "'", "''"))
End Function

'计算字符长度，汉字算两个字符
Public Function CharLen(ByVal paraStr As String) As Long
  CharLen = LenB(StrConv(paraStr, vbFromUnicode))
End Function

'向树型结构增加图书分类信息
Public Sub Add_BTypeToTree(TreeView1 As TreeView, _
                          ByVal paraKey As String)
  '定义树型子节点关键字
  Dim ChildKey As String      '定义子结点的名称
  '定义RecordSet对象
  Dim rs As New ADODB.Recordset
  
  '使用SELECT语句读取第一级图书分类
  SQLStmt = "SELECT * FROM BookType WHERE UID = 0"
  Set rs = SQLQuery(SQLStmt)
  While Not rs.EOF
    '读取图书分类编号
    objBookType.TypeID = rs.Fields(0)
    '读取图书分类名称
    objBookType.TypeName = rs.Fields(1)
    '读取图书上级分类
    objBookType.UID = rs.Fields(2)      '也可直接写为objBookType.UID = 0
    '添加第一级子节点名称
    ChildKey = TREENAME + Trim(Str(objBookType.TypeID))
    '向树型结构添加新的子节点
    TreeView1.Nodes.Add paraKey, tvwChild, ChildKey, objBookType.TypeName
    rs.MoveNext
  Wend

  '定义父节点关键字
  Dim ParentKey As String
  
  '使用SELECT语句读取第二级图书分类
  SQLStmt = "SELECT * FROM BookType WHERE UID > 0"
  Set rs = SQLQuery(SQLStmt)
  While Not rs.EOF
    '读取图书分类编号
    objBookType.TypeID = rs.Fields(0)
    '读取图书分类名称
    objBookType.TypeName = rs.Fields(1)
    '读取图书上级分类
    objBookType.UID = rs.Fields(2)
    '添加第二级子节点名称
    ParentKey = TREENAME + Trim(Str(objBookType.UID))
    ChildKey = TREENAME + Trim(Str(objBookType.TypeID))
    '向树型结构添加新的子节点
    TreeView1.Nodes.Add ParentKey, tvwChild, ChildKey, objBookType.TypeName
    rs.MoveNext
  Wend
  
  '断开连接
  DBDisconnect
End Sub

'向树型结构增加证件类型信息
Public Sub Add_CTypeToTree(TreeView1 As TreeView, _
                          ByVal paraKey As String)
  '定义树型变量
  Dim ChildKey As String      '定义子结点的名称
  '定义RecordSet对象
  Dim rs As New ADODB.Recordset
  
  '使用SELECT语句读取所有证件类型
  SQLStmt = "SELECT * FROM CardType"
  Set rs = SQLQuery(SQLStmt)
  While Not rs.EOF
    '读取证件分类编号
    objCardType.CTypeID = rs.Fields(0)
    '读取证件分类名称
    objCardType.TypeName = rs.Fields(1)
    '读取可借阅书籍的最大数目
    objCardType.MaxCount = rs.Fields(2)
    '读取可借阅书籍的最长时间
    objCardType.MaxDays = rs.Fields(3)
    '读取可续借书籍的最长时间
    objCardType.RenewDays = rs.Fields(4)
    '生成子节点名称
    ChildKey = TREENAME + objCardType.CTypeID
    '向树型结构添加新的子节点
    TreeView1.Nodes.Add paraKey, tvwChild, ChildKey, objCardType.TypeName
    rs.MoveNext
  Wend
  
  '断开连接
  DBDisconnect
End Sub

'往ComboBox控件中添加数据
Public Sub Addcbo(ByVal paraCombo As ComboBox, ByVal paraSQLStmt As String, ByVal index As Integer)
  '定义RecordSet对象
  Dim rs As New ADODB.Recordset
  Set rs = SQLQuery(paraSQLStmt)
  While Not rs.EOF
    '向ComboBox控件中添加数据
    paraCombo.AddItem rs.Fields(index).Value
    rs.MoveNext
  Wend
  
  '断开连接
  DBDisconnect
End Sub

'向ListBox控件中添加数据，用于刷新用户名、用户类型等信息
Public Sub AddList(ByVal paraList As ListBox, ByVal paraSQLStmt As String)
  '定义RecordSet对象
  Dim rs As New ADODB.Recordset
  SQLStmt = "SELECT * FROM PasswdInfo"
  Set rs = SQLQuery(SQLStmt)
  
  paraList.Clear
  While Not rs.EOF
    paraList.AddItem rs.Fields(0) & Space(30 - CharLen(rs.Fields(0))) _
                   & IIf(rs.Fields(2) = 1, "超级用户", "普通用户")
    rs.MoveNext
  Wend
  
  '断开连接
  DBDisconnect
End Sub