formwiz.frm

关于VB模块的

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "msflxgrd.ocx"
Begin VB.Form Form1 
   Caption         =   "一个使用ADO的数据库应用程序"
   ClientHeight    =   4848
   ClientLeft      =   60
   ClientTop       =   348
   ClientWidth     =   4560
   LinkTopic       =   "Form1"
   ScaleHeight     =   4848
   ScaleWidth      =   4560
   StartUpPosition =   2  'CenterScreen
   Begin VB.Frame Frame1 
      Height          =   4095
      Left            =   240
      TabIndex        =   4
      Top             =   120
      Width           =   4095
      Begin MSFlexGridLib.MSFlexGrid Grid 
         Height          =   1935
         Left            =   120
         TabIndex        =   11
         Top             =   240
         Width           =   3855
         _ExtentX        =   6795
         _ExtentY        =   3408
         _Version        =   393216
         FixedCols       =   0
         AllowUserResizing=   1
      End
      Begin VB.TextBox Text_Top 
         Height          =   285
         Left            =   960
         TabIndex        =   0
         Top             =   600
         Width           =   2415
      End
      Begin VB.TextBox Text_Bot 
         Height          =   285
         IMEMode         =   3  'DISABLE
         Left            =   960
         PasswordChar    =   "*"
         TabIndex        =   2
         Top             =   1800
         Width           =   2415
      End
      Begin VB.TextBox Text_Mid 
         Height          =   285
         Left            =   960
         TabIndex        =   1
         Top             =   1200
         Width           =   2415
      End
      Begin VB.Label Label_Top 
         Caption         =   "Server"
         Height          =   255
         Left            =   960
         TabIndex        =   10
         Top             =   360
         Width           =   2415
      End
      Begin VB.Label Label_Bot 
         Caption         =   "Password"
         Height          =   255
         Left            =   960
         TabIndex        =   9
         Top             =   1560
         Width           =   2415
      End
      Begin VB.Label Label_Mid 
         Caption         =   "Login ID"
         Height          =   255
         Left            =   960
         TabIndex        =   8
         Top             =   960
         Width           =   2415
      End
      Begin VB.Label Label2 
         BackColor       =   &H8000000E&
         BorderStyle     =   1  'Fixed Single
         Height          =   1695
         Left            =   120
         TabIndex        =   7
         Top             =   2280
         Width           =   3855
      End
      Begin VB.Label Label1 
         BackColor       =   &H8000000E&
         BorderStyle     =   1  'Fixed Single
         Height          =   1935
         Left            =   120
         TabIndex        =   6
         Top             =   240
         Width           =   3855
      End
   End
   Begin VB.CommandButton Command_Cancel 
      Caption         =   "Cancel"
      Height          =   375
      Left            =   2400
      TabIndex        =   5
      Top             =   4320
      Width           =   1215
   End
   Begin VB.CommandButton Command_Next 
      Caption         =   "Next-->"
      Height          =   375
      Left            =   720
      TabIndex        =   3
      Top             =   4320
      Width           =   1335
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim nState As Integer
Dim sServer As String
Dim sLoginID As String
Dim sPassword As String

' Displayed by Form_load
Const sCase0_UpperText = "一个使用ADO的数据库演示程序."
Const sCase0_LowerText = "下一步输入连接数据库信息."

Const sCase1_LowerText = "输入用户名称和密码." & _
    "下一步选择连接的DSN."

Const sCase2_UpperText = "使用DSN连接数据库成功."
Const sCase2_LowerText = "下一步将使用非DSN连接数据库."

Const sCase3_UpperText = "使用非DSN连接数据库成功."
Const sCase3_LowerText = "下一步使用本地SQLOLEDB provider连接数据库."

Const sCase4_UpperText = "使用本地SQLOLEBD provider连接数据库成功."
Const sCase4_LowerText = "下一步执行一个简单查询并创建一个向前的结果集."

Const sCase5_LowerText = "创建向前结果集成功." & vbCrLf & vbCrLf _
     & " 下一步用一个简单KEYSET类型RECORDSET对象查询."

Const sCase6_LowerText = "创建KEYSET类型RECORDSET成功." & vbCrLf & vbCrLf _
     & " 下一步将创建一个相反顺序的RECORDSET."

Const sCase7_LowerText = "相反顺序的RECORDSET创建成功." & vbCrLf & vbCrLf _
     & " 下一步用SQL创建查询内容."

Const sCase8_LowerText = "所有stor_Id字段数字为7131的记录." & vbCrLf & vbCrLf _
     & "  下一步将创建DEPARTMENT表."

Const sCase9_UpperText = "DEPARTMENT表创建成功."
Const sCase9_LowerText = "下一步将使用Recordset添加50条记录."

