Results 1 to 31 of 31

Thread: [RESOLVED] Binding Underlying Controls of UserControls to Database

  1. #1

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Resolved [RESOLVED] Binding Underlying Controls of UserControls to Database

    Hi, Consider you already filled a data grid view with a table of database file and you want to changeover from conventional "Tabular view" to sort of "Cellular view" which is a table layout panel with UserControls programmatically added in its cells.

    This UserControl has TextBoxes, ProgressBars, Labels and so on next to a Public property called UniqueID to pass which row it supposed to show. Formerly CellValueChanged event with a bulky parallel for did the matter but the goal is to achieve a bidirectional relation meaning that changing a value on cellular view will also apply on data grid view (and dataset/adapter update later) right before new data comes (Causing your new data to discard and revert to original DGV given value).

    What I tried: Adding a BindingNavigator to UserControl and link it to database won't do anything. Its records are always zero and linking it to corresponding table databinding on main form forces all UserControls to follow only one record.

    Is there a way to achieve such desire?
    Last edited by pourkascheff; Jul 8th, 2024 at 03:27 PM.

  2. #2

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding inner controls of UserControls to Database

    OK, It seemed to be a problem with my expression. Consider following example. DGV works fine, inserting, removing, updating database works just fine codes were done. Row selection is out of concerns of this project. It supposed to be a small monitoring software. The goal is "Cellular" view based on a "Tabular" which you are witnessing on demo app to be linked together. Meaning that except ID NumericUpDown (Which I gave them) are visually OK but "Name", "Reading" and "Bool" controls were expected to follow corresponded row of table. Also any changes on either table or UserControls should apply on another. How can I achieve this? Any clues?
    Attached Images Attached Images  

  3. #3
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    You bind your datasource to the grid, then use DataBindings, i.e.

    Code:
    TextBox1.DataBindings.Add("Text", [same datasource as dgv], "fieldName")

  4. #4
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    For a CheckBox…

    Code:
    CheckBox1.DataBindings.Add("Checked", [same datasource as dgv], "fieldName")

  5. #5

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding Underlying Controls of UserControls to Database

    Hey paul, thanks for your eXtreme support. I tried your suggestion in my UserControl_load event and refer desired controls to follow dgv in main form as shown below:
    Code:
    Public Class UserControl1
        Public Property MyType As Integer = 0
        Public Property MyAddress As Integer = 0
        Private Sub UserControl1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            If MyType = 0 Then
                Me.Enabled = False
            End If
    
            SpinEdit1.DataBindings.Add("EditValue", Form1.MainTableBindingSource, "ID")
            LabelControl1.DataBindings.Add("Text", Form1.MainTableBindingSource, "TankName")
            SpinEdit2.DataBindings.Add("EditValue", Form1.MainTableBindingSource, "Field1")
            CheckEdit1.DataBindings.Add("Checked", Form1.MainTableBindingSource, "Ullage")
    
            SpinEdit1.Value = MyAddress 'Which Instrument to follow?
        End Sub
    End Class
    Note that no DatabaseDataSets, MainTableBindingSources and MainTableTableAdapters were created automatically on "bottom controls box" of visual design environment of UC since I didn't bind the old property grid way.

    Results are not OK and there are 2 problems:
    1) All UserControls grid in Form1 show only row1 or any other rows of dgv that I clicked on in run-time. The goal is one UC per each row.
    2) Updating dgv cell values will not applied on UCs and vice versa manipulating UC won't effect dgv cell values UNTIL I lose focus on each row for both mentioned direction (I guess some AcceptChanges/Update/Refresh thing is required here)

    Didn't consider binding world is this much tricky. Any instruction manuals or yt videos are also welcome here. peace/.

  6. #6
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    It's almost definitely a matter of timing. After setting up your Form1.MainTableBindingSource, or in your Form1_Shown event if you don't do that in code...

    Code:
    yourUC.Bind(yourBindingSource)
    Code:
    Public Class UserControl1
        Public Property MyType As Integer = 0
        Public Property MyAddress As Integer = 0
        Private Sub UserControl1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            If MyType = 0 Then
                Me.Enabled = False
            End If
            
        End Sub
    
        Public Sub Bind(ByVal bs As BindingSource)
            SpinEdit1.DataBindings.Add("EditValue", bs, "ID")
            LabelControl1.DataBindings.Add("Text", bs, "TankName")
            SpinEdit2.DataBindings.Add("EditValue", bs, "Field1")
            CheckEdit1.DataBindings.Add("Checked", bs, "Ullage")
    
            SpinEdit1.Value = MyAddress '??? Not sure why you're trying to modify here?
        End Sub
    
    End Class

  7. #7
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    You need to do that for each UC, and it should work

  8. #8
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    What am i thinking? You only need the one UC. DataBinding works by changing the UC values when you change the row in the DGV. Is that NOT what you want?

  9. #9
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    Code:
    Public Class UserControl1
        Public Property MyType As Integer = 0
        Public Property MyAddress As Integer = 0
    
        Private  dtSource As DataTable
        Private row As Integer
    
        Private Sub UserControl1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            If MyType = 0 Then
                Me.Enabled = False
            End If        
        End Sub
    
        Public Sub Bind(ByVal dt As DataTable, ByVal row As Integer)
            Me.dtSource  = dt
            Me.row = row
            AddHandler dt.ColumnChanged, AddressOf Column_Changed
            SpinEdit1.Value = dtSource.Rows(Me.row).Columns(0).Value
                LabelControl1.Text = dtSource.Rows(Me.row).Columns(1).Value
                SpinEdit2.Value = dtSource.Rows(Me.row).Columns(2).Value
                CheckEdit1.Checked = dtSource.Rows(Me.row).Columns(3).Value
                'If you want control edits to change your grid, you'll have to handle the control events and set dtSource manually
        End Sub
    
        Private Sub Column_Changed(sender As Object, e As DataColumnChangeEventArgs)
            If e.Row = Me.row Then
                SpinEdit1.Value = dtSource.Rows(Me.row).Columns(0).Value
                LabelControl1.Text = dtSource.Rows(Me.row).Columns(1).Value
                SpinEdit2.Value = dtSource.Rows(Me.row).Columns(2).Value
                CheckEdit1.Checked = dtSource.Rows(Me.row).Columns(3).Value
            End If
        End Sub
    
    End Class
    Last edited by .paul.; Jul 10th, 2024 at 04:48 PM.

  10. #10

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding Underlying Controls of UserControls to Database

    Quote Originally Posted by .paul. View Post
    Code:
    yourUC.Bind(yourBindingSource)
    Functioning is always a good idea. I replaced your snippet with the code below, since various type of databases were planned to feed to this windows form application and number of UserControls (corresponding to rows of main table) are indetermined.
    Code:
            For Each Ctrl As Control In TableLayoutPanel1.Controls
                If TypeOf Ctrl Is UserControl1 Then
                    Dim UC As UserControl1 = DirectCast(Ctrl, UserControl1)
                    UC.MyBind(Me.MainTableBindingSource)
                    UC.Visible = True
                End If
            Next
    Quote Originally Posted by .paul. View Post
    Code:
    SpinEdit1.Value = MyAddress '??? Not sure why you're trying to modify here?
    It was intended to mimicking a sort of virtual dgv_row_selection thing that you mentioned it is mandatory. They only set once I will suppress duplicates it is only because UCs order might be different than rows.

    Quote Originally Posted by .paul. View Post
    DataBinding works by changing the UC values when you change the row in the DGV. Is that NOT what you want?
    I'm afraid not. Following picture might change your perspective to the problem in a better way:
    Name:  UserControlBindDemo (2).png
