mnfrm.vb

This application uses OleDb as a backhand communicator with the file to allow the user to : select,

        Private Sub menuF_open_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles menuF_open.Click
            'Check for any data changes!!
            If Check_If_Data_Changed() = True Then
                'Alert the user that data is not saved!!
                Dim r As DialogResult = MessageBox.Show("The database file changed, are sure you want open file without saving this file ?", "Open File Without Saving this File", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)
                If r = DialogResult.No Then Exit Sub
            End If
            menuOpenClick()
        End Sub

        Private Sub menuF_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles menuF_save.Click
            'Save the changes to the source database the mdb file
            btnUpdate.Focus()
            Dim R As DialogResult = MessageBox.Show("Are you sure you want to save, changes will be permenant", "Save Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)
            If R = DialogResult.Yes Then Save_File()
        End Sub

        Private Sub menuF_exit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles menuF_exit.Click
            'Exit the application
            'Check and Alert the user if data changed and is not saved!!
            If Check_If_Data_Changed() = True Then
                Dim r As DialogResult = MessageBox.Show("The database file changed, are sure you want exit without saving ?", "Exit Without Save", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)
                If r = DialogResult.Yes Then
                    Application.Exit()
                End If
            Else
                Application.Exit()
            End If
        End Sub

        Private Sub menuSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles menuSearch.Click
            'Search the data using simple search techniques
            'or by using filters
            'in order to show correct data check if the data
            'is changed without save or not
            If DataLoaded = False Then Exit Sub

            menuSearchClick()

        End Sub

#End Region

#Region "Save"
        'This routine handles saving
        'data changes to the main file
        Private Sub Save_File()
            ' Create a new dataset to hold the changes that have
            ' been made to the main dataset.
            Dim objDataSetChanges As New DataSet()
            ' Stop any current edits.
            Me.BindingContext(accessDataSet, comboTables.Text).EndCurrentEdit()
            ' Get the changes that have been made to the main dataset.
            objDataSetChanges = CType(accessDataSet.GetChanges(), DataSet)
            ' Check to see if any changes have been made.
            If Not (objDataSetChanges Is Nothing) Then
                ' There are changes that need to be made, so attempt to update the datasource by
                ' calling the update method and passing the dataset and any parameters.
                UpdateDataSource(objDataSetChanges)

                'Make sure the database connection is closed!!
                If Me.accessConnection.State.ToString() <> "Closed" Then
                    Me.accessConnection.Close()
                End If
            End If
        End Sub 'Save_File
#End Region

#Region "Menu Open"
        ' Menu Open Click routine!!
        Public Sub menuOpenClick()
            Dim openFile As New OpenFileDialog()
            openFile.FileName = ""
            'Make sure only *.mdb files can be opened
            'by using a filter
            openFile.Filter = "Microsoft Access Application (*.mdb)|*.mdb"

            Dim res As System.Windows.Forms.DialogResult = openFile.ShowDialog()
            If res = System.Windows.Forms.DialogResult.Cancel Then
                Return
            End If
            'Change the mouse icon and caption 
            'of the form to inform the user that
            'the data is being loaded
            Me.Cursor = Cursors.WaitCursor
            Me.Text = "DataEasy: Loading Data Please Wait..."

            'The connection parameters
            accessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + openFile.FileName '
            'ToDo: Error processing original source shown below
            '
            '
            '-----------------------------------------^--- Syntax error: ';' expected
            Dim stDataSource As String = openFile.FileName
            dataSourceFile = openFile.FileName
            'remove any dunamically created controls from the form
            removeMadeControls()

            Try
                ' Attempt to fill the temporary dataset.
                ' Turn off constraint checking before the dataset is ed.
                ' This allows the adapters to fill the dataset without concern
                ' for dependencies between the tables.
                accessDataSet = New DataSet()
                accessDataSet.EnforceConstraints = False

                CType(Me.accessDataSet, System.ComponentModel.ISupportInitialize).BeginInit()

                Try
                    ' Open the connection.
                    accessConnection.Open()

                    'Get how many tables this datafile has
                    Dim schemaTable As DataTable = accessConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

                    'Update the comboBox to have all
                    'the tables in the database
                    'and keep the first table in the database
                    'as the combobox's text
                    comboTables.Items.Clear()

                    Dim i As Integer = 0

                    Dim r As DataRow
                    For Each r In schemaTable.Rows
                        If i = 0 Then
                            comboTables.Text = r("TABLE_NAME").ToString()
                            comboTables.Items.Add(r("TABLE_NAME").ToString())
                        Else
                            comboTables.Items.Add(r("TABLE_NAME").ToString())
                        End If
                        i += 1
                    Next r

                    'load data
                    ComboBoxText = comboTables.Text
                    If menuSearch.Enabled = True Then
                        removeMadeControls()
                    End If 'Call the LoadData Routine!!
                    LoadData(("Select * From [" + comboTables.Text + "]"))
                Catch fillException As System.Data.OleDb.OleDbException
                    'report error incase of failure in loading data
                    MessageBox.Show(fillException.Message)
                Finally
                    ' Turn constraint checking back on.
                    accessDataSet.EnforceConstraints = True
                    ' Close the connection whether or not the exception was thrown.
                    accessConnection.Close()
                End Try
            Catch eFillDataSet As System.Data.OleDb.OleDbException
                Throw eFillDataSet
            End Try
            'Return the cursor and form's caption to their normal state
            Me.Cursor = Cursors.Arrow
            Me.Text = "DataEasy"
        End Sub 'menuOpenClick

#End Region

#Region "Menu Search Click"
        'Search Menu is clicked
        Private Sub menuSearchClick()

            'Create a new instance of the search form
            'Specifying the datasource and table to view
            Dim sfrm = New searchfrm(Me, dataSourceFile, "[" + comboTables.Text + "]", "Select * From [" + comboTables.Text + " ]")
            ' Create a new dataset to hold the changes that have been made to the main dataset.
            Dim objDataSetChanges As New DataSet()
            ' Stop any current edits.
            Me.BindingContext(accessDataSet, comboTables.Text).EndCurrentEdit()
            ' Get the changes that have been made to the main dataset.
            objDataSetChanges = CType(accessDataSet.GetChanges(), DataSet)
            ' Check to see if any changes have been made.
            If Not (objDataSetChanges Is Nothing) Then
                'alert the user that in order to view the same data
                'in the search he/she has to save the file
                Dim r As DialogResult = MessageBox.Show("The database file changed, in oder to see your changes in the search form you have to save, continue any way?", "Change In Data File Detected", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)
                If r = DialogResult.No Then
                    Return
                End If
            End If
            Form.ActiveForm.Enabled = False
            sfrm.Show()
        End Sub 'menuSearchClick

#End Region

#Region "LoadData"
        'Here data is read from the database
        'and all the parameters are set
        'Select string is the SQL command required 
        'to view the data
        Public Sub LoadData(ByVal SelectString As String)
            Me.Cursor = Cursors.WaitCursor
            Me.Text = "DataEasy: Loading Data Please Wait..."
            Try
                accessDataSet = New DataSet()

                Dim accessDataTable As DataTable
                'create new instances for select, insert and update
                'and delete commands to be used with the adapter
                Dim accessSelectCommand As New OleDbCommand()
                Dim accessInsertCommand As New OleDbCommand()
                Dim accessUpdateCommand As New OleDbCommand()
                Dim accessDeleteCommand As New OleDbCommand()

                accessDataAdapter = Nothing
                accessDataAdapter = New OleDbDataAdapter()

                accessSelectCommand.CommandText = SelectString
                accessSelectCommand.Connection = accessConnection
                accessDataAdapter.SelectCommand = accessSelectCommand

                ' Attempt to fill the dataset through the accessDataAdapter
                accessDataAdapter.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", comboTables.Text)})
                'populate the DataSet with existing constraints information from a data source
                accessDataAdapter.FillSchema(accessDataSet, SchemaType.Source, comboTables.Text)

                ' Fill the dataser
                accessDataAdapter.Fill(accessDataSet)

                'create an instance for a datatable
                accessDataTable = accessDataSet.Tables(comboTables.Text)

                ' Make dynamic Insert Commands and Parameters
                accessInsertCommand.Connection = accessConnection
                make_Insert_Command(accessDataTable, accessInsertCommand)
                accessDataAdapter.InsertCommand = accessInsertCommand

                ' The dynamic Update Commands and Parameters
                accessUpdateCommand.Connection = accessConnection
                make_Update_Command(accessDataTable, accessUpdateCommand)
                accessDataAdapter.UpdateCommand = accessUpdateCommand

                ' The dynamic Delete Commands and Parameters
                accessDeleteCommand.Connection = accessConnection
                make_Delete_Command(accessDataTable, accessDeleteCommand)
                accessDataAdapter.DeleteCommand = accessDeleteCommand

                ' Dynamic Controls Postions
                Dim controlTop As Integer = 10
                Dim controlLeft As Integer = 10

                'Get all the System.DataTypes of all the
                'columns in the table and assign them to the
                'array colType
                colType = New String(accessDataTable.Columns.Count) {}

                'Here AutoMenu is created which would allow the user
                'to insert automatic incrementation of numbers (+1 on the
                'last cell) or insert today's date for datetime type
                'columns
                AutoMenu = New ContextMenu(accessDataTable.Columns.Count) {}

                'Create dynamically all the textboxes and labels
                'which will hold and link information to the database
                'making it easier to input data
                Dim i As Integer
                For i = 0 To accessDataTable.Columns.Count - 1
                    colType(i) = accessDataTable.Columns(i).DataType.ToString()

                    'Create the control (Label)
                    Dim LabelControl As Label = CType(CreateControls.MakeControl("Label", 30, 100, controlLeft, controlTop + 3, accessDataTable.Columns(i).Caption + " :", "cLabel" + i.ToString()), Label)