Const sCase10_LowerText = "添加50条记录成功." & vbCrLf & vbCrLf _
     & "下一步将使用RECORDSET对象更新50条记录."

Const sCase11_LowerText = "更新记录成功." & vbCrLf & vbCrLf _
     & " 下一步将使用RECORDSET对象删除50条记录."

Const sCase12_LowerText = "删除记录成功." & vbCrLf & vbCrLf _
     & "下一步将直接使用SQL语句添加50条记录."

Const sCase13_LowerText = "添加记录成功." & vbCrLf & vbCrLf _
     & " 下一步将直接使用SQL语句更新记录."

Const sCase14_LowerText = "更新记录成功." & vbCrLf & vbCrLf _
     & " 下一步将直接使用SQL语句删除记录."

Const sCase15_LowerText = "删除记录成功." & vbCrLf & vbCrLf _
     & "下一步将创建一个存储过程."

Const sCase16_UpperText = "创建存储过程成功."
Const sCase16_LowerText = "下一步将调用存储过程."

Const sCase17_LowerText = "调用存储过程成功，下一步将产生一个错误信息."

Const sCase18_UpperText = "ADO错误信息创建成功."
Const sCase18_LowerText = "下一步将执行对记录更新的批处理."

Const sCase19_LowerText = "对记录更新的批处理执行成功，下一步将执行对多记录集的查询."

Const sCase20_LowerText = "对多记录集的查询成功，下一步执行事务并回滚. " & _
    "这个事务删除DEPARTMENT表中所有记录.然后回滚恢复DEPARTMENT表."

Const sCase21_LowerText = "事务会滚成功，下一步将执行事务并确认事务. " & _
    "这个事务首先删除记录，然后更新表."

Const sCase22_LowerText = "事务确认成功，下一步将显示二进制数据."

Const sCase23_LowerText = "显示二进制数据成功，下一步将结束ADO应用程序的演示."

Const sCase24_UpperText = "完成ADO应用程序演示."
Const sCase24_LowerText = "下一步将重新执行程序，CANCEL将退出程序."


Private Sub Connect()

    Dim cn As New ADODB.Connection

    MousePointer = vbHourglass
    
    ' DSN Connection using the OLE DB provider for ODBC - MSDASQL
    cn.ConnectionString = "DSN=" & _
        ";DATABASE=pubs;UID=" & sLoginID & _
        ";PWD=" & sPassword
        
    ' Prompt the user to select the DSN
    cn.Properties("Prompt") = adPromptComplete
    cn.Open
    cn.Close
        
    MousePointer = vbDefault
    
End Sub

Private Sub DSNlessConnect()
    
    Dim cn As New ADODB.Connection
    
    MousePointer = vbHourglass
    
    ' DSNless Connection using the OLE DB provider for ODBC - MSDASSQL
    cn.ConnectionString = "DRIVER=SQL Server" & _
        ";SERVER=" & sServer & _
        ";UID=" & sLoginID & _
        ";PWD=" & sPassword & _
        ";DATABASE=pubs"
    cn.Open
    cn.Close
    
    MousePointer = vbDefault
    
End Sub
Private Sub SQLOLEDBConnect()

    MousePointer = vbHourglass
    
    ' Use the global connection object - cn
    ' Connect using the OLE DB provider for SQL Server - SQLOLEDB
    cn.ConnectionString = "PROVIDER=SQLOLEDB" & _
        ";SERVER=" & sServer & _
        ";UID=" & sLoginID & _
        ";PWD=" & sPassword & _
        ";DATABASE=pubs"
    cn.Open

    MousePointer = vbDefault
    
End Sub

Private Sub ForwardOnlyRecordset()
    
    Dim rs As New ADODB.Recordset
    
    Screen.MousePointer = vbHourglass
    
    ' Associate the Recordset with the open connection
    rs.ActiveConnection = cn
    
    'Use the open method
    rs.Open "Select * From stores", , , , adCmdText
    
    DisplayForwardGrid rs, Grid
    
    rs.Close
                    
    Screen.MousePointer = vbDefault

End Sub

Private Sub KeysetRecordset()
    
    Dim rs As New ADODB.Recordset
    
    Screen.MousePointer = vbHourglass
    
    ' Associate the Recordset with the open connection
    rs.ActiveConnection = cn
    rs.Source = "Select * From employee"


    ' Pass the Open method the SQL and Recordset type parameters
    rs.Open , , adOpenKeyset, adLockReadOnly
    
    ' Display the grid -- use a 1 to display in forward order
    DisplayKeysetGrid rs, Grid, 1
       
    rs.Close
        
    Screen.MousePointer = vbDefault

