Solved

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

Posted on 2014-11-10
7
325 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

735 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