My last post somehow went wrong so i try it again.
I have a form with just one combobox and a datagrid view.
What i would like to do are 2 things.
At first i would like to remove all duplicate records from the combobox that are loaded in when opening the form. How do i do this?
Second those duplicates are names of companies that have different contacts to me.
So I would like to be able to choose one of the names in the combobox which has for example 3 contacts, and show these contacts in the data grid.
Company1 james
company1 john
company2 johnnyB
Company1 anne
company3 joan
company4 rick
etc.
If i selelct company1 the datagrid should show the names james, john and anne.
How do i do this in a simple way?
Using MS-Access as a database (since you did not mention your data provider) using an OleDbConnection and OleDbCommand to load data into a DataTable, create a view (see below) which returns distinct values for a field from a database table.
Our intial SQL (returned via an OleDbCommand) into a DataTable
Code:
SELECT
Identifer,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
PostalCode,
Country
FROM Customer
Here is a simple example
Code:
Public Sub SimpleDemo()
Using cn As New OleDbConnection("Your connection string")
Dim cmd As OleDbCommand = New OleDbCommand
cmd.Connection = cn
cmd.CommandText = _
<SQL>
SELECT
Identifier,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
PostalCode,
Country
FROM Customer
</SQL>.Value
Dim dt As New DataTable With {.TableName = "Customers"}
cn.Open()
dt.Load(cmd.ExecuteReader())
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, "CompanyName"))
End Using
End Sub
Now dt has all fields and rows while DistinctCompanies has Distinct companies but having just the company names does not do much good unless we can have a pointer back to the database or the other data table so let's add the primary key
Code:
Dim ColNames() As String = {"Identifier", "CompanyName"}
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, ColNames))
In the ComboBox Identifier becomes the ValueMember, CompanyName the DisplayMember and DistinctCompanies the data source.
use a hashtable or some other mechanism to track what is going into the combo box or better yet, if your datasource is a database, use SELECT DISTINCT or something.
the combobox should track the name and primary key of the company. once someone selects a company name, look up all contacts in the database with the selected ID value and display them in the grid.
Haven't got that much code to show. I used the built in database connection of VS2010.
Code:
Private Sub form5_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Me.firmTableAdapter.Fill(Me.Kjelpasser2012DataSet.navn)
End Sub
I don't have both the firm name and the persons name in the same combobox. I just wrote this as an example.
So how do i communicate this to my datagrid and how do i prevent the duplicate records from my access database to show in the combobox?
Well this cleared up a few things, but it is still not clear on how to prevent duplicates in the combobox. used your code but i'm not sure how it works and what it does. The combobox is connected and fills the correct firm names but keeps on displaying the duplicate names
Well this cleared up a few things, but it is still not clear on how to prevent duplicates in the combobox. used your code but i'm not sure how it works and what it does. The combobox is connected and fills the correct firm names but keeps on displaying the duplicate names
If you searched Google for DataView Google would suggest DataView Distinct and the second item in the results would be http://msdn.microsoft.com/en-us/libr...(v=VS.90).aspx which in the first several paragraphs explains what my code is doing Create a DataTable that includes only distinct rows from the DataView, analogously to the DISTINCT keyword in Transact-SQL.
My advice is when someone gives you a suggestion that you do not understand is to use Google or your fav search engine and research.
Developers use parts of the .NET Framework daily where some not only use but also study and understand what they use which is critical in development of a solution for many reasons ranging from tigher, better code to less time coding. Of course there will always be something that does not make sense even after reading the documentation which is why there are forums such as this one.
If we go back to my code and studying the parts as suggested using Google and you find that something is not clicking rather than coming back and saying "I do not understand this" come back and say something like "I do not understand the second parameter of ToTable" although this means you did not research ToTable so someone here might point you to this or if that link was not clear they may give you some insight to the method.
I searched with google, but did not find everything to be clear for me. As i'm using the automated database connection. I think i need to change the OLEDB connection you mentioned. How to asddept this to my use is not quite clear to me. I understand what you are doing now, but the connection is not clear to me. How do i rewrite the connection to the database so that it willl be suitable for my "automated" connection?
The select command is used to "pull" out the correct data from the database I use the datagridview that displays the data. If i select the data form the combobox it wil show the correct data this in the datagrid.
Code:
Public Sub SimpleDemo()
Using cn As New OleDbConnection("Your connection string")
Dim cmd As OleDbCommand = New OleDbCommand
cmd.Connection = cn
cmd.CommandText = _
<SQL>
SELECT
Identifier,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
PostalCode,
Country
FROM Customer
</SQL>.Value
Dim dt As New DataTable With {.TableName = "Customers"}
cn.Open()
dt.Load(cmd.ExecuteReader())
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, "CompanyName"))
End Using
End Sub
The next part is problematic because i can't connect the dots to my own database solution. cn.open opens the oleDB connection which i don't use. I need this but again it is not quite clear to me on how to rewrite this to my needs.
Code:
Dim dt As New DataTable With {.TableName = "Customers"}
cn.Open()
dt.Load(cmd.ExecuteReader())
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, "CompanyName"))
I used a manual database connection before in VB6, but i used the automated version now and it will be double work to rewrite the whole program to a manual connection.
For automatic you may have the following generated via the IDE
Code:
Public Class Form1
Private Sub CustomerBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomerBindingNavigatorSaveItem.Click
Me.Validate()
Me.CustomerBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.Database1DataSet)
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Database1DataSet.Customer' table. You can move, or remove it, as needed.
Me.CustomerTableAdapter.Fill(Me.Database1DataSet.Customer)
End Sub
End Class
If so then to get to the DataTable we would use
Code:
Dim dt = CType(CustomerBindingSource.DataSource, Database1DataSet).Customer
Yes this code is roughly what I use.
I however don't see how I prevent duplicates in the combobox by using the code you presented earlier.
Code:
Dim dt As New DataTable With {.TableName = "Customers"}
cn.Open()
dt.Load(cmd.ExecuteReader())
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, "CompanyName"))
Only fills the data this works now in my datagridview, but does not indicate to that it prevents duplicates. Can you tell me how to prevent the duplicates in my combobox.
Yes this code is roughly what I use.
I however don't see how I prevent duplicates in the combobox by using the code you presented earlier.
Code:
Dim dt As New DataTable With {.TableName = "Customers"}
cn.Open()
dt.Load(cmd.ExecuteReader())
Dim DistinctCompanies = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, "CompanyName"))
Only fills the data this works now in my datagridview, but does not indicate to that it prevents duplicates. Can you tell me how to prevent the duplicates in my combobox.
The variable DistinctCompanies returns only Distinct rows. You would understand this if you read the MSDN documentation on the code by picking it apart, this is how I learned by studying MSDN documentation. I did not simply give you code and thought it would work but instead gave you could I know works.
It took a while to find out how this works, but i'm not getting the select distinct to prevent duplicates. I think i'm quite over my head here :-(. Reading the MSDN does not solve my problem at this point unfortunately.
I keep getting duplicate files or no load at all. Any tips?
Last edited by rakker; Jan 29th, 2012 at 10:00 AM.
So i tried a bit, but it seems that i'm not getting any further.
I created a form with a DATAGRID that fills during load (the fill routine) this form has now 1 combobox which i want to load with 1 of the collumns in from the datagrid (the "Firm" collumn).
If i select a firm from my Combobox it needs to filter all the people that are registred for this firm.
Code:
Private Sub loaddistinct()
Dim dt = CType(Me.EmployeesBindingSource.DataSource, 2012DataSet).Employees
Dim colnames() As String = {"firm", "lastname"}
Dim employeesbindingsource = _
New DataView( _
dt.DefaultView _
.ToTable("employees", True, colnames))
End Sub
in my form load i did the following.
Code:
Private Sub form5_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the '2012DataSet1.Employees' table. You can move, or remove it, as needed.
Me.EmployeesTableAdapter.Fill(Me.2012DataSet1.Employees)
Call loaddistinct()
cmbselectfirma.DisplayMember = "firm"
cmbselectfirma.ValueMember = "firm"
cmbselectfirma.DataSource = EmployeesBindingSource
End Sub
Well this doesn't seem to do the trick my combobox is now displaying nothing at all :-(. Is there anyway to get a more usefull combobox :-).
Attached is a VS2010 project which opens a MS-Access 2007 table, shows all rows for a table in a DataGridView and also populates a ComboBox with Distinct rows for two columns in the underlying source table shown in the DataGridView.
If you are using VS2008 then simply create a new project, add the form from this project into that project and make it the main form. Copy the Data folder to your project, build and run.
As shown in the code below I access data in a different manner than you but the key is to work with the DataTable, nothing else matters.
Code:
Public Class frmDemo
WithEvents bsData As New BindingSource
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim FileName As String = IO.Path.Combine(Application.StartupPath, "Data\Database1.accdb")
Dim cb As New OleDb.OleDbConnectionStringBuilder With {.DataSource = FileName, .Provider = "Microsoft.ACE.OLEDB.12.0"}
Using cn As New OleDb.OleDbConnection With {.ConnectionString = cb.ConnectionString}
Using cmd As New OleDb.OleDbCommand With {.CommandText = "SELECT CompanyName,CompanyContact FROM Company", .Connection = cn}
Dim dt As New DataTable
cn.Open()
dt.Load(cmd.ExecuteReader)
bsData.DataSource = dt
DataGridView1.DataSource = bsData
End Using
End Using
LoadComboBox()
End Sub
Private Sub LoadComboBox()
Dim dt = CType(Me.bsData.DataSource, DataTable)
Dim colnames() As String = {"CompanyName", "CompanyContact"}
Dim dv = _
New DataView( _
dt.DefaultView _
.ToTable("Companies", True, colnames))
ComboBox1.DisplayMember = "CompanyContact"
ComboBox1.ValueMember = "CompanyName"
ComboBox1.DataSource = dv
Label1.DataBindings.Add("Text", dv, "CompanyName")
End Sub
End Class
I got the combobox to select a firm.
I still have problems with getting the datagrid to reload and display only the selected firm + lastname + firstname
Firm firstname lastname
a1 john steward
a1 jim jackson
a2 jack black
a1 john johnson
a3 harry houdini
a4 larry king
a2 nonsmo king
Any tip on how to do this.?
Last edited by rakker; Feb 21st, 2012 at 09:29 AM.
I got the combobox to select a firm.
I still have problems with getting the datagrid to reload and display only the selected firm + lastname + firstname
Firm firstname lastname
a1 john steward
a1 jim jackson
a2 jack black
a1 john johnson
a3 harry houdini
a4 larry king
a2 nonsmo king
Any tip on how to do this.?
If I am understanding you correctly your ComboBox shows properly. Now to show information for the selection in the ComboBox you will need a method to identifiy the selection which would be best done by adding the primary key to query information for the contact as shown below, simply edit which columns are pulled when populating your ComboBox.
Code:
Firm firstname lastname Identifier
a1 john steward 200
a1 jim jackson 123
a2 jack black 89
a1 john johnson 345