form1.vb

Beginning VB.NET DatabasesAll_Code.rar

        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(466, 359)
        Me.Controls.Add(Me.GroupBox2)
        Me.Controls.Add(Me.GroupBox1)
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "Form1"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "Access SQL"
        Me.GroupBox1.ResumeLayout(False)
        Me.GroupBox2.ResumeLayout(False)
        CType(Me.grdResults, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

#Region " Connection Procedures "
    Private Sub btnDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDatabase.Click
        'Delcare OpenFileDialog object
        Dim objOpenFileDialog As New OpenFileDialog

        'Set the OpenFileDialog properties
        With objOpenFileDialog
            .Filter = "Access Database (*.mdb)|*.mdb|All files (*.*)|*.*"
            .FilterIndex = 1
            .InitialDirectory = "C:\"
            .Title = "Open Access Database"
        End With

        'Show the dialog
        If objOpenFileDialog.ShowDialog = DialogResult.OK Then
            'If the Open button was clicked, then load the file name selected
            txtDatabase.Text = objOpenFileDialog.FileName
            'Change the status
            lblStatus.Text = "Database is set"
        End If

        'Cleanup
        objOpenFileDialog.Dispose()
        objOpenFileDialog = Nothing
    End Sub

    Private Sub btnOpenConnection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenConnection.Click
        'Initialize a new instance of the OleDbConnection class 
        objConnection = New OleDbConnection( _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & txtDatabase.Text & ";")

        Try
            'Open the connection
            objConnection.Open()
        Catch OleDbExceptionErr As OleDbException
            'Display the error
            MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
        Catch InvalidOperationExceptionErr As InvalidOperationException
            'Display the error
            MessageBox.Show(InvalidOperationExceptionErr.Message, "Access SQL")
        End Try

        'Check the state of the connection and report appropriately
        If objConnection.State = ConnectionState.Open Then
            lblStatus.Text = "Database connection is open"
        Else
            lblStatus.Text = "Database connection failed"
        End If
    End Sub

    Private Sub btnCloseConnection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCloseConnection.Click
        'Close the connection
        objConnection.Close()
        'Display the status of the connection
        lblStatus.Text = "Database connection is closed"

        'Cleanup
        objConnection.Dispose()
        objConnection = Nothing
    End Sub
#End Region

#Region " In-Line SQL Procedures "
    Private Function IsConnectionOpen() As Boolean
        'Is the connection object set to a valid instance
        'of the OleDbConnection class
        If objConnection Is Nothing Then
            Return False
        End If

        'Is the connection open
        If objConnection.State <> ConnectionState.Open Then
            Return False
        End If

        'If we made it this far return True
        Return True
    End Function

    Private Sub btnDataReader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataReader.Click
        'Validate connection state
        If Not IsConnectionOpen() Then
            Exit Sub
        End If

        'Declare and initialize a new instance of the OleDbCommand class
        Dim objCommand As New OleDbCommand(txtSQL.Text, objConnection)

        'Set the CommandType property
        If optText.Checked Then
            objCommand.CommandType = CommandType.Text
        Else
            objCommand.CommandType = CommandType.TableDirect
        End If

        'Declare a OleDbDataReader object
        Dim objDataReader As OleDbDataReader

        'Declare a String variable
        Dim strData As String

        Try
            'Execute the SQL text
            objDataReader = objCommand.ExecuteReader()

            'Check to see if we have data
            If objDataReader.HasRows Then
                'Process all rows
                While objDataReader.Read()
                    'Clear the variable
                    strData = String.Empty
                    'Get the data in each column
                    For intIndex As Integer = 0 To objDataReader.FieldCount - 1
                        strData &= objDataReader.Item(intIndex).ToString & ", "
                    Next
                    'Remove the last comma from the string
                    strData = strData.Remove(strData.Length - 2, 2)
                    'Write the data to the TextBox
                    txtSQL.Text &= ControlChars.CrLf & strData
                End While
            End If

            'Close the reader
            objDataReader.Close()
        Catch OleDbExceptionErr As OleDbException
            MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
        End Try

        'Cleanup
        objCommand.Dispose()
        objCommand = Nothing
        objDataReader = Nothing
    End Sub

    Private Sub btnDataAdapter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataAdapter.Click
        'Validate connection state
        If Not IsConnectionOpen() Then
            Exit Sub
        End If

        'Declare and initialize a new instance of the OleDbCommand class
        Dim objCommand As New OleDbCommand(txtSQL.Text, objConnection)

        'Set the CommandType property
        If optText.Checked Then
            objCommand.CommandType = CommandType.Text
        Else
            objCommand.CommandType = CommandType.TableDirect
        End If

        'Declare a OleDbDataAdapter object
        Dim objDataAdapter As New OleDbDataAdapter

        'Declare a DataSet object
        Dim objDataSet As New DataSet

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

        Try
            'Populate the DataSet
            objDataAdapter.Fill(objDataSet)

            ''Bind the DataSet to the DataGrid
            'grdResults.DataSource = objDataSet
            ''Tell the DataGrid which table in the DataSet to use
            'grdResults.DataMember = objDataSet.Tables(0).TableName

            'Alternate data binding
            grdResults.SetDataBinding(objDataSet, objDataSet.Tables(0).TableName)

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

            'Set the GridLineStyle property
            grdResults.GridLineStyle = DataGridLineStyle.None

            'Set the SelectionBackColor and SelectionForeColor properties
            grdResults.SelectionBackColor = Color.LightGray
            grdResults.SelectionForeColor = Color.Black

        Catch OleDbExceptionErr As OleDbException
            MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
        End Try

        'Cleanup
        objCommand.Dispose()
        objCommand = Nothing
        objDataAdapter.Dispose()
        objDataAdapter = Nothing
        objDataSet.Dispose()
        objDataSet = Nothing
    End Sub
#End Region
End Class