formwiz.frm

关于VB模块的

    'Execute the prepared SQL statement to add 50 rows
    For i = 1 To 50
        cmd.Parameters(0) = CStr(i)
        cmd.Parameters(1) = "Department " & CStr(i)
        cmd.Execute
    Next
    
    'Create a recordset to display the new rows
    rs.Open "Select * From department", cn, , , adCmdText
    DisplayForwardGrid rs, Grid
    
    rs.Close
   
    Screen.MousePointer = vbDefault
    
End Sub

Private Sub PreparedUpdate()

    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 = "Update department Set Dep_Name = ? Where Dep_ID = ?"
        .CreateParameter , adChar, adParamInput, 25
        .CreateParameter , adChar, adParamInput, 4
    End With
    
    ' Execute the prepared SQL statement to update 50 rows
    For i = 0 To 50
        cmd.Parameters(0).Value = "Updated Department " & CStr(i)
        cmd.Parameters(1).Value = CStr(i)
        cmd.Execute
    Next
    
    ' Create a recordset to display the updated rows
    rs.Open "Select * From department", cn, , , adCmdText
    DisplayForwardGrid rs, Grid
    
    rs.Close
       
    Screen.MousePointer = vbDefault
    
End Sub

Private Sub PreparedDelete()

    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 and SQL command
    With cmd
        .ActiveConnection = cn
        .CommandText = "Delete department"
    End With
    
    'Execute the SQL once (that's all that is needed)
    cmd.Execute
    
    'Create a recordset to display the empty table
    rs.Open "Select * From department", cn, , , adCmdText
    DisplayForwardGrid rs, Grid
    
    rs.Close
    
    Screen.MousePointer = vbDefault
    
End Sub
Private Sub CreateSP()
    
    Dim sSQL As String
    On Error Resume Next
    
    Screen.MousePointer = vbHourglass
    
    sSQL = "Drop Proc CountStoreQty"
    cn.Execute sSQL
    
    On Error GoTo ErrorHandler
    
    sSQL = "Create Proc CountStoreQty" _
        & "(@stor_id Char(4), @qty Int Output) As " _
        & "Select @qty = Sum(qty) From sales Where stor_id = @stor_id"
    
     cn.Execute sSQL
     Screen.MousePointer = vbDefault
    Exit Sub
   
ErrorHandler:
    DisplayADOError cn
    Screen.MousePointer = vbDefault

End Sub

Private Sub CallSP()
    
    Dim cmd As New ADODB.Command
    Dim parm0 As New ADODB.Parameter
    Dim parm1 As New ADODB.Parameter
    Dim sSQL As String
    On Error GoTo ErrorHandler
                   
    Screen.MousePointer = vbHourglass
    
    'Use the global cn Conneciton object
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "CountStoreQty"
    
    parm0.Direction = adParamInput
    parm0.Type = adChar
    parm0.Size = 4
    cmd.Parameters.Append parm0
    
    parm1.Direction = adParamOutput
    parm1.Type = adInteger
    parm1.Size = 4
    cmd.Parameters.Append parm1
    
    parm0.Value = "7067"
    cmd.Execute
    
    Label_Mid.Caption = "Total Qty for Store 7067"
    Text_Mid.Text = parm1.Value

    Screen.MousePointer = vbDefault
   
ErrorHandler:
    DisplayADOError cn

    Screen.MousePointer = vbDefault

End Sub
Private Sub ShowError()
    
    Dim rs As New ADODB.Recordset
    On Error GoTo ErrorHandler
    Screen.MousePointer = vbHourglass
    
    rs.Open "Select * From no_such_table", cn
    rs.Close
    
    Screen.MousePointer = vbDefault
    Exit Sub
    
ErrorHandler:
    DisplayADOError cn
    Screen.MousePointer = vbDefault

End Sub
Private Sub BatchUpdate()

    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, adLockBatchOptimistic, adCmdText
        
    'Add 50 rows to the department table
    For i = 1 To 50
        rs.AddNew
        rs!Dep_ID = i
        rs!Dep_Name = "Add Batch Department " & CStr(i)
        rs.Update
    Next
    
    rs.UpdateBatch
    
    'Display the new rows in a grid
    DisplayKeysetGrid rs, Grid, 1
    
    rs.Close
    
    Screen.MousePointer = vbDefault

