mnfrm.vb

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

                    'Create the control (TextBox)
                    Dim TextControl As TextBox = CType(CreateControls.MakeControl("TextBox", 30, 100, controlLeft + 100, controlTop, accessDataTable.Columns(i).Caption, "cText" + i.ToString()), TextBox)

                    'Assign the TabIndex sequentially to the created
                    'textbox control
                    TextControl.TabIndex = i

                    'Bind the textbox control to the database table column
                    TextControl.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.accessDataSet, comboTables.Text + "." + accessDataSet.Tables(comboTables.Text).Columns(i).ToString()))

                    'Finally add the controls to the form
                    Me.Controls.Add(TextControl)
                    Me.Controls.Add(LabelControl)

                    'Here we arrange the controls on the form
                    If i = accessDataTable.Columns.Count - 1 Then
                        Exit For
                    End If
                    If controlLeft = 10 Then
                        controlLeft = 220
                    Else
                        If controlLeft = 220 And accessDataTable.Columns.Count > 6 Then
                            controlLeft = 430
                            Me.Width = 680
                        Else
                            controlTop += LabelControl.Height + 10
                            controlLeft = 10
                        End If
                    End If
                Next i
                arrange_Controls(controlTop, accessDataTable.Columns.Count)

                'Bind the datagrid control to the database table
                dGrid.SetDataBinding(accessDataSet, comboTables.Text)


                'Create AutoInsertion of Date for only 'DateTime' Type
                'columns and
                'AutoNumber to only 'Integer' type columns

                For i = 0 To accessDataTable.Columns.Count - 1
                    If colType(i) = "System.Int32" Then
                        Dim Item1 As New MenuItem("Automatic Incrementation")
                        AddHandler item1.Click, AddressOf Me.cMenuClick
                        AutoMenu(i) = New ContextMenu(New MenuItem() {Item1})
                    Else
                        If colType(i) = "System.DateTime" Then
                            Dim Item1 As New MenuItem("Automatic Insertion of Today's Date")
                            AddHandler item1.Click, AddressOf Me.cMenuClick
                            AutoMenu(i) = New ContextMenu(New MenuItem() {Item1})
                        End If
                    End If
                Next i
                'refresh the database view in the grid
                DataSet_PositionChanged()
                ' catch any errors and display them
            Catch e As System.Data.OleDb.OleDbException
                MessageBox.Show(e.Message)
            End Try
            Me.Cursor = Cursors.Arrow
            Me.Text = "DataEasy"
        End Sub 'LoadData

#End Region

#Region "make_Insert_Command"
        'Here the command string and the parameters are
        'assigned dynamically for the insert command
        Private Sub make_Insert_Command(ByVal datatable As DataTable, ByVal insertcommand As OleDbCommand)
            Dim insertString1 As String = "INSERT INTO [" + comboTables.Text + "] ("
            Dim insertString2 As String = ""

            Dim i As Integer
            For i = 0 To datatable.Columns.Count - 1
                insertString1 += "[" + datatable.Columns(i).Caption + "]"
                insertString2 += "?"

                If i <> datatable.Columns.Count - 1 Then
                    insertString1 += ","
                    insertString2 += ","
                Else
                    insertString1 += ") VALUES (" + insertString2 + ")"
                End If
                insertcommand.Parameters.Add(New System.Data.OleDb.OleDbParameter(datatable.Columns(i).Caption, System.Data.OleDb.OleDbType.Variant, 16, datatable.Columns(i).Caption))
            Next i
            insertcommand.CommandText = insertString1
        End Sub 'make_Insert_Command

#End Region

#Region "make_Update_Command"
        'Here the command string and the parameters are
        'assigned for the update command
        Private Sub make_Update_Command(ByVal datatable As DataTable, ByVal updatecommand As OleDbCommand)
            Dim updateString1 As String = "UPDATE [" + comboTables.Text + "] SET "
            Dim updateString2 As String = ""

            Dim i As Integer
            For i = 0 To datatable.Columns.Count - 1
                If datatable.Columns(i).AutoIncrement <> True Then
                    updateString1 += "[" + datatable.Columns(i).Caption + "]  = ? "
                    If i <> datatable.Columns.Count - 1 Then
                        updateString1 += " , "
                    End If
                    Dim colSQLType As OleDbType = Return_OleDBType(datatable.Columns(i).DataType.ToString())
                    updatecommand.Parameters.Add(New OleDbParameter(datatable.Columns(i).Caption, colSQLType, 0, datatable.Columns(i).Caption))
                End If

                updateString2 += "([" + datatable.Columns(i).Caption + "] = ? OR ? IS NULL AND [" + datatable.Columns(i).Caption + "] IS NULL)"

                If i <> datatable.Columns.Count - 1 Then
                    updateString2 += " AND "

                Else
                    updateString1 += " WHERE (" + updateString2 + ")"
                End If
            Next i
            For i = 0 To datatable.Columns.Count - 1
                Dim colSQLType As OleDbType = Return_OleDBType(datatable.Columns(i).DataType.ToString())
                updatecommand.Parameters.Add(New OleDbParameter("Original_" + datatable.Columns(i).Caption, colSQLType, 0, System.Data.ParameterDirection.Input, False, CType(10, System.Byte), CType(10, System.Byte), datatable.Columns(i).Caption, System.Data.DataRowVersion.Original, Nothing))
                updatecommand.Parameters.Add(New OleDbParameter("Original_" + datatable.Columns(i).Caption + "1", colSQLType, 0, System.Data.ParameterDirection.Input, False, CType(10, System.Byte), CType(10, System.Byte), datatable.Columns(i).Caption, System.Data.DataRowVersion.Original, Nothing))
            Next i

            updatecommand.CommandText = updateString1
        End Sub 'make_Update_Command


        Private Function Return_OleDBType(ByVal SystemType As String) As OleDbType
            Select Case SystemType

                Case "System.Boolean"
                    Return OleDbType.Boolean
                Case "System.Byte"
                    Return OleDbType.UnsignedTinyInt
                Case "System.Byte[]"
                    Return OleDbType.VarBinary
                Case "System.Binary"
                    Return OleDbType.VarBinary
                Case "System.DateTime"
                    Return OleDbType.DBTimeStamp
                Case "System.Decimal"
                    Return OleDbType.Decimal
                Case "System.Double"
                    Return OleDbType.Double
                Case "System.Single"
                    Return OleDbType.Single
                Case "System.Guid"
                    Return OleDbType.Guid
                Case "System.Int16"
                    Return OleDbType.SmallInt
                Case "System.Int32"
                    Return OleDbType.Integer
                Case "System.Int64"
                    Return OleDbType.BigInt
                Case "System.Object"
                    Return OleDbType.Variant
                Case "System.String"
                    Return OleDbType.VarWChar
                Case "System.UInt16"
                    Return OleDbType.UnsignedSmallInt
                Case "System.UInt32"
                    Return OleDbType.UnsignedInt
                Case "System.UInt64"
                    Return OleDbType.UnsignedBigInt
                Case "System.AnsiString"
                    Return OleDbType.VarChar
                Case "System.Currency"
                    Return OleDbType.Currency
                Case "System.Date"
                    Return OleDbType.DBDate
                Case "System.SByte"
                    Return OleDbType.TinyInt
                Case "System.Time"
                    Return OleDbType.DBTime
                Case "VarNumeric"
                    Return OleDbType.VarNumeric
                Case Else
                    Return OleDbType.Variant
            End Select
        End Function 'Return_OleDBType

#End Region

#Region "make_Delete_Command"
        'Here the command string and the parameters are
        'assigned for the delete command
        Private Sub make_Delete_Command(ByVal datatable As DataTable, ByVal deletecommand As OleDbCommand)
            Dim deleteString As String = "DELETE FROM [" + comboTables.Text + "] WHERE "

            Dim i As Integer
            For i = 0 To datatable.Columns.Count - 1
                deleteString += "( [" + datatable.Columns(i).Caption + "] = ? OR ? IS NULL AND [" + datatable.Columns(i).Caption + "] IS NULL )"

                If i <> datatable.Columns.Count - 1 Then
                    deleteString += " AND "
                End If
                deletecommand.Parameters.Add(New OleDbParameter("Original_" + datatable.Columns(i).Caption, System.Data.OleDb.OleDbType.Variant, 0, System.Data.ParameterDirection.Input, False, CType(10, System.Byte), CType(10, System.Byte), datatable.Columns(i).Caption, System.Data.DataRowVersion.Original, Nothing))
                deletecommand.Parameters.Add(New OleDbParameter("Original_" + datatable.Columns(i).Caption + "1", System.Data.OleDb.OleDbType.Variant, 0, System.Data.ParameterDirection.Input, False, CType(10, System.Byte), CType(10, System.Byte), datatable.Columns(i).Caption, System.Data.DataRowVersion.Original, Nothing))
            Next i

            deletecommand.CommandText = deleteString
        End Sub 'make_Delete_Command

#End Region

#Region "Check_If_Data_Changed"
        ' Here we check if the data has changed
        Private Function Check_If_Data_Changed() As Boolean
            Try
                ' 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
                    Return True
                Else
                    Return False
                End If
            Catch
            End Try
        End Function 'Check_If_Data_Changed

#End Region

#Region "arrange_Controls"
        'Here the control positions and other control paramters are
        'set based on the data loaded
        Private Sub arrange_Controls(ByVal startingPos As Integer, ByVal TabIndex_start As Integer)
            btnAdd.TabIndex = TabIndex_start + 1
            btnCancel.TabIndex = TabIndex_start + 2
            btnDelete.TabIndex = TabIndex_start + 3
            btnUpdate.TabIndex = TabIndex_start + 5

            startingPos += 10
            comboTables.Top = startingPos + 42
            comboTables.Left = 90

            'lblcombo.Top = startingPos + 45;
            'lblcombo.Left = 10;
            btnNavNext.Top = startingPos + 40
            btnNavNext.Left = 385

            lblNavLocation.Top = startingPos + 40
            lblNavLocation.Left = 290

            btnNavPrev.Top = startingPos + 40
            btnNavPrev.Left = 255

            btnUpdate.Top = startingPos + 42 ';80;
            btnUpdate.Left = 10

            btnCancel.Top = startingPos + 80
            btnCancel.Left = 345

            btnDelete.Top = startingPos + 80
            btnDelete.Left = 270

            btnAdd.Top = startingPos + 80
            btnAdd.Left = 190