locked
Add new row to a binding source -combobox text and selected value RRS feed

  • Question

  • Hi,

    I am trying to add a new row to a binding source from a combo box selection (text and selected value) below is my code but it is not working as expected. i a getting error on the following line 

    DirectCast(.Current, DataRowView)("sSize") = ComboBox1.SelectedValue

    my code

    Public Class Form1
        Dim dt_cbx_Source As New DataTable("Table1")
        Dim dt_Save As New DataTable("Save")
        Dim bs_Source As New BindingSource
        Dim bs_Save As New BindingSource
        Dim testPath As String = "Table1.xml"
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            '//populate source datatable from xml file
            With dt_cbx_Source
                .Columns.Add("Product", GetType(String))
                .Columns.Add("Size", GetType(Single))
                .ReadXml(testPath)
            End With
    
            '//create datatble to save selected items
            With dt_Save
                .Columns.Add("sProduct", GetType(String))
                .Columns.Add("sSize", GetType(Single))
            End With
    
            '//bind cb source data to dgv ..just to check values
            bs_Source.DataSource = dt_cbx_Source
            With DataGridView1
                .DataSource = bs_Source
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            End With
    
            '//populate combobox from source datatable
            With ComboBox1
                .DisplayMember = "Product"
                .ValueMember = "Size"
                .DataSource = bs_Source
            End With
    
            '//bind save dt to dgv2
            bs_Save.DataSource = dt_Save
            With DataGridView2
                .DataSource = bs_Save
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            End With
    
            ComboBox1.DataBindings.Add("Text", bs_Save, "sProduct", True, DataSourceUpdateMode.OnPropertyChanged)
            ComboBox1.DataBindings.Add("SelectedValue", bs_Save, "sSize", True, DataSourceUpdateMode.OnPropertyChanged)
    
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
            With bs_Save
                .AddNew()
                DirectCast(.Current, DataRowView)("sProduct") = ComboBox1.Text
                DirectCast(.Current, DataRowView)("sSize") = ComboBox1.SelectedValue
                .EndEdit()
            End With
    
        End Sub
    
    End Class
    

    What is the right way to add a new row to a binding source?

    Thanks

     
    Sunday, April 12, 2020 7:46 AM

Answers

  • Hi Shan,

    If you want select a product from DataGridView2  and display it in the Combox1 use the following code

    on the DataGridView2 events section find CellClick after double click on it write this code.
    Note : 

    DataGridView2.Rows(e.RowIndex).Cells(0).Value // getting the name of the product
    DataGridView2.Rows(e.RowIndex).Cells(1).Value // getting the size of the product

    Private Sub DataGridView2_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView2.CellClick
    
    
    
            If e.RowIndex >= 0 Then
                Dim row As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
    
    
                ComboBox1.SelectedValue = DataGridView2.Rows(e.RowIndex).Cells(1).Value
    
            End If
    
    
    
        End Sub

    Please remember to mark the replies as answers if they helped you :) ~

    • Proposed as answer by Cor Ligthert Sunday, April 12, 2020 2:06 PM
    • Marked as answer by Shan1986 Monday, April 13, 2020 10:55 AM
    Sunday, April 12, 2020 1:07 PM

