Chip 1999 August/Chip_1999-08_cd.bin/msdn/VSTUDIO/MSDE/msde_vb6.exe/frmMain.frm

Visual Basic Form | 1999-05-13 | 8KB | 211 lines

VERSION 5.00
Begin VB.Form frmMain
   Caption         =   "MSDE Sample"
   ClientHeight    =   5775
   ClientLeft      =   1080
   ClientTop       =   1545
   ClientWidth     =   9765
   LinkTopic       =   "Form1"
   ScaleHeight     =   5775
   ScaleWidth      =   9765
   Begin VB.Frame Frame1
      Caption         =   "Connection Type"
      Height          =   975
      Left            =   240
      TabIndex        =   3
      Top             =   120
      Width           =   1575
      Begin VB.OptionButton optConnectionType
         Caption         =   "DAO"
         Height          =   255
         Index           =   1
         Left            =   120
         TabIndex        =   5
         Top             =   600
         Width           =   735
      End
      Begin VB.OptionButton optConnectionType
         Caption         =   "ADO"
         Height          =   255
         Index           =   0
         Left            =   120
         TabIndex        =   4
         Top             =   240
         Value           =   -1  'True
         Width           =   735
      End
   End
   Begin VB.CommandButton cmdStoredProc
      Caption         =   "Stored Procedure"
      Height          =   375
      Left            =   240
      TabIndex        =   2
      Top             =   1200
      Width           =   1575
   End
   Begin VB.TextBox txtResults
      Height          =   3495
      Left            =   240
      MultiLine       =   -1  'True
      TabIndex        =   0
      Top             =   2040
      Width           =   9255
   End
   Begin VB.Label Label1
      Caption         =   "Results:"
      Height          =   255
      Left            =   240
      TabIndex        =   1
      Top             =   1800
      Width           =   1335
   End
Attribute VB_Name = "frmMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private Sub cmdStoredProc_Click()
    SelectedConnectionType = Not optConnectionType(ConnectionType.ADO).Value
    Dim Counter As Single
    Select Case SelectedConnectionType

        Case ConnectionType.ADO

            Dim adoCommand As ADODB.Command
            Dim adoResults As ADODB.Recordset
            Dim adoParm As ADODB.Parameter

            Set adoCommand = CreateObject("ADODB.Command")
            Set adoResults = CreateObject("ADODB.Recordset")

            With adoCommand
                ' Indicate that we will be calling a stored procedure.
                ' You can also execte SQL text, open a table, or open
                ' a recordset from a file.
                .CommandType = adCmdStoredProc
                .CommandText = vbQuote & "Sales By Year" & vbQuote
                ' The fastest way to execute the stored proc.
                ' is to append any parameters to the parameters collection.
                ' However, you have to know all of the required info about parameters
                ' to do this.
                Set adoParm = .CreateParameter("@Beginning_Date", adDBTimeStamp, adParamInput, , #8/1/1996#)  ' ***
                .Parameters.Append adoParm ' ***
                Set adoParm = .CreateParameter("@Ending_Date", adDBTimeStamp, adParamInput, , #8/15/1996#)  ' ***
                .Parameters.Append adoParm ' ***
                ' The next line connects us to the database. Notice that
                ' we don't have to create a Connection object. Instead, we just use
                ' the connection string. We are re-establishing the connection
                ' each time we execute the procedure. Caching the connection (in
                ' a global variable for example) or enabling ODBC connection
                ' pooling would speed query execution.
                .ActiveConnection = ADOConnectionString
                ' Uncomment the following two lines (and comment out the starred (***)
                ' lines above) to have the parameters collection
                ' "build" itself
'                 .Parameters.Refresh
'                 .Parameters("@Beginning_Date") = #8/1/1996#
'                 .Parameters("@Ending_Date") = #8/15/1996#
            End With

            Counter = Timer()

            With adoResults
                ' A client-side cursor will allow us to disconnect the recordset
                ' later.
                .CursorLocation = adUseClient
                .Open adoCommand, , adOpenStatic, adLockBatchOptimistic

                Counter = Timer() - Counter
                ' Disconnect the recordset to conserve database resources.
                ' Data is cached locally, but no connection to the database
                ' exists. You can reconnect the recordset later by setting the
                ' ActiveConnection property again.
                Set .ActiveConnection = Nothing

                ' Call function to display data.
                DisplayRecordset adoResults

                .Close
            End With

            Set adoCommand = Nothing

        Case ConnectionType.DAO

            Dim wrkODBC As DAO.Workspace
            Dim daoConnection As DAO.Connection
            Dim daoQueryDef As DAO.QueryDef
            Dim daoResults As DAO.Recordset

            ' Create an ODBCDirect workspace and connect to the database.
            Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
            Set daoConnection = wrkODBC.OpenConnection("Northwind", , False, DAOConnectionString)

            Counter = Timer()

            ' Create a query def. and assign parameter values.
            ' You need to use the ODBC call syntax with DAO.
            Set daoQueryDef = daoConnection.CreateQueryDef("name", "{ call " & vbQuote & "Sales By Year" & vbQuote & " ( '8/1/1996', '8/15/1996') }")

            Set daoResults = daoQueryDef.OpenRecordset

            Counter = Timer() - Counter
            DisplayRecordset daoResults

            ' Clean up.
            daoResults.Close
            daoQueryDef.Close
            daoConnection.Close
            wrkODBC.Close

            Set daoResults = Nothing
            Set daoQueryDef = Nothing
            Set daoConnection = Nothing
            Set wrkODBC = Nothing

    End Select

    txtResults = txtResults.Text & vbCrLf & vbCrLf & Counter & " seconds elapsed."

End Sub
Private Sub Form_Load()
    optConnectionType(ConnectionType.ADO).Value = Not SelectedConnectionType
    optConnectionType(ConnectionType.ADO + 1).Value = SelectedConnectionType
End Sub
Private Sub Form_Resize()
    If Me.Width > 2160 Then
        txtResults.Width = Me.Width - 630
    Else
        Me.Width = 2160
    End If
    If Me.Height > 3600 Then
        txtResults.Height = Me.Height - 2685
    Else
        Me.Height = 3600
    End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
    End
End Sub
Private Sub DisplayRecordset(ByVal RecordsetToDisplay As Object)
    Dim objField As Object
    Dim HeaderString As String
    Dim DataString As String
    txtResults.Text = ""
    HeaderString = ""
    DataString = ""
    For Each objField In RecordsetToDisplay.Fields
        HeaderString = HeaderString & objField.Name & vbTab
    Next ' objField
    HeaderString = HeaderString & vbCrLf
    While Not RecordsetToDisplay.EOF
        For Each objField In RecordsetToDisplay.Fields
            DataString = DataString & objField.Value & vbTab
        Next ' objField
        DataString = DataString & vbCrLf
        RecordsetToDisplay.MoveNext
    Wend
    txtResults.Text = HeaderString & DataString
End Sub