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_DeptToTree(TreeView1 As TreeView, _
                          ByVal paraKey As String)
  '定义树型变量
  Dim ChildKey As String      '定义子结点的名称
  '定义RecordSet对象
  Dim rs As New ADODB.Recordset
  
  '使用SELECT语句读取所有部门数据
  SQLStmt = "SELECT * FROM DeptInfo"
  Set rs = SQLQuery(SQLStmt)
  While Not rs.EOF
    '读取部门编号
    objDept.DeptID = rs.Fields(0)
    '读取部门名称
    objDept.DeptName = rs.Fields(1)
    '读取部门的描述信息
    If IsNull(rs.Fields(2)) Then
      objDept.Describes = ""
    Else
      objDept.Describes = rs.Fields(2)
    End If
    '生成子节点名称
    ChildKey = TREENAME + Trim(Str(objDept.DeptID))
    '向树型结构添加新的子节点
    TreeView1.Nodes.Add paraKey, tvwChild, ChildKey, objDept.DeptName
    rs.MoveNext
  Wend

  '断开连接
  DBDisconnect
End Sub

'往Combo控件中添加数据
Public Sub Addcbo(ByVal paraCombo As ComboBox, ByVal paraSQLStmt As String, ByVal index As Integer)
  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

'看所修改内容是否在Combo控件内容中
Public Function InCombo(ByVal paraStr As String, _
                         ByVal paraCombo As ComboBox) As Boolean
  i = 0
  Do While i < paraCombo.ListCount
    If paraCombo.List(i) = Trim(paraStr) Then
      '如果在就可以返回True
      InCombo = True
      Exit Function
    End If
    '如果不在就继续找
    i = i + 1
  Loop
  '所有的数据都与想要输入的值不相同，弹出错误警告对话框，并返回False
  MsgBox paraStr + " 不在列表中，请重新设置", vbOKOnly + vbCritical, "错误警告"
  InCombo = False
End Function

'根据税率计算个人所得税
Public Function ComputeTax(ByVal paraWage As Double) As Double
  Select Case paraWage
    Case 0 To 500
      ComputeTax = paraWage * 0.05
    Case 501 To 1999
      ComputeTax = paraWage * 0.1
    Case 2000 To 4999
      ComputeTax = paraWage * 0.15
    Case 5000 To 19999
      ComputeTax = paraWage * 0.2
    Case 20000 To 39999
      ComputeTax = paraWage * 0.25
    Case 40000 To 59999
      ComputeTax = paraWage * 0.3
    Case 60000 To 79999
      ComputeTax = paraWage * 0.35
    Case 80000 To 99999
      ComputeTax = paraWage * 0.4
    Case Is >= 100000
      ComputeTax = paraWage * 0.45
    Case Else
      ComputeTax = 0
  End Select
End Function