Delete all but one column in dataset/table and remove duplicates

Hi guys, I'm pretty new to VB so need some help.

I've got a SQL query which runs and fills a dataset/table. It fills it with a heap of fields; Computer, Location, Floor, Owner, Type...

How can I get it to remove all the fields except "Location" and to then go through the list and remove any duplicates?

Not that it matters, but my dataset/table is sResult2.Tables(0)
LVL 1
amaru96Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mankowitzConnect With a Mentor Commented:
do something like this:


    dim dt as DataTable = dsResult2.Tables(0).DefaultView.ToTable(True, "Location")
    GridView1.DataSource = dt
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
What is your current SQL query?

The updated one should be something like


Select Distinct Location
From MYTABLE
0
 
mankowitzCommented:
You should probably apply a constraint to the table so that it does not allow duplicates. For example:

 
   Dim table As DataTable = new DataTable()
   Dim keys(1) As DataColumn
   Dim column  As DataColumn

   column = New DataColumn()
   column.DataType = System.Type.GetType("System.String")
   column.ColumnName= "Location"
   table.Columns.Add(column)
   keys(0) = column
   table.PrimaryKey = keys
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
it_saigeDeveloperCommented:
Depending on what you need this for (i.e. - datasource for a drop down box), it might be easier to just generate a new Enumerable.  Something like:
Imports Microsoft.Win32
Imports System.Runtime.InteropServices
Imports System.Text

Class DataTableExample
	Shared Sub Main()
		Try
			Dim table As New DataTable("Test Information")
			' Create four typed columns in the DataTable.
			table.Columns.Add("Computer", GetType(String))
			table.Columns.Add("Location", GetType(String))
			table.Columns.Add("Floor", GetType(Integer))
			table.Columns.Add("Owner", GetType(String))
			table.Columns.Add("Type", GetType(String))

			' Add five rows with those columns filled in the DataTable.
			table.Rows.Add("PC01", "Florida", 5, "David", "IBM")
			table.Rows.Add("PC02", "California", 4, "John", "DELL")
			table.Rows.Add("PC03", "Rio", 3, "Sally", "HP")
			table.Rows.Add("PC04", "Rio", 2, "Peter", "TOSHIBA")
			table.Rows.Add("PC05", "California", 1, "Paul", "HYUNDAI")
			table.Rows.Add("PC06", "Florida", 1, "Javier", "TANDY")
			table.Rows.Add("PC07", "New York", 2, "Denise", "ATARI")
			table.Rows.Add("PC08", "Florida", 3, "Michelle", "AMIGA")
			table.Rows.Add("PC09", "California", 4, "Ramone", "APPLE")
			table.Rows.Add("PC10", "Rio", 5, "Dan", "COMMODORE")

			Dim locations = From row In table Group row By Location = row.Field(Of String)("Location") Into LocationGroup = Group Select New With {Location}
			For Each row In locations
				Console.WriteLine(String.Format("Location = {0}", row.Location))
			Next
		Catch e As Exception
			Console.WriteLine(e.ToString())
		End Try
		Console.ReadLine()
	End Sub
End Class

Open in new window


Generates the following output:Capture.JPG
-saige-
0
 
amaru96Author Commented:
Guys, to remove the columns in my datatable all I needed to do was:

 dsResult2.Tables(0).Columns.Remove("Computer")
 dsResult2.Tables(0).Columns.Remove("Owner")
dsResult2.Tables(0).Columns.Remove("Floor")
....

Now I have the datatable with the 1 column that I need (location).

How can I now go through that and remove the duplicate entries?
0
 
it_saigeDeveloperCommented:
As I stated the solution really depends on what you need it for.  Are you wanting to write the distinct list of locations back to the database?  Are you wanting to use them for a dropdown list?  What do you need to do with the list once you obtain it?

-saige-
0
 
amaru96Author Commented:
I use it to fill a datagrid.
0
All Courses

From novice to tech pro — start learning today.