form1.vb

Beginning VB.NET DatabasesAll_Code.rar

Imports System.Data.OleDb

Public Class Form1
    Inherits System.Windows.Forms.Form

    'Form level variables
    Private strConnectionString As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=..\ProjectTimeTracker.mdb;"

    Private objConnection As OleDbConnection
    Private objCommand As OleDbCommand
    Private objDataAdapter As OleDbDataAdapter
    Private objDataSet As DataSet

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents btnNonParameterQuery As System.Windows.Forms.Button
    Friend WithEvents grdResults As System.Windows.Forms.DataGrid
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents txtProjectID As System.Windows.Forms.TextBox
    Friend WithEvents btnParameterQuery As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.btnNonParameterQuery = New System.Windows.Forms.Button
        Me.grdResults = New System.Windows.Forms.DataGrid
        Me.Label1 = New System.Windows.Forms.Label
        Me.txtProjectID = New System.Windows.Forms.TextBox
        Me.btnParameterQuery = New System.Windows.Forms.Button
        CType(Me.grdResults, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'btnNonParameterQuery
        '
        Me.btnNonParameterQuery.Anchor = CType((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.btnNonParameterQuery.Location = New System.Drawing.Point(264, 8)
        Me.btnNonParameterQuery.Name = "btnNonParameterQuery"
        Me.btnNonParameterQuery.Size = New System.Drawing.Size(144, 23)
        Me.btnNonParameterQuery.TabIndex = 0
        Me.btnNonParameterQuery.Text = "Non-Parameter Query"
        '
        'grdResults
        '
        Me.grdResults.Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                    Or System.Windows.Forms.AnchorStyles.Left) _
                    Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.grdResults.CaptionVisible = False
        Me.grdResults.DataMember = ""
        Me.grdResults.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.grdResults.Location = New System.Drawing.Point(0, 72)
        Me.grdResults.Name = "grdResults"
        Me.grdResults.Size = New System.Drawing.Size(416, 118)
        Me.grdResults.TabIndex = 1
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(0, 42)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(56, 16)
        Me.Label1.TabIndex = 4
        Me.Label1.Text = "Project ID"
        Me.Label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        '
        'txtProjectID
        '
        Me.txtProjectID.Location = New System.Drawing.Point(64, 40)
        Me.txtProjectID.Name = "txtProjectID"
        Me.txtProjectID.Size = New System.Drawing.Size(192, 20)
        Me.txtProjectID.TabIndex = 5
        Me.txtProjectID.Text = ""
        '
        'btnParameterQuery
        '
        Me.btnParameterQuery.Anchor = CType((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.btnParameterQuery.Location = New System.Drawing.Point(264, 39)
        Me.btnParameterQuery.Name = "btnParameterQuery"
        Me.btnParameterQuery.Size = New System.Drawing.Size(144, 23)
        Me.btnParameterQuery.TabIndex = 6
        Me.btnParameterQuery.Text = "Parameter Query"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(416, 189)
        Me.Controls.Add(Me.btnParameterQuery)
        Me.Controls.Add(Me.txtProjectID)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.grdResults)
        Me.Controls.Add(Me.btnNonParameterQuery)
        Me.Name = "Form1"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "Access Queries"
        CType(Me.grdResults, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub PopulateGrid()
        'Initialize a new instance of the OleDbDataAdapter class
        objDataAdapter = New OleDbDataAdapter

        'Initialize a new instance of the DataSet class
        objDataSet = New DataSet

        'Set the SelectCommand for the OleDbDataAdapter
        objDataAdapter.SelectCommand = objCommand

        Try
            'Populate the DataSet
            objDataAdapter.Fill(objDataSet, "Projects")

            'Bind the DataSet to the DataGrid
            grdResults.SetDataBinding(objDataSet, "Projects")

            'Only execute if the table style has not been created
            If Not grdResults.TableStyles.Contains("Projects") Then

                'Declare a DataGridTableStyle object
                Dim objDataGridTableStyle As New DataGridTableStyle

                'Set the AlternatingBackColor property
                objDataGridTableStyle.AlternatingBackColor = Color.WhiteSmoke

                'Set the MappingName for the DataGridTableStyle
                objDataGridTableStyle.MappingName = "Projects"

                'Add the DataGridTableStyle to the DataGrid
                grdResults.TableStyles.Add(objDataGridTableStyle)

                'Set the column widths
                grdResults.TableStyles.Item("Projects").GridColumnStyles.Item( _
                    "ProjectID").Width = 200
                grdResults.TableStyles.Item("Projects").GridColumnStyles.Item( _
                    "ProjectName").Width = 100
                grdResults.TableStyles.Item("Projects").GridColumnStyles.Item( _
                    "ProjectDescription").Width = 125
                grdResults.TableStyles.Item("Projects").GridColumnStyles.Item( _
                    "SequenceNumber").Width = 100
                grdResults.TableStyles.Item("Projects").GridColumnStyles.Item( _
                    "LastUpdateDate").Width = 100

                'Right Align SequenceNumber column
                grdResults.TableStyles.Item("Projects").GridColumnStyles.Item( _
                    "SequenceNumber").Alignment = HorizontalAlignment.Right

                'Cleanup
                objDataGridTableStyle.Dispose()
                objDataGridTableStyle = Nothing
            End If
        Catch OleDbException As OleDbException
            Debug.WriteLine(OleDbException.Message)
        End Try

        'Cleanup
        objCommand.Dispose()
        objCommand = Nothing
        objDataAdapter.Dispose()
        objDataAdapter = Nothing
        objDataSet.Dispose()
        objDataSet = Nothing
        objConnection.Dispose()
        objConnection = Nothing
    End Sub

    Private Sub btnNonParameterQuery_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnNonParameterQuery.Click
        'Initialize a new instance of the OleDbConnection class 
        objConnection = New OleDbConnection(strConnectionString)

        'Initialize a new instance of the OleDbCommand class
        objCommand = New OleDbCommand

        'Set the objCommand object properties
        objCommand.CommandText = "usp_SelectProjects"
        objCommand.CommandType = CommandType.StoredProcedure
        objCommand.Connection = objConnection

        'Populate the DataGrid
        Call PopulateGrid()
    End Sub

    Private Sub btnParameterQuery_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnParameterQuery.Click
        'Initialize a new instance of the OleDbConnection class 
        objConnection = New OleDbConnection(strConnectionString)

        'Initialize a new instance of the OleDbCommand class
        objCommand = New OleDbCommand

        'Set the objCommand object properties
        objCommand.CommandText = "usp_SelectProject"
        objCommand.CommandType = CommandType.StoredProcedure
        objCommand.Connection = objConnection

        'Add the required parameter for the query
        objCommand.Parameters.Add("@ProjectID", OleDbType.Guid, 16).Value = _
            New Guid(txtProjectID.Text)

        'Populate the DataGrid
        Call PopulateGrid()
    End Sub
End Class