All replies

  • Hi Shan,

    I have tried to test your code, just remove two lines 

      'ComboBox1.DataBindings.Add("Text", bs_Save, "sProduct", True, DataSourceUpdateMode.OnPropertyChanged)
            ' ComboBox1.DataBindings.Add("SelectedValue", bs_Save, "sSize", True, DataSourceUpdateMode.OnPropertyChanged)
    <?xml version='1.0' encoding='UTF-8'?>
    <Root>
      <Products>
        <Product>Lamb Leg - Bone - In Nz</Product>
        <Size>39</Size>
      </Products>
    
      <Products>
        <Product>Macaroons - Two Bite Choc</Product>
        <Size>95</Size>
      </Products>
    
    
      <Products>
        <Product>Bread - Dark Rye, Loaf</Product>
        <Size>14</Size>
      </Products>
    
      <Products>
        <Product>Nantucket - Kiwi Berry Cktl.</Product>
        <Size>23</Size>
      </Products>
    
      <Products>
        <Product>Cheese - Stilton</Product>
        <Size>58</Size>
      </Products>
      <Products>
        <Product>Icecream Cone - Areo Chocolate</Product>
        <Size>75</Size>
      </Products>
      <Products>
        <Product>Juice - Orange 1.89l</Product>
        <Size>28</Size>
      </Products>
      <Products>
        <Product>Langers - Cranberry Cocktail</Product>
        <Size>89</Size>
      </Products>
      <Products>
        <Product>Longos - Chicken Caeser Salad</Product>
        <Size>99</Size>
      </Products>
      <Products>
        <Product>Mushroom - Porcini Frozen</Product>
        <Size>35</Size>
      </Products>
      <Products>
        <Product>Soup - Campbells Asian Noodle</Product>
        <Size>66</Size>
      </Products>
      <Products>
        <Product>Oil - Margarine</Product>
        <Size>29</Size>
      </Products>
      <Products>
        <Product>Lemonade - Mandarin, 591 Ml</Product>
        <Size>40</Size>
      </Products>
      <Products>
        <Product>Olive - Spread Tapenade</Product>
        <Size>23</Size>
      </Products>
      <Products>
        <Product>Bread - 10 Grain</Product>
        <Size>57</Size>
      </Products>
    
        </Root>

    Public Class Form1
    
        Dim dt_cbx_Source As New DataTable("Products")
        Dim dt_Save As New DataTable("Save")
        Dim bs_Source As New BindingSource
        Dim bs_Save As New BindingSource
        Dim testPath As String = "C:\Users\blbl\Source\Repos\LineChartTest\WindowsAppbindingSource\bin\Debug\Table1.xml"
    
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            '//populate source datatable from xml file
    
    
    
    
            With dt_cbx_Source
                .Columns.Add("Product", GetType(String))
                .Columns.Add("Size", GetType(Single))
                .ReadXml(testPath)
            End With
    
    
    
    
    
    
            '//create datatble to save selected items
            With dt_Save
                .Columns.Add("sProduct", GetType(String))
                .Columns.Add("sSize", GetType(Single))
            End With
    
    
    
    
            '//bind cb source data to dgv ..just to check values
            bs_Source.DataSource = dt_cbx_Source
            With DataGridView1
                .DataSource = bs_Source
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            End With
    
    
    
    
    
    
            '//populate combobox from source datatable
            With ComboBox1
                .DisplayMember = "Product"
                .ValueMember = "Size"
                .DataSource = bs_Source
            End With
    
    
    
            '//bind save dt to dgv2
            bs_Save.DataSource = dt_Save
            With DataGridView2
                .DataSource = bs_Save
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            End With
    
    
    
    
            'ComboBox1.DataBindings.Add("Text", bs_Save, "sProduct", True, DataSourceUpdateMode.OnPropertyChanged)
            ' ComboBox1.DataBindings.Add("SelectedValue", bs_Save, "sSize", True, DataSourceUpdateMode.OnPropertyChanged)
    
    
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
            'MsgBox(ComboBox1.SelectedValue)
            ' MsgBox(ComboBox1.Text)
    
            With bs_Save
                .AddNew()
                DirectCast(.Current, DataRowView)("sProduct") = ComboBox1.Text
                DirectCast(.Current, DataRowView)("sSize") = ComboBox1.SelectedValue
                .EndEdit()
            End With
    
    
    
    
        End Sub
    End Class


    Please remember to mark the replies as answers if they helped you :) ~



    • Edited by Rebin Qadir Sunday, April 12, 2020 10:19 AM
    • Proposed as answer by Cor Ligthert Sunday, April 12, 2020 11:25 AM
    Sunday, April 12, 2020 10:12 AM
  • Hi Shan,

    I have tried to test your code, just remove two lines 

      'ComboBox1.DataBindings.Add("Text", bs_Save, "sProduct", True, DataSourceUpdateMode.OnPropertyChanged)
            ' ComboBox1.DataBindings.Add("SelectedValue", bs_Save, "sSize", True, DataSourceUpdateMode.OnPropertyChanged)
    <?xml version='1.0' encoding='UTF-8'?>
    <Root>
      <Products>
        <Product>Lamb Leg - Bone - In Nz</Product>
        <Size>39</Size>
      </Products>
    
      <Products>
        <Product>Macaroons - Two Bite Choc</Product>
        <Size>95</Size>
      </Products>
    
    
      <Products>
        <Product>Bread - Dark Rye, Loaf</Product>
        <Size>14</Size>
      </Products>
    
      <Products>
        <Product>Nantucket - Kiwi Berry Cktl.</Product>
        <Size>23</Size>
      </Products>
    
      <Products>
        <Product>Cheese - Stilton</Product>
        <Size>58</Size>
      </Products>
      <Products>
        <Product>Icecream Cone - Areo Chocolate</Product>
        <Size>75</Size>
      </Products>
      <Products>
        <Product>Juice - Orange 1.89l</Product>
        <Size>28</Size>
      </Products>
      <Products>
        <Product>Langers - Cranberry Cocktail</Product>
        <Size>89</Size>
      </Products>
      <Products>
        <Product>Longos - Chicken Caeser Salad</Product>
        <Size>99</Size>
      </Products>
      <Products>
        <Product>Mushroom - Porcini Frozen</Product>
        <Size>35</Size>
      </Products>
      <Products>
        <Product>Soup - Campbells Asian Noodle</Product>
        <Size>66</Size>
      </Products>
      <Products>
        <Product>Oil - Margarine</Product>
        <Size>29</Size>
      </Products>
      <Products>
        <Product>Lemonade - Mandarin, 591 Ml</Product>
        <Size>40</Size>
      </Products>
      <Products>
        <Product>Olive - Spread Tapenade</Product>
        <Size>23</Size>
      </Products>
      <Products>
        <Product>Bread - 10 Grain</Product>
        <Size>57</Size>
      </Products>
    
        </Root>

    Public Class Form1
    
        Dim dt_cbx_Source As New DataTable("Products")
        Dim dt_Save As New DataTable("Save")
        Dim bs_Source As New BindingSource
        Dim bs_Save As New BindingSource
        Dim testPath As String = "C:\Users\blbl\Source\Repos\LineChartTest\WindowsAppbindingSource\bin\Debug\Table1.xml"
    
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            '//populate source datatable from xml file
    
    
    
    
            With dt_cbx_Source
                .Columns.Add("Product", GetType(String))
                .Columns.Add("Size", GetType(Single))
                .ReadXml(testPath)
            End With
    
    
    
    
    
    
            '//create datatble to save selected items
            With dt_Save
                .Columns.Add("sProduct", GetType(String))
                .Columns.Add("sSize", GetType(Single))
            End With
    
    
    
    
            '//bind cb source data to dgv ..just to check values
            bs_Source.DataSource = dt_cbx_Source
            With DataGridView1
                .DataSource = bs_Source
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            End With
    
    
    
    
    
    
            '//populate combobox from source datatable
            With ComboBox1
                .DisplayMember = "Product"
                .ValueMember = "Size"
                .DataSource = bs_Source
            End With
    
    
    
            '//bind save dt to dgv2
            bs_Save.DataSource = dt_Save
            With DataGridView2
                .DataSource = bs_Save
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            End With
    
    
    
    
            'ComboBox1.DataBindings.Add("Text", bs_Save, "sProduct", True, DataSourceUpdateMode.OnPropertyChanged)
            ' ComboBox1.DataBindings.Add("SelectedValue", bs_Save, "sSize", True, DataSourceUpdateMode.OnPropertyChanged)
    
    
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
            'MsgBox(ComboBox1.SelectedValue)
            ' MsgBox(ComboBox1.Text)
    
            With bs_Save
                .AddNew()
                DirectCast(.Current, DataRowView)("sProduct") = ComboBox1.Text
                DirectCast(.Current, DataRowView)("sSize") = ComboBox1.SelectedValue
                .EndEdit()
            End With
    
    
    
    
        End Sub
    End Class


    Please remember to mark the replies as answers if they helped you :) ~



    Hi Rebin,

    Thanks for the answer but i need data bindings. if i select a product from the datagridview 2.. it should show the product in the combo box so that i can change the selection again. Any idea?

    thanks  

    Sunday, April 12, 2020 10:43 AM


  • Hi Rebin,

    Thanks for the answer but i need data bindings. if i select a product from the datagridview 2.. it should show the product in the combo box so that i can change the selection again. Any idea?

    thanks  

    Why, the combobox is a complex data control. Not a simple control as a label or a textbox. What Rebbin shows is the right way. You should not use the databindings on a complex data control. That it is in the textbox is only because it inherits all properties from control.

    Success
    Cor

    Sunday, April 12, 2020 11:31 AM
  • Hello,

    Approaching this programmatically without user actions other than clicking a button where the ComboBox data source is a DataTable and the DataGridView data source is a DataTable both set via a BindingSource use DataTable.ImportRow. In the example below I didn't do any DataBinding as 

    ComboBox1.DataBindings.Add("Text", bs_Save, "sProduct", True, DataSourceUpdateMode.OnPropertyChanged)
    ComboBox1.DataBindings.Add("SelectedValue", bs_Save, "sSize", True, DataSourceUpdateMode.OnPropertyChanged)

    Code sample

    Here I read from SQL-Server which doesn't matter as any database table is set to a DataTable in the end.

    Backend class to read data

    Imports System.Data.SqlClient
    '
    ' My personal DLL for setting connection string
    ' same with Inherits SqlServerConnection
    ' Installed via NuGet package manager as
    ' BaseConnectionLibrary
    ' but you don't need this
    Imports BaseConnectionLibrary.ConnectionClasses
    
    Public Class DataOperationsSqlServer
        Inherits SqlServerConnection
    
        Public Sub New()
            DefaultCatalog = "NorthWindAzure"
            DatabaseServer = "KARENS-PC"
        End Sub
        Public Function ReadCategories() As DataTable
            Dim table As New DataTable
            Dim selectStatement = "SELECT CategoryId, CategoryName FROM Categories"
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = selectStatement
                    cn.Open()
                    table.Load(cmd.ExecuteReader())
                End Using
            End Using
            Return table
        End Function
    End Class
    

    Form code

    Public Class Form1
        Private ReadOnly dataOperations As New DataOperationsSqlServer
        Private ReadOnly comboBoxBindingSource As New BindingSource
        Private ReadOnly dataGridViewBindingSource As New BindingSource
        Private Sub Form1_Shown(sender As Object, e As EventArgs) _
            Handles Me.Shown
    
            Dim dt = dataOperations.ReadCategories()
    
            comboBoxBindingSource.DataSource = dt
    
            CategoryComboBox.DataSource = comboBoxBindingSource
            CategoryComboBox.DisplayMember = "CategoryName"
    
            dataGridViewBindingSource.DataSource = dt.Clone
            DataGridView1.DataSource = dataGridViewBindingSource
    
        End Sub
    
        Private Sub AddButton_Click(sender As Object, e As EventArgs) _
            Handles AddButton.Click
    
            Dim comboBoxDataRow = CType(comboBoxBindingSource.Current, DataRowView).Row
            Dim dataGridViewDataTable = CType(dataGridViewBindingSource.DataSource, DataTable)
    
            Dim dataRow = dataGridViewDataTable.
                    AsEnumerable().
                    FirstOrDefault(
                    Function(row) row.Field(Of Integer)("CategoryId") =
                                  comboBoxDataRow.Field(Of Integer)("CategoryId"))
    
            If dataRow Is Nothing Then
                dataGridViewDataTable.ImportRow(comboBoxDataRow)
            End If
    
        End Sub
    End Class
    

    Note I only allow a ComboBox DataRow to be sent to the DataGridView if non-existing 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, April 12, 2020 12:06 PM
  • Hi,
    you must change your design. You have inconsistent bindings.

    1. ComboBox1.DataSource = bs_Source

    2. ComboBox1.ValueMember = "Size" -> user select data item (from bs_Source ) to SelectedValue

    3. ComboBox1.DataBindings.Add("SelectedValue", bs_Save, ... - SelectedValue is binded to other Source

    4. bs_Save.AddNew() set an empty data item -> SelectedValue sets to null/Nothing

    5. DirectCast(.Current, DataRowView)("sSize") = ComboBox1.SelectedValue -> you copy null/Nothing to binded field and get error


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Sunday, April 12, 2020 1:07 PM
  • Hi Shan,

    If you want select a product from DataGridView2  and display it in the Combox1 use the following code

    on the DataGridView2 events section find CellClick after double click on it write this code.
    Note : 

    DataGridView2.Rows(e.RowIndex).Cells(0).Value // getting the name of the product
    DataGridView2.Rows(e.RowIndex).Cells(1).Value // getting the size of the product

    Private Sub DataGridView2_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView2.CellClick
    
    
    
            If e.RowIndex >= 0 Then
                Dim row As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
    
    
                ComboBox1.SelectedValue = DataGridView2.Rows(e.RowIndex).Cells(1).Value
    
            End If
    
    
    
        End Sub

    Please remember to mark the replies as answers if they helped you :) ~

    • Proposed as answer by Cor Ligthert Sunday, April 12, 2020 2:06 PM
    • Marked as answer by Shan1986 Monday, April 13, 2020 10:55 AM
    Sunday, April 12, 2020 1:07 PM
  • Hi Rebin,

    Very nice done. I was searching for the same solution, but yours is much easier than I was trying (using currency manager). 

    This one is nice, together with your previous answer it fits in my perception perfect. 


    Success
    Cor

    Sunday, April 12, 2020 2:06 PM
  • Hi Cor Ligthert,

    Thanks, Currency Manager is a great choice. 


    Please remember to mark the replies as answers if they helped you :) ~

    Sunday, April 12, 2020 5:41 PM