module1.bas

MIS系统开发实例（VB+SQL SERVER2000）

Attribute VB_Name = "Module1"
Public change(8) As String
Public user As String
Public sql As String
Public adocon As New ADODB.Connection
Public adors As New ADODB.Recordset
Public adocom As New ADODB.Command
Public strSQLServer As String   'SQL服务器地址
Public strSQLUser As String 'SQL用户名
Public strSQLPW As String 'SQL密码
Public manager As String '当前用户
Public Type curxiaoshou
     pk As String * 10
     curnum As String * 10
     shoushi As String * 20
     zonge As String * 10
     renyuan As String * 10
     fapiaono As String * 10
     ypk As String * 10
     beizhu As String * 100
     sign As Integer
End Type

Public Sub adoopen()
On Error GoTo err
   Dim adostr As String
   adostr = "provider=sqloledb;server=" & strSQLServer & ";user id=" & strSQLUser & ";password=" & strSQLPW & ";Initial Catalog=yiyaoguanli"
   adocon.ConnectionString = adostr
   adocon.Open
   Exit Sub
err:
   MsgBox "错误1: " & err.Number & ": " & err.Description & " in: " & err.Source & vbCrLf & vbCrLf, vbCritical
   Call adoclose
End Sub
Public Sub rs(ByVal sql As String)
   On Error GoTo err
   adors.Open sql, adocon, adOpenDynamic, adLockOptimistic
   Exit Sub
err:
   MsgBox "错误2: " & err.Number & ": " & err.Description & " in: " & err.Source & vbCrLf & vbCrLf, vbCritical
   Call adoclose
End Sub
Public Sub adoclose()
On Error Resume Next
   adors.Close
   Set adors = Nothing
   adocon.Close
   Set adocon = Nothing
End Sub


'连接SQL Server 2000服务器
Public Function sqlConnect(ByVal cnThis As ADODB.Connection, ByVal strServer As String, ByVal strUser As String, ByVal strPass As String)
    Dim strSQL As String
    '生成连接字符串
    strSQL = "provider=sqloledb;server=" & strServer & ";user id=" & strUser & ";password=" & strPass
    cnThis.Open strSQL
End Function

Public Sub comadd(ByVal sqlstr As String, ByVal ziduan As String, ByVal comb As ComboBox)
comb.Clear
Call adoopen
Call rs(sqlstr)
If Not adors.EOF Then
    While Not adors.EOF
          comb.AddItem Trim$(adors.Fields(ziduan))
          adors.MoveNext
    Wend
End If
Call adoclose
End Sub