Views: 64
Size:  13.8 KB

  11. #11
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    Have another look at post #9

  12. #12

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding Underlying Controls of UserControls to Database

    I didn't miss it paul but since there were few errors with it I gave up rectifying them. Can you enlighten me a bit more about it? Please note that I'm still kind of new to VS, Option strict = On and also I am using DevExpress controls they may be different in some extent.

    10 Errors in total and are included next to their lines by comment:
    Code:
        Public Sub Bind(ByVal dt As DataTable, ByVal row As Integer)
            Me.dtSource = dt
            Me.ROW = row
            AddHandler dt.ColumnChanged, AddressOf Column_Changed
            SpinEdit1.Value = dtSource.Rows(Me.ROW).Columns(0).Value    'Columns is not a member of 'DataRow'
            LabelControl1.Text = dtSource.Rows(Me.ROW).Columns(1).Value 'Columns is not a member of 'DataRow'
            SpinEdit2.Value = dtSource.Rows(Me.ROW).Columns(2).Value    'Columns is not a member of 'DataRow'
            CheckEdit1.Checked = dtSource.Rows(Me.ROW).Columns(3).Value 'Columns is not a member of 'DataRow'
        End Sub
        Private Sub Column_Changed(sender As Object, e As DataColumnChangeEventArgs)
            If e.Row = Me.ROW Then 'Operator '=' is not defined for types 'DataRow' and 'Integer'
                SpinEdit1.Value = dtSource.Rows(Me.ROW).Columns(0).Value    'Columns is not a member of 'DataRow'    
                LabelControl1.Text = dtSource.Rows(Me.ROW).Columns(1).Value 'Columns is not a member of 'DataRow'
                SpinEdit2.Value = dtSource.Rows(Me.ROW).Columns(2).Value    'Columns is not a member of 'DataRow'
                CheckEdit1.Checked = dtSource.Rows(Me.ROW).Columns(3).Value 'Columns is not a member of 'DataRow'
            End If
        End Sub
        Private Sub CheckEdit1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckEdit1.CheckedChanged
            dtSource.Rows(Me.ROW).Columns(3).Value = CheckEdit1.Checked 'Is it what you're talking about to achieve bidirectional updatable binding?
            'dtSource.AcceptChanges() 'ACCEPT/UPDATE/REFRESH
        End Sub

  13. #13
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    Sorry, I wasn’t at my computer when I wrote that…

    You can either use…

    Code:
    Rows(index).Item("ColumnName").Value
    Or…

    Code:
    Rows(index).Item(index).Value
    But again, I haven’t tested this…

  14. #14
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    I just noticed. e.row isn’t an integer. You can remove the if statement there, just run the code without the If…

    Code:
    If e.Row = Me.ROW Then

  15. #15

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding Underlying Controls of UserControls to Database

    Quote Originally Posted by .paul. View Post
    I haven’t tested this…
    Well, I do.

    Good news:
    1) Each UserControls now show a row. I edited the local bind function to the following.
    Code:
    Public Class UserControl1
        Public Property MyType As Integer = 0
        Public Property MyAddress As Integer = 0 '<-- That's why I declared it from the beginning of the thread
    
        Private dtSource As DataTable = Form1.Database1DataSet.MainTable
        Private ROW As Integer
    
        Private Sub UserControl1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            If MyType = 0 Then
                Me.Enabled = False
            End If
            Me.ROW = MyAddress + 1 '<-- Get a role
        End Sub
    Public Sub MyBind(ByVal dt As DataTable) ', ByVal row As Integer)
            Me.dtSource = dt
            Me.ROW = MyAddress '<-- That's the public variable since this thread created
            AddHandler dt.ColumnChanged, AddressOf Column_Changed
            SpinEdit1.Value = CDec(dtSource.Rows(Me.ROW).Item("ID"))
            LabelControl1.Text = CStr(dtSource.Rows(Me.ROW).Item("TankName"))
            SpinEdit2.Value = CDec(dtSource.Rows(Me.ROW).Item("Field1"))
            CheckEdit1.Checked = CBool(dtSource.Rows(Me.ROW).Item("Ullage"))
        End Sub
        Private Sub Column_Changed(sender As Object, e As DataColumnChangeEventArgs)
            'If e.Row = Me.ROW Then
            SpinEdit1.Value = CDec(dtSource.Rows(Me.ROW).Item("ID"))
            LabelControl1.Text = CStr(dtSource.Rows(Me.ROW).Item("TankName"))
            SpinEdit2.Value = CDec(dtSource.Rows(Me.ROW).Item("Field1"))
            CheckEdit1.Checked = CBool(dtSource.Rows(Me.ROW).Item("Ullage"))
            'End If
        End Sub
        Private Sub CheckEdit1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckEdit1.CheckedChanged
            'dtSource.Rows(Me.ROW).Columns(3).Value = CheckEdit1.Checked 'Is that what you're talking about to achieve bidirectional updatable binding?
            'dtSource.AcceptChanges() 'ACCEPT/UPDATE/REFRESH
        End Sub
    End Class
    Bad news:
    1) This whole operation is "Row" depended. Meaning that sorting the table will ruin the order or arrangement of UserControls in desktop workspace. Is there a way to avoid that? Each UC follows a uniqueID PrimaryKey matched with its address variable?
    2) "Column_Changed" event would never be raised. I used debugging buttons to perform refresh/update/accept stuffs but it seems once they've been created we're not able to manipulate it. Is it in contrast of binding nature?
    3) I'm not sure what to write in controls value_changed events so that update the table bidirectional.

    [Edit]
    2) "Column_Changed" happens. Manual changes on DGV will not be validated (Error text: There is no rows at position 5) (found it with commenting)
    Last edited by pourkascheff; Jul 15th, 2024 at 02:07 AM.

  16. #16
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    3) Yes
    2) You're ok with that now?
    1) How are you sorting your DGV?

  17. #17

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding Underlying Controls of UserControls to Database

    you're trolling, aye?

    DevExpress GridControl does fancy stuffs.

    ChatGPT always say things about gets and sets same as Microsoft says here.
    Code:
    Public Class LookupBox
        Public Property DataSource() As Object
            Get
                Return ComboBox1.DataSource
            End Get
            Set(ByVal value As Object)
                ComboBox1.DataSource = value
            End Set
        End Property
    End Class
    Should I consider changeover to another strategy and implementation?

    wish England wins UEFA 2024 by the way. The final is being held.
    Last edited by pourkascheff; Jul 14th, 2024 at 03:18 PM.

  18. #18
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    Seriously, to change the uc layout on sort, it depends how you're sorting. If it's a built in sorting on your grid, i'm not sure how to rearrange your uc's

  19. #19
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    Don't forget Rows indices start at zero

  20. #20
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    You didn’t mention this is WPF???

    You can handle the GridControl.CustomColumnSort event, and edit your custom controls properties, then reposition them as necessary…

    https://docs.devexpress.com/WPF/DevE...tomColumnSort#
    Last edited by .paul.; Jul 15th, 2024 at 04:53 AM.

  21. #21

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding Underlying Controls of UserControls to Database

    Nah mate, this is a WinForm project. If it was a WPF I'd rather to ask in relevant forum.

  22. #22
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    Quote Originally Posted by pourkascheff View Post
    Nah mate, this is a WinForm project. If it was a WPF I'd rather to ask in relevant forum.
    It was the grid control that made me think it was WPF. Look through the events, see if you can find an after sorted event, then rearrange your user controls as I suggested

  23. #23

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding Underlying Controls of UserControls to Database

    Quote Originally Posted by .paul. View Post
    it depends how you're sorting.
    I need to tell you something. I only guessed the sort thing to be honest. At least assigning bindings later on a button_click event (Right after I sort ascending/descending by manually clicking on column headers randomly) It has no effect on the results and works fine. So, my bad.

    I'm not familiar with WPF but wish it was WPF based since bindings are much accessible in a proper way (look at the two-way feature).
    Name:  WPFBind.jpg