End Sub

Private Sub MultipleRS()

    Dim rs As New ADODB.Recordset
    Dim fld As Field
    Dim sSQL As String
    Dim i As Integer
    Dim nFldCount As Integer
    
    Screen.MousePointer = vbHourglass
    
    'Setup the three SELECT statements
    sSQL = "Select au_lname, au_fname From authors; "
    sSQL = sSQL & "Select title From titles; "
    sSQL = sSQL & "Select stor_name From stores "
                    
    rs.Open sSQL, cn
    
    ' Setup the grid
    Grid.Redraw = False
    Grid.FixedRows = 0
    Grid.Cols = 2
    Grid.ColWidth(0) = TextWidth(String(rs.Fields(0).DefinedSize + 2, "a"))
    Grid.ColWidth(1) = TextWidth(String(rs.Fields(1).DefinedSize + 2, "a"))
    Grid.Rows = 1
    Grid.Row = 0
    i = 1

    Do
        Grid.Col = 0
        Grid.CellBackColor = &HC0C0C0
        Grid.Text = "Recordset Number: " & i
        Grid.Col = 1
        Grid.CellBackColor = &HC0C0C0
        Grid.Text = ""
        
        Do Until rs.EOF
            Grid.Rows = Grid.Rows + 1
            Grid.Row = Grid.Row + 1
            nFldCount = 0
            'Loop through all fields
            For Each fld In rs.Fields
                Grid.Col = nFldCount
                Grid.Text = fld.Value
                nFldCount = nFldCount + 1
            Next fld
            rs.MoveNext
        Loop

        i = i + 1
        Set rs = rs.NextRecordset
    Loop Until rs.State = adStateClosed
    Grid.Redraw = True
    
    Screen.MousePointer = vbDefault

End Sub
Private Sub TransRollBack()

    Dim rs As New ADODB.Recordset
    
    Screen.MousePointer = vbHourglass
                
    'Start a transaction using the exisitn Connection object
    cn.BeginTrans
    
    'Execute SQL to delete all of the rows from the table
    cn.Execute "Delete department"
        
    'Now Rollback the transaction - the table is unchanged
    cn.RollbackTrans
        
    'Create a recordset to display the unchangted table
    rs.Open "Select * From department", cn, , , adCmdText
    DisplayForwardGrid rs, Grid
    rs.Close
    
    Screen.MousePointer = vbDefault

End Sub
Private Sub TransCommit()

    Dim rs As New ADODB.Recordset
    
    Screen.MousePointer = vbHourglass
        
    'Start a transaction using the existing Connection object
    cn.BeginTrans
    
    'Execute SQL to delete all of the rows from the table
    cn.Execute "Delete department"
    
    'Commit the transaction and update the table
    cn.CommitTrans
        
    'Create a recordset to display the empty table
    rs.Open "Select * From department", cn, , , adCmdText
    DisplayForwardGrid rs, Grid
    rs.Close

    
    Screen.MousePointer = vbDefault

End Sub

Private Sub BinaryData()
    
    Dim rs As New ADODB.Recordset
    Dim fld As ADODB.Field
    
    Screen.MousePointer = vbHourglass
    
    rs.Open "Select pub_id, logo From pub_info", cn, , , adCmdText
    
    ' Setup the grid
    Grid.Redraw = False
    Grid.Cols = rs.Fields.Count
    Grid.Rows = 1
    Grid.Row = 0
    Grid.Col = 0
    Grid.Clear
    'Size the grid columns bigger thatn normal
    Grid.ColWidth(0) = TextWidth(String(rs.Fields(0).ActualSize + 4, "a"))
    Grid.ColWidth(1) = TextWidth(String(200, "a"))
    Grid.RowHeightMin = Grid.RowHeight(0) * 3
            
    'Setup the headings
     For Each fld In rs.Fields
         Grid.Text = fld.Name
         If Grid.Col < rs.Fields.Count - 1 Then
            Grid.Col = Grid.Col + 1
         End If
    Next fld

    
    ' Move through each row in the record set
    Do Until rs.EOF
        
        ' Set the position in the grid
        Grid.Rows = Grid.Rows + 1
        Grid.Row = Grid.Rows - 1
        Grid.Col = 0
        
        'Loop through all fields
        For Each fld In rs.Fields
            If fld.Type = adLongVarBinary Then
                'Store the image into a temp bitmap file
                BintoFile "tempbmp.bmp", fld
                Grid.CellPictureAlignment = flexAlignLeftCenter
                'Load the temporary bitmap into the grid
                Set Grid.CellPicture = LoadPicture("tempbmp.bmp")
            Else
                'Treat the column as regular data
                Grid.Text = fld.Value
            End If
            If Grid.Col < rs.Fields.Count - 1 Then
                Grid.Col = Grid.Col + 1
            End If
        Next fld
    
        rs.MoveNext
    Loop
    Grid.Redraw = True
    
    rs.Close
    
    Screen.MousePointer = vbDefault

