Results 1 to 19 of 19

Thread: VB 2010 - Listbox duplicating items

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2013
    Posts
    9

    VB 2010 - Listbox duplicating items

    I have a form which pulls and sends data from a database, when i search for a existing customer it populates a listbox with all records from sql. when i click on one of the items it populates the textboxs fine, however the item which is clicks gets duplicated into the text box as seen in screen shots.

    Name:  img1.png
Views: 1645
Size:  53.3 KB
    Name:  img2.png
Views: 645
Size:  55.0 KB

    Code:
     Imports System.Data.OleDb
    
    Public Class Form1
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim X As Integer ' Stores ID number for specific row
        Dim dsexisting As New DataSet 'dataset for existing customer details
        Dim dspostcode As New DataSet
    
        Private Sub send_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonCommit.Click
    
    
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow
    
            dsNewRow = ds.Tables("CustomerDetails").NewRow()
    
            dsNewRow.Item("Forename") = Forename_txtbx.Text
            dsNewRow.Item("Surname") = Surname_txtbx.Text
            dsNewRow.Item("Postcode") = Postcode_txtbx.Text
            dsNewRow.Item("Address") = StreetAddress_txtbx.Text
            dsNewRow.Item("Housenumb") = housenumb_txtbx.Text
            dsNewRow.Item("DeliveryTime") = Deltime_comb.Text
            dsNewRow.Item("TotalBags") = totalbags_ComboBox.Text
            dsNewRow.Item("Chilled") = chilled_ComboBox.Text
            dsNewRow.Item("Frozen") = frozen_ComboBox.Text
            dsNewRow.Item("Dry") = dry_ComboBox.Text
    
            ds.Tables("CustomerDetails").Rows.Add(dsNewRow)
    
            da.Update(ds, "CustomerDetails")
    
            MsgBox("New Record added to the Database")
    
    
    
        End Sub
    
    
        
        Private Sub Existing_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Existing_btn.Click
    
            dsexisting.Clear()
    
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Sam\Desktop\VB\CustomerDetails.accdb"
            con.Open()
            sql = "SELECT * FROM CustomerDetails WHERE Surname = '" & Surname_txtbx.Text & "'"
            da = New OleDb.OleDbDataAdapter(sql, con)
    
            da.Fill(dsexisting, "SearchResults")
    
            con.Close()
    
    
            ' Dim Addresslistadapter As New OleDbDataAdapter
            Address_listbx.DataSource = dsexisting.Tables("SearchResults")
            Address_listbx.DisplayMember = "String"
            Address_listbx.ValueMember = "ID"
    
           
        End Sub
    
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Postcodesearch_btn.Click
    
            dspostcode.Clear()
    
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Sam\Desktop\VB\CustomerDetails.accdb"
            con.Open() 'opens connection to database using the connection string
            sql = "SELECT * FROM Postcode_test WHERE Postcode = '" & Postcode_txtbx.Text & "'" 'selects all data from customer details table
    
            da = New OleDb.OleDbDataAdapter(sql, con)
    
            da.Fill(dspostcode, "Postcode_test") 'fills dataset with results from sql
    
            con.Close()
    
            StreetAddress_txtbx.Text = dspostcode.Tables("Postcode_test").Rows(0).Item(2)
        End Sub
    
        Private Sub Address_listbx_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Address_listbx.DoubleClick
            X = Address_listbx.SelectedValue
    
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Sam\Desktop\VB\CustomerDetails.accdb"
            con.Open()
            sql = "SELECT * FROM CustomerDetails WHERE ID =" & X
            da = New OleDb.OleDbDataAdapter(sql, con)
    
            da.Fill(dsexisting, "SearchResults")
    
            Forename_txtbx.Text = dsexisting.Tables("SearchResults").Rows(1).Item(1)
            Postcode_txtbx.Text = dsexisting.Tables("SearchResults").Rows(1).Item(3)
            housenumb_txtbx.Text = dsexisting.Tables("SearchResults").Rows(1).Item(4)
            StreetAddress_txtbx.Text = dsexisting.Tables("SearchResults").Rows(1).Item(5)
        End Sub
    
    End Class

  2. #2
    Frenzied Member
    Join Date
    Aug 2009
    Location
    Los Angeles
    Posts
    1,335

    Re: VB 2010 - Listbox duplicating items

    I am a little confused to what you are doing but think I might have an idea.
    It looks like you are adding records to a dataset called "CustomerDetails"
    then you are trying to search for a record and create another dataset called SearchResults
    You want the selected record to be placed in the textboxes (which is working)
    but it is adding that selected record to the dataset "CustomerDetails"

    It looks like you are filling your dsexisitng again when you double click the Address List Box

    If all you want to do is populate your text boxes with the selected record then just do that. Remove

    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Sam\Desktop\VB\CustomerDetails.accdb"
    con.Open()
    sql = "SELECT * FROM CustomerDetails WHERE ID =" & X
    da = New OleDb.OleDbDataAdapter(sql, con)

    da.Fill(dsexisting, "SearchResults")

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2013
    Posts
    9

    Re: VB 2010 - Listbox duplicating items

    Thank you, deleting that part of the code did solve it duplicating issue. However, I thought that code was needed to select the specific row using the ID number. I have come across another issue, regardless of what item I select in my list box the textbox's are only populated with the Mark Herd record. I Have debugged the program and the selected member doesnt update depend on which item in the list box is selected, so when i double click the Mark Herd record always populates the textbox.

    Any help is appreciated

  4. #4
    Frenzied Member
    Join Date
    Aug 2009
    Location
    Los Angeles
    Posts
    1,335

    Re: VB 2010 - Listbox duplicating items

    That's because you have it coded that way.

    Code:
     Forename_txtbx.Text = dsexisting.Tables("SearchResults").Rows(1).Item(1)
            Postcode_txtbx.Text = dsexisting.Tables("SearchResults").Rows(1).Item(3)
            housenumb_txtbx.Text = dsexisting.Tables("SearchResults").Rows(1).Item(4)
            StreetAddress_txtbx.Text = dsexisting.Tables("SearchResults").Rows(1).Item(5)
    You textboxes are set to the table rows 1
    static so now matter what you select its going to row 1
    you need to make that code reflect the selected item

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: VB 2010 - Listbox duplicating items

    You should just get all the data upfront and bind it to all your controls. You then need no extra code for navigation. For example:
    vb.net Code:
    1. Private ReadOnly table As New DataTable
    2. Private ReadOnly adapter As New OleDbDataAdapter("SQL query here", "connection string here") With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
    3. Private ReadOnly builder As New OleDbCommandBuilder(Me.adapter)
    4.  
    5. Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    6.     Me.adapter.Fill(Me.table)
    7.     Me.BindingSource1.DataSource = Me.table
    8.  
    9.     Me.ListBox1.DisplayMember = "ID"
    10.     Me.ListBox1.DataSource = Me.BindingSource1
    11.  
    12.     Me.TextBox1.DataBindings.Add("Text", Me.BindingSource1, "Column1")
    13.     Me.TextBox2.DataBindings.Add("Text", Me.BindingSource1, "Column2")
    14. End Sub
    15.  
    16. Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    17.     'Add a new record.
    18.     Me.BindingSource1.AddNew()
    19. End Sub
    20.  
    21. Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    22.     'Save all changes.
    23.     Me.BindingSource1.EndEdit()
    24.     Me.adapter.Update(Me.table)
    25. End Sub
    That's basically all you need. Whenever you select an item in the ListBox, the corresponding fields will automatically be displayed in the TextBoxes.

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2013
    Posts
    9

    Re: VB 2010 - Listbox duplicating items

    Hi thank you for the help, but i am still new to the language and still learning. I'm currently trying to test you code in a new form to understand how it works but i don't understand why your using the "Me." code or where you have declared the binding source. Also would pulling all the data on form load work if there is new records being added whilst the program is running?

    Thanks

  7. #7

    Thread Starter
    New Member
    Join Date
    Oct 2013
    Posts
    9

    Re: VB 2010 - Listbox duplicating items

    Thanks. I set them to table rows 1 because I thought i could use the ID number to pull that specific row after the sql has filtered them. any thoughts on how to go about it?

  8. #8
    Frenzied Member
    Join Date
    Aug 2009
    Location
    Los Angeles
    Posts
    1,335

    Re: VB 2010 - Listbox duplicating items

    Follow the code from jmc on post # 5 , that should do everything you want. The binding source will do what it says it does, it will bind your data
    you need to add a bindingsource from the tool box onto your form first. When you use the binding source you can then fill your textboxes as such

    Me.TextBox1.DataBindings.Add("Text", Me.BindingSource1, "Column1")

    Me.TextBox2.DataBindings.Add("Text", Me.BindingSource1, "Column2")





    You can read here on why to use ME

    http://msdn.microsoft.com/en-us/libr...(v=vs.90).aspx

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: VB 2010 - Listbox duplicating items

    The BindingSource is added to the form in the designer.

  10. #10

    Thread Starter
    New Member
    Join Date
    Oct 2013
    Posts
    9

    Re: VB 2010 - Listbox duplicating items

    Sorry for the hassle but you've approached it a whole different way to how i know, so i'm a little confused. I set up a new form with the same layout. added your code you gave me adapting it to my project but when the form loads nothing is pushed into the listbox. or was i supposed to use your code and implement it into my current project. Again sorry I am still learning the language.

    The code

    Code:
    Imports System.Data.OleDb
    Public Class Form1
        Private ReadOnly table As New DataTable
        Private ReadOnly adapter As New OleDbDataAdapter("SQL query here", "connection string here") With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
        Private ReadOnly builder As New OleDbCommandBuilder(Me.adapter)
    
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
    
            Me.adapter.Fill(Me.table)
            Me.BindingSource1.DataSource = Me.table
    
            Me.Address_listbx.DataSource = Me.BindingSource1
            Me.Address_listbx.DisplayMember = "String"
    
            Me.Surname_txtbx.DataBindings.Add("Text", Me.BindingSource1, "Forename")
            Me.Forename_txtbx.DataBindings.Add("Text", Me.BindingSource1, "Surname")
            Me.Postcode_txtbx.DataBindings.Add("Text", Me.BindingSource1, "Postcode")
            Me.housenumb_txtbx.DataBindings.Add("Text", Me.BindingSource1, "House_Number")
            Me.StreetAddress_txtbx.DataBindings.Add("Text", Me.BindingSource1, "Address")
    
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'Add a new record.
            Me.BindingSource1.AddNew()
        End Sub
    
        Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            'Save all changes.
            Me.BindingSource1.EndEdit()
            Me.adapter.Update(Me.table)
        End Sub
    End Class

  11. #11

    Thread Starter
    New Member
    Join Date
    Oct 2013
    Posts
    9

    Re: VB 2010 - Listbox duplicating items

    Name:  custdb.png
Views: 559
Size:  169.1 KB

    thats my database as well

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: VB 2010 - Listbox duplicating items

    Here's a fairly important bit that you've not customised to your own situation:
    Code:
    Private ReadOnly adapter As New OleDbDataAdapter("SQL query here", "connection string here") With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: VB 2010 - Listbox duplicating items

    The important part of my code was simply the data-binding part, i.e.
    Code:
        Me.BindingSource1.DataSource = Me.table
     
        Me.ListBox1.DisplayMember = "ID"
        Me.ListBox1.DataSource = Me.BindingSource1
     
        Me.TextBox1.DataBindings.Add("Text", Me.BindingSource1, "Column1")
        Me.TextBox2.DataBindings.Add("Text", Me.BindingSource1, "Column2")
    How you populate the DataTable is irrelevant, as long as there's a populated DataTable to bind.

  14. #14

    Thread Starter
    New Member
    Join Date
    Oct 2013
    Posts
    9

    Re: VB 2010 - Listbox duplicating items

    Thank you for all your help, I now understand how the code is working with the binding source etc. I have it functioning with a search button to filter my records. Will my previous code for adding new records still work but instead of adding it to the dataset add it to the datatable and update the binding source and table?

    Thanks so much again.

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: VB 2010 - Listbox duplicating items

    The DataTable has a Rows property that is a DataRowCollection and contains DataRows. That's where the data is. The DataTable also has a DefaultView property that is a DataView and contains DataRowViews. Each DataRowView in the DefaultView is a view of the data in a DataRow in the Rows. The DefaultView allows you to filter and sort the view of the data while the data itself is unaffected. When you bind a DataTable, e.g. to a BindingSource, it's actually the contents of the DefaultView that gets exposed, i.e. the BindingSource exposes the DataRowViews from the DefaultView of the DataTable.

    The whole point of using a BindingSource is that it is the one-stop-shop for that bound data. If you want to make a change in code, you do it through the BindingSource.

    Add a new row? Call AddNew on the BindingSource and it returns a DataRowView. Set it's properties and call EndEdit on the BindingSource and the underlying DataRow gets added to the Rows of the DataTable.

    Edit an existing row? The Current property of BindingSource returns the DataRowView that represents the record currently selected in the UI. You can index the BindingSource itself to get a DataRowView for any other record. Set the fields of that DataRowView to edit the record.

    Delete an existing row? Call RemoveCurrent on the BindingSource to delete the currently selected row or Remove or RemoveAt to delete any other row.

    Because the UI controls, the BindingSource and the DataTable are bound, any change to any one of them is automatically propagated to the other two. Those changes are all just made locally though. To save those changes to the database, call Update on your data adapter; generally the same data adapter that you called Fill on in the first place to get the original data.

  16. #16

    Thread Starter
    New Member
    Join Date
    Oct 2013
    Posts
    9

    Re: VB 2010 - Listbox duplicating items

    Ok so I'm struggling to add new records to the bindingsource and database, from what i have found online this should work:

    On click of the new customer button the textboxs are cleared and a new row is added to the datatable(bindingsource) ready to be populated.

    Code:
        Private Sub newcust_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles newcust_btn.Click
            BindingSource1.AddNew()
            Forename_txtbx.Text = ""
            Surname_txtbx.Text = ""
            Postcode_txtbx.Text = ""
            StreetAddress_txtbx.Text = ""
            housenumb_txtbx.Text = ""
        End Sub
    Then using this code the new row is added and populated with values

    Code:
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim newcustrow As DataRow = dtcustdetails.NewRow()
    
            newcustrow("Forename") = Forename_txtbx.Text
            newcustrow("Surname") = Surname_txtbx.Text
            newcustrow("Postcode") = Postcode_txtbx.Text
            newcustrow("House_Number") = housenumb_txtbx.Text
            newcustrow("Address") = StreetAddress_txtbx.Text
            BindingSource1.EndEdit()
            dtcustdetails.Rows.Add(newcustrow)
            da.Update(dtcustdetails)
    
        End Sub
    However from debugging i have found that it isnt creating a new row at all it is editing the existing record which is pulled on form load. also it doesnt add it to the database its self only the datatable in the program so no data is saved.

    error: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

    Thanks in advance
    Last edited by sherdd; Oct 24th, 2013 at 12:04 PM.

  17. #17
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,593

    Re: VB 2010 - Listbox duplicating items

    BindingSource1("Forename")

    I am not sure it would work like that. Try

    BindingSource1.Current("Forename")


    LOL,.. you edited your last post so this is no longer valid...
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

  18. #18

    Thread Starter
    New Member
    Join Date
    Oct 2013
    Posts
    9

    Re: VB 2010 - Listbox duplicating items

    Thanks i tried it but if i debug and look whats in the datatable it is still editing my previous record, cannot seem to get it to work regardless of what i've tried. cannot get the update to work either.

  19. #19
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: VB 2010 - Listbox duplicating items

    The fact that you're creating two new rows is a problem. It should be fairly clear what BindingSource.AddNew does, at least at a macro level, and it should also be fairly clear what DataTable.NewRow does. They both create a new row. You should be dealing with the BindingSource exclusively when it comes to the bound data.

    You call AddNew on the BindingSource and that will, under the hood, call NewRow on the DataTable. The new row has a RowState of Detached, i.e. it is not actually part of the DataTable. That DataRow, just like every DataRow created by that DataTable, has an associated DataRowView that is an item in the DataView exposed via its DefaultView property. It's the contents of that DefaultView that is exposed via the BindingSource. When you call AddNew, the DataRowView that is associated with the new DataRow is exposed via the Current property of the BindingSource. As such, any controls bound to that BindingSource will display that row as selected.

    When you have finished populating the new row, you call EndEdit on the BindingSource to commit the change to the underlying data source, i.e. change the RowState of the underlying DataRow from Detached to Added. The row is now part of the DataTable and ready to be inserted into the database. You call Update on your data adapter and any rows with a RowState of Added will be inserted into the database, while any with a RowState of Modified will be updated and any with a RowState of Deleted will be deleted.

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