End Sub
Private Sub KeysetRecordsetReverse()
    
    Dim rs As New ADODB.Recordset
    
    Screen.MousePointer = vbHourglass
    
    ' Pass the Open method the SQL and Recordset type parameters
    rs.Open "Select * From employee", cn, adOpenKeyset, adLockReadOnly, adCmdText
    
    ' Display the grid -- use a 2 to display in reverse order
    DisplayKeysetGrid rs, Grid, 2
       
    rs.Close
        
    Screen.MousePointer = vbDefault

End Sub
 Private Sub CommandPS()
    
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim sSQL As String
    
    Screen.MousePointer = vbHourglass
    
    With cmd
        'Use a global conneciton object -- cn
        .ActiveConnection = cn
        'Set up the SQL statement that uses one parameter marker
        .CommandText = "Select * From sales Where stor_id = ?"
        'Specify the parameter characteristics
        .CreateParameter , adChar, adParamInput, 4
        'Set the parameter value
        .Parameters(0).Value = "7131"
    End With
     
    'Setup the input parameter
    Set rs = cmd.Execute
    
    'Display the Recordset in a grid
    DisplayForwardGrid rs, Grid
   
    rs.Close
        
    Screen.MousePointer = vbDefault

 End Sub
Private Sub CreateTable()

    Dim sSQL As String
    On Error Resume Next
    
    Screen.MousePointer = vbHourglass
    
    'Make certain that the table is created by dropping the table
    ' If the table doesn't exist the code will move on to the next statement
    sSQL = "Drop Table department"
    cn.Execute sSQL
       
    'Reset the error handler and create the table
    ' If an error is encountered it will be displayed
    On Error GoTo ErrorHandler
    sSQL = "Create Table department " _
        & "(Dep_ID Int Not Null, Dep_Name Char(25), Primary Key(Dep_ID))"
    cn.Execute sSQL
    
    Screen.MousePointer = vbDefault
    Exit Sub
    
ErrorHandler:
    DisplayADOError cn
    Screen.MousePointer = vbDefault
     
End Sub
Private Sub CursorAdd()

    Dim rs As New ADODB.Recordset
    Dim i As Integer
    
    Screen.MousePointer = vbHourglass
    
    'Pass in the SQL, Connection, Cursor type, lock type and source type
    rs.Open "Select Dep_ID, Dep_Name From department", _
            cn, adOpenKeyset, adLockOptimistic, adCmdText
        
    'Add 50 rows to the department table
    ' Note that the Bang ! notation is used to specfy column names
    For i = 1 To 50
        rs.AddNew
        rs!Dep_ID = i
        rs!Dep_Name = "Department " & CStr(i)
        rs.Update
    Next
    
    'Display the new rows in a grid
    DisplayKeysetGrid rs, Grid, 1
    
    rs.Close
    
    Screen.MousePointer = vbDefault

End Sub
Private Sub CursorUpdate()

    Dim rs As New ADODB.Recordset
    Dim i As Integer
    Dim sTemp As String
        
    Screen.MousePointer = vbHourglass
    
    ' Pass in SQL, Connection, cursor type, lock type and source type
    rs.Open "Select Dep_ID, Dep_Name From department", _
            cn, adOpenKeyset, adLockOptimistic, adCmdText
 
    Do Until rs.EOF
        'Trim off the blanks because ADO doesn't truncate the data
        sTemp = Trim(rs!Dep_Name)
        rs!Dep_Name = "Updated " & sTemp
        'Update the row
        rs.Update
        rs.MoveNext
    Loop
    
    'Display the updated rows in a grid
    DisplayKeysetGrid rs, Grid, 1
    
    rs.Close
    
    Screen.MousePointer = vbDefault
    
End Sub
Private Sub CursorDelete()

    Dim rs As New ADODB.Recordset
    Dim i As Integer
    
    Screen.MousePointer = vbHourglass
    
    'Pass in the SQL, Connection, cursor type, lock type and source type
    ' Note that this is a forward-only cursor but it can update the current row
    rs.Open "Select Dep_ID, Dep_Name From department", _
            cn, adOpenForwardOnly, adLockOptimistic, adCmdText
    
    'Delete all of the rows
    Do Until rs.EOF
        rs.Delete
        rs.MoveNext
    Loop
    
    'Display the empty Recordset in a grid
    DisplayForwardGrid rs, Grid
    
    rs.Close
    
    Screen.MousePointer = vbDefault

End Sub

Private Sub PreparedAdd()
    
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim i As Integer
    
    Screen.MousePointer = vbHourglass
    
    'Setup the Command object's Connection, SQL and parameter types
    With cmd
        .ActiveConnection = cn
        .CommandText = "Insert Into department Values(?,?)"
        .CreateParameter , adChar, adParamInput, 4
        .CreateParameter , adChar, adParamInput, 25
    End With