End Sub
Private Sub Finish()
End Sub
Private Sub Reset()
    
    'Close any open connections
    cn.Close
    
    'Reset the grid size
    Grid.RowHeightMin = 240

    'Initialize the program control values
    Label1.Visible = True
    Label2.Visible = True
    Grid.Visible = False
    Label_Top.Visible = False
    Text_Top.Visible = False
    Label_Mid.Caption = "User ID"
    Label_Mid.Visible = False
    Text_Mid.Visible = False
    Text_Mid.Enabled = True
    Label_Bot.Caption = "Password"
    Label_Bot.Visible = False
    Text_Bot.Visible = False
    Text_Bot.PasswordChar = "*"


    
    Text_Top.Text = sServer
    Text_Mid.Text = sLoginID
    Text_Bot.Text = sPassword
    
    Label1.Caption = sCase0_UpperText
    Label2.Caption = sCase0_LowerText


End Sub


Private Sub Command_Next_Click()

    Select Case nState
        
        Case 1

            'Get the Server, Login and Password that are required to connect
            nState = nState + 1
            Label1.Visible = False
            Label_Top.Visible = True
            Label_Mid.Visible = True
            Label_Bot.Visible = True
            Text_Top.Visible = True
            Text_Mid.Visible = True
            Text_Bot.Visible = True
            Text_Top.SetFocus
            Label2.Caption = sCase1_LowerText
       Case 2
            sServer = Text_Top.Text
            sLoginID = Text_Mid.Text
            sPassword = Text_Bot.Text
            Connect
            nState = nState + 1
            Label1.Visible = True
            Label_Top.Visible = False
            Label_Mid.Visible = False
            Label_Bot.Visible = False
            Text_Top.Visible = False
            Text_Mid.Visible = False
            Text_Bot.Visible = False
            Label1.Caption = sCase2_UpperText
            Label2.Caption = sCase2_LowerText
            Beep
        Case 3
            DSNlessConnect
            nState = nState + 1
            Label1.Visible = True
            Label_Top.Visible = False
            Label_Mid.Visible = False
            Label_Bot.Visible = False
            Text_Top.Text = ""
            Text_Mid.Text = ""
            Text_Bot.Text = ""
            Text_Top.Visible = False
            Text_Mid.Visible = False
            Text_Bot.Visible = False
            Label1.Caption = sCase3_UpperText
            Label2.Caption = sCase3_LowerText
            Beep
        Case 4
            SQLOLEDBConnect
            nState = nState + 1
            Label1.Visible = True
            Grid.Visible = False
            Label_Top.Visible = False
            Label_Mid.Visible = False
            Label_Bot.Visible = False
            Text_Top.Visible = False
            Text_Mid.Visible = False
            Text_Bot.Visible = False
            Label1.Caption = sCase4_UpperText
            Label2.Caption = sCase4_LowerText
            Beep
        Case 5
            ForwardOnlyRecordset
            nState = nState + 1
            Label1.Visible = False
            Grid.Visible = True
            Label_Top.Visible = False
            Label_Mid.Visible = False
            Label_Bot.Visible = False
            Text_Top.Visible = False
            Text_Mid.Visible = False
            Text_Bot.Visible = False
            Label2.Caption = sCase5_LowerText
            Beep
         Case 6
            KeysetRecordset