Views: 41
Size:  34.0 KB

    As a conclusion, we are able to A) give each UserControls a role to play as their "row" say. Also B) run-time updating cell values are now updating on equivalent UserControl control by code below:
    Code:
    'FORM1 CODE
    
    Private Sub GridView1_CellValueChanged(sender As Object, e As DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs) Handles GridView1.CellValueChanged
            'SPECIFIC VALUE CHANGE TO TAKE ACTION
            If e.Column Is GridView1.Columns("Field1") Then 'Reading Updated
                If CBool(GridView1.GetRowCellValue(e.RowHandle, "Ullage")) = False Then 'Automatic
                    GridView1.SetRowCellValue(e.RowHandle, "Calculated", CUShort(e.Value) * 2) 'Calculations
                    'GridControl1.RefreshDataSource()
                    'GridControl1.Update()
                    'GridControl1.Refresh()
                    'GridView1.RefreshData()
                Else
                    'Manual
                End If
            Else
                'Other cells
            End If
    
            'COMMIT CHANGES
            Database1DataSet.MainTable.AcceptChanges()
            MainTableTableAdapter.Update(Database1DataSet.MainTable)
    
            'OVERWRITE DATABASE
            Using dbCon As New OleDbConnection(My.Settings.Database1ConnectionString)
                dbCon.Open()
                'Database1DataSet.AcceptChanges()
                Dim changedRow As DataRow = GridView1.GetDataRow(e.RowHandle)
                Dim sql As String = "UPDATE MainTable SET [TankName] = @Value1, [Field1] = @Value2, [Calculated] = @Value3, [Ullage] = @Value4 WHERE ID = @PrimaryKeyValue" 'Construct the SQL UPDATE statement
                Dim cmd As OleDbCommand = New OleDbCommand(sql, dbCon)
                cmd.Parameters.AddWithValue("@Value1", changedRow("TankName"))
                cmd.Parameters.AddWithValue("@Value2", changedRow("Field1"))
                cmd.Parameters.AddWithValue("@Value3", changedRow("Calculated"))
                cmd.Parameters.AddWithValue("@Value4", changedRow("Ullage"))
                cmd.Parameters.AddWithValue("@PrimaryKeyValue", changedRow("ID"))
                cmd.ExecuteNonQuery() 'Execute the SQL statement to update the database
                dbCon.Close()
            End Using
    
            'UPDATE VISUALS
            MainTableTableAdapter.Fill(Database1DataSet.MainTable)
            GridControl1.DataSource = Database1DataSet.MainTable
            GridView1.RefreshData()
    
            'RE-ASSIGN BINDINGS FOR ALL
            Try
                For Each Ctrl As Control In TableLayoutPanel1.Controls
                    If TypeOf Ctrl Is UserControl1 Then
                        Dim UC As UserControl1 = DirectCast(Ctrl, UserControl1)
                        UC.MyBind(Me.Database1DataSet.MainTable)
                    End If
                Next
            Catch ex As Exception
            End Try
    End Sub
    The problem is this code is visibly and tangibly SLOW! Different phases (sectioned by comments) are necessary to be present but is there a way to reduce overall load and fix it? It is possible some commands are repetitive or useless-ineffective.

    Finally C) The opposite direction of this scenario, meaning that updating a control of UserControl will not be written in DGV. In spite of existing of following code. What are the deficiencies?
    Code:
    'USERCONTROL CODE
    
    Private Sub CheckEdit1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckEdit1.CheckedChanged
            Form1.GridView1.SetRowCellValue(Me.ROW, "ULLAGE", CBool(CheckEdit1.Checked))
    End Sub
    'Similar for other controls
    Last edited by pourkascheff; Jul 16th, 2024 at 03:24 AM.

  24. #24
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    I’d directly edit the datatable, then refresh the grid

  25. #25

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding Underlying Controls of UserControls to Database

    Paragraph C rectified as follows:
    EditValueChanged event is used instead of CheckedChanged, also tested for other types of controls.
    Code:
    'USERCONTROL CODE
    
    Public Class UserControl1
        Public Property MyType As Integer = 0
        Public Property MyAddress As Integer = 0
    
        Private dtSource As DataTable
        Private ROW As Integer
    
        Private Sub UserControl1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            If MyType = 0 Then
                Me.Enabled = False
            End If
        End Sub
    
        Public Sub MyBind(ByVal dt As DataTable) ', ByVal row As Integer)
            Me.dtSource = dt
            Me.ROW = MyAddress - 1 '<-- Get a Role, That's the public variable since this thread created
    
            'AddHandler dt.ColumnChanged, AddressOf Column_Changed <-- Not sure why do we used it
    
            SpinEdit1.Value = CDec(dtSource.Rows(Me.ROW).Item("ID"))
            LabelControl1.Text = CStr(dtSource.Rows(Me.ROW).Item("TankName"))
            SpinEdit2.Value = CDec(dtSource.Rows(Me.ROW).Item("Field1"))
            CheckEdit1.Checked = CBool(dtSource.Rows(Me.ROW).Item("Ullage"))
        End Sub
    
        Private Sub LabelControl1_EditValueChanged(sender As Object, e As EventArgs) Handles LabelControl1.EditValueChanged
            Form1.GridView1.SetRowCellValue(Me.ROW, "TankName", CStr(LabelControl1.Text))
        End Sub
    
        Private Sub SpinEdit2_EditValueChanged(sender As Object, e As EventArgs) Handles SpinEdit2.EditValueChanged
            Form1.GridView1.SetRowCellValue(Me.ROW, "Field1", CDec(SpinEdit2.Value))
        End Sub
    
        Private Sub CheckEdit1_EditValueChanged(sender As Object, e As EventArgs) Handles CheckEdit1.EditValueChanged
            Form1.GridView1.SetRowCellValue(Me.ROW, "Ullage", CBool(CheckEdit1.Checked))
        End Sub
    
    End Class
    Manual sorting randomly by clicking on header columns like before will stop binding from one way. Meaning that From DGV to UC performs correctly. But from UC to DGV does nothing. I may disable every possible ways to sort for user or using another way to "find" the row corresponding to UC Address (Paul called it Me.Row) Or MyBind argument should get row mandatorily (Same as paul first idea). I don't know...

    For being slow and short visual unresponsiveness, Order of code execution were a bit reordered, Accept/Update/Refresh lines were commented and "Re-Bind" then "Save" operations were also fed through a BackGroundWorker and results are relatively better than before. Please correct me If there's something wrong with the approach or codes. Thanks:
    Code:
        Private Sub GridView1_CellValueChanged(sender As Object, e As DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs) Handles GridView1.CellValueChanged
            'CALCULATIONS BEFORE TAKE ACTION
            'COMMIT CHANGES (ALL COMMENTED)
            If Not BackgroundWorker1.IsBusy Then
                RowHandleObj = e.RowHandle 'SINCE e IS LOCAL ONLY IN THIS SUB
                BackgroundWorker1.RunWorkerAsync()
            End If
        End Sub
    
        Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
            For Each Ctrl As Control In TableLayoutPanel1.Controls
                If TypeOf Ctrl Is UserControl1 Then
                    Dim UC As UserControl1 = DirectCast(Ctrl, UserControl1)
                    UC.MyBind(Me.Database1DataSet.MainTable)
                End If
            Next
            Using dbCon As New OleDbConnection(My.Settings.Database1ConnectionString)
                dbCon.Open()
                Dim changedRow As DataRow = GridView1.GetDataRow(RowHandleObj)
                Dim sql As String = "UPDATE MainTable SET [TankName] = @Value1, [Field1] = @Value2, [Calculated] = @Value3, [Ullage] = @Value4 WHERE ID = @PrimaryKeyValue"
                Dim cmd As OleDbCommand = New OleDbCommand(sql, dbCon)
                cmd.Parameters.AddWithValue("@Value1", changedRow("TankName"))
                cmd.Parameters.AddWithValue("@Value2", changedRow("Field1"))
                cmd.Parameters.AddWithValue("@Value3", changedRow("Calculated"))
                cmd.Parameters.AddWithValue("@Value4", changedRow("Ullage"))
                cmd.Parameters.AddWithValue("@PrimaryKeyValue", changedRow("ID"))
                cmd.ExecuteNonQuery()
                dbCon.Close()
            End Using
        End Sub
    So, dear reader - who you also might have same concerns like I do - we mimicked a bidirectional binding for now.

  26. #26
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    The point of the row variable (Me.row) is to keep track of which row in the datatable that this data comes from. If you directly edit the datatable, then Refresh the grid, your data should change. The important part is keeping track of which datatable row to edit...

  27. #27

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding Underlying Controls of UserControls to Database

    Quote Originally Posted by .paul. View Post
    I’d directly edit the datatable, then refresh the grid
    How would you do it? Sorry for asking, I'm a newbie...

    Quote Originally Posted by .paul. View Post
    If you directly edit the datatable, then Refresh the grid,
    You keep saying Datatable, so it's a clue. Am I working with something else possibly wrong "The hardest way" as some say? And what do you mean by "directly"?

  28. #28
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,621

    Re: Binding Underlying Controls of UserControls to Database

    The datatable is passed in... no? MyBind(ByVal dt As DataTable) ... so edit dt instead of the grid: Form1.GridView1.SetRowCellValue(Me.ROW, "Ullage", CBool(CheckEdit1.Checked))


    Personally I would have thought passing in a DataBindingSource would be better... that's what it's for... binding a single source to multiple controls and maintaining the datastate.

    Althought looking back at it, I probably would have designed the UC a bit differently ...


    and who knows what's been going on... I've had this window open for.... some time... :P


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  29. #29

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding Underlying Controls of UserControls to Database

    wow, my english skills are being challanged here tg, I must say.

    Quote Originally Posted by techgnome View Post
    The datatable is passed in... no? MyBind(ByVal dt As DataTable) ... so edit dt instead of the grid
    Yes it does. I knew there was something wrong with my approach. I would be appreciated if you take time and give a code example. Which part was your point? A or B? (See code below) DataTable (tb) is inaccessible from another control sub since it is a local variable inside of MyBind so only dtSource remains and a proper method to update the mother table (DGV on Form1 which is an imaginary thing of a DataTable as you claimed.) which I'm suspected to ImportRow (not sure, just read its tooltip and found it relevant) otherwise there are .Rows, .LoadDataRow and .NewRow but the red part should be a DataRow data type instead of an integer.

    Code:
        Public Sub MyBind(ByVal dt As DataTable)
            Me.dtSource = dt
            Me.ROW = MyAddress - 1 '<-- Get a Role
            SomeControl.Value = CDec(dtSource.Rows(Me.ROW).Item("SomeTableColumn"))'<-- A
        End Sub
    
        Private Sub SomeControl_EditValueChanged(sender As Object, e As EventArgs) Handles SomeControl.EditValueChanged
            dtSource.ImportRow(Me.ROW) '<-- B
        End Sub

    Quote Originally Posted by techgnome View Post
    I've had this window open for.... some time... :P
    You mean this thread? I didn't get that part.

    There is a Database development dedicated forum here on VBForums but I didn't find any of them related to such binding.

  30. #30
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,584

    Re: Binding Underlying Controls of UserControls to Database

    I already showed you how to save the datatable, and the row in the datatable that refers to the data in a particular uc. Each uc should be initiated by passing in the datatable and the zero based row index...
    Please don't keep chopping up the code you're given. That is the reason we've been going round in circles here. If you think you can improve my answer, check it's feasible before heading off in the wrong direction.

    When binding the controls...

    Code:
    For x as integer = 0 to yourDataTable.Rows.Count - 1
        youeUserControl.MyBind(yourDataTable, x)
    Next
    Code:
    Public Class UserControl1
        Public Property MyType As Integer = 0
        Public Property MyAddress As Integer = 0
    
        Private  dtSource As DataTable
        Private row As Integer
    
        Private Sub UserControl1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            If MyType = 0 Then
                Me.Enabled = False
            End If        
        End Sub
    
        Public Sub MyBind(ByVal dt As DataTable, ByVal row As Integer)
            Me.dtSource = dt
            Me.row = row
            AddHandler dt.ColumnChanged, AddressOf Column_Changed
            SomeControl.Value = CDec(dtSource.Rows(Me.ROW).Item("SomeTableColumn"))'<-- A
        End Sub
    
        Private Sub Column_Changed(sender As Object, e As DataColumnChangeEventArgs)
            SpinEdit1.Value = dtSource.Rows(Me.row).Item(0).Value
            LabelControl1.Text = dtSource.Rows(Me.row).Item(1).Value
            SpinEdit2.Value = dtSource.Rows(Me.row).Item(2).Value
            CheckEdit1.Checked = dtSource.Rows(Me.row).Item(3).Value
        End Sub
    
        Private Sub SomeControl_EditValueChanged(sender As Object, e As EventArgs) Handles SomeControl.EditValueChanged
            dtSource.Rows(Me.row).item("SomeTableColumn") = SomeControl.Value
        End Sub
    
    End Class
    Last edited by .paul.; Jul 18th, 2024 at 02:43 AM.

  31. #31

    Thread Starter
    Hyperactive Member pourkascheff's Avatar
    Join Date
    Apr 2020
    Location
    LocalHost
    Posts
    380

    Re: Binding Underlying Controls of UserControls to Database

    That was informative, it worked! Thanks.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width