-
Oct 18th, 2013, 10:07 AM
#1
Thread Starter
New Member
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.
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
-
Oct 18th, 2013, 01:30 PM
#2
Frenzied Member
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")
-
Oct 19th, 2013, 10:52 AM
#3
Thread Starter
New Member
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
-
Oct 19th, 2013, 05:59 PM
#4
Frenzied Member
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
-
Oct 19th, 2013, 08:46 PM
#5
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:
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(sender As Object, e As EventArgs) Handles MyBase.Load
Me.adapter.Fill(Me.table)
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")
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'Add a new record.
Me.BindingSource1.AddNew()
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
'Save all changes.
Me.BindingSource1.EndEdit()
Me.adapter.Update(Me.table)
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.
-
Oct 21st, 2013, 07:48 AM
#6
Thread Starter
New Member
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
-
Oct 21st, 2013, 07:50 AM
#7
Thread Starter
New Member
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?
-
Oct 21st, 2013, 10:03 AM
#8
Frenzied Member
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
-
Oct 21st, 2013, 05:54 PM
#9
Re: VB 2010 - Listbox duplicating items
The BindingSource is added to the form in the designer.
-
Oct 22nd, 2013, 03:44 AM
#10
Thread Starter
New Member
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
-
Oct 22nd, 2013, 03:50 AM
#11
Thread Starter
New Member
Re: VB 2010 - Listbox duplicating items
thats my database as well
-
Oct 22nd, 2013, 04:11 AM
#12
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}
-
Oct 22nd, 2013, 04:13 AM
#13
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.
-
Oct 22nd, 2013, 07:36 AM
#14
Thread Starter
New Member
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.
-
Oct 22nd, 2013, 08:22 AM
#15
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.
-
Oct 24th, 2013, 11:19 AM
#16
Thread Starter
New Member
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.
-
Oct 24th, 2013, 11:59 AM
#17
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
-
Oct 24th, 2013, 01:48 PM
#18
Thread Starter
New Member
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.
-
Oct 24th, 2013, 06:15 PM
#19
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|