Solved

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

Posted on 2014-11-10
7
316 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

Suggested Solutions

There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
This article will show, step by step, how to integrate R code into a R Sweave document
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

778 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