Solved

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

Posted on 2014-11-10
7
307 Views
Last Modified: 2014-11-11
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)
0
Comment
Question by:amaru96
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40432419
What is your current SQL query?

The updated one should be something like


Select Distinct Location
From MYTABLE
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 40432652
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
 
LVL 33

Expert Comment

by:it_saige
ID: 40432803
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 1

Author Comment

by:amaru96
ID: 40433898
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
 
LVL 33

Expert Comment

by:it_saige
ID: 40433910
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
 
LVL 1

Author Comment

by:amaru96
ID: 40433935
I use it to fill a datagrid.
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 40434146
do something like this:


    dim dt as DataTable = dsResult2.Tables(0).DefaultView.ToTable(True, "Location")
    GridView1.DataSource = dt
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now