Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-11-10
7
Medium Priority
?
392 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 35

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 35

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
Suggested Courses

782 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