Matching data, datable

Question

0.00/5 (No votes)

See more:

Hi, i have this datatable dt3 which looks like:

ADminNo     PaperNo
111411H     3
111411H     18
172828z     3
172828z     18
111380Y     93
111938S     10

How to i get something like:

ConflictingPaper     Numberofstudents    AdminNo
3 : 8                2                   111411H
                                         172828z

so on and so forth.
To populate in a datagridview.
i tried something like:

VB

Dim curadminno As String = String.Empty
                        curadminno = dr3("AdminNo").ToString
                        Dim z, k As Integer
                        For z = 0 To dt3.Rows.Count
                            If dt3.Rows(z).Item(0) = curadminno Then
                                DataGridView3.Rows.Add(String.Format("{1}:{1}", _
                                                   dr3("PaperNo").ToString()))

its wrong though.

appreciate if anyone could help me out with this!
Thanks!

Posted 24-Jul-13 20:34pm

12345_abcde

Updated 24-Jul-13 20:37pm

v2

Add a Solution

Comments

Pheonyx 25-Jul-13 3:04am

I think your question is a bit out of context, where did the 8 come from in the "ConflictingPaper" column? What defines a conflict? could you explain a bit more about the scenario that you are working with as, I personally, feel the question is unclear without a greater understanding.

Is this data from SQL? or just stored in datatables in memory?

12345_abcde 25-Jul-13 3:40am

it 18 instead of 8, sorry. conflicting means, there is a common student in this 2 papers. so that later on the time table scheduler will know that these 2 papers cannot be scheduled at in the same time slot as there is common students which will cause the conflict.

data is stored in datatable memory. using with vb.net

Hope i had made the question clearer.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-07-24T21:21:00

If you use SQL Server, then you can try something like this:

SQL

DECLARE @tmp TABLE (ADminNo VARCHAR(30), PaperNo INT)

INSERT INTO @tmp (AdminNo, PaperNo)
SELECT '111411H', 3
UNION ALL SELECT '111411H', 18
UNION ALL SELECT '172828z', 3
UNION ALL SELECT '172828z', 18
UNION ALL SELECT '111380Y', 93
UNION ALL SELECT '111938S', 10

SELECT t1.AdminNo, COUNT(t1.AdminNo) AS NumberOfStudents, STUFF( (SELECT ', ' + CONVERT(VARCHAR(5),[PaperNo]) AS 'text()' 
                  FROM @tmp AS t2
                  WHERE t2.AdminNo = t1.AdminNo
                  FOR XML PATH('')), 1, 1, '') AS [ConflictingPaper]
FROM @tmp AS t1
GROUP BY AdminNo
HAVING COUNT(AdminNo)>1

Result:

AdminNo    NumberOfStudents     ConflictingPaper
111411H    2                    3, 18
172828z    2                    3, 18

[EDIT]

12345_abcde wrote:
data is retrieved from access database and stored in datatable. using with vb.net

So... You can use below query:

SQL

SELECT t1.AdminNo, COUNT(t1.AdminNo) AS NumberOfStudents
FROM @tmp AS t1
GROUP BY AdminNo
HAVING COUNT(AdminNo)>1

It will return the same result as above, but without the ConflictingPaper column.

How to fetch data from MS Access database? Please, read this: Accessing Microsoft Office Data from .NET Applications[^]

[/EDIT]

Kuthuparakkal · Answer 2 · 2013-07-25T01:34:00

Solution 2

Try this:
Your required data will be stored into the datatable dt....

VB

Dim dt As New DataTable
  ' Create four typed columns in the DataTable.
  dt.Columns.Add("ConflictingPaper", GetType(String))
  dt.Columns.Add("Numberofstudents", GetType(String))
  dt.Columns.Add("AdminNo", GetType(String))

  Dim query1 = (From _a In dt3
          Group Convert.ToString(_a.Field(Of Int32)("PaperNo"))
          By AdminNo = _a.Field(Of String)("AdminNo")
          Into Group
          Select dt.LoadDataRow(New Object() {String.Join(":", Group.ToArray()), Group.Count(), AdminNo}, False)
          ).ToList().Count()

FYI The Imports:

VB

Imports System
Imports System.Data
Imports System.Math
Imports System.Linq

Posted 25-Jul-13 1:34am

Kuthuparakkal

Updated 25-Jul-13 21:05pm

v5

Comments

12345_abcde 26-Jul-13 1:45am

Hi thanks Kuthuparakkal .

hmm, but what is _a & _afield?

and i also having error stating: unable to cast object of type 'system.int32' to type 'system.string'
i think cause my paperNo is integer values. whe i changed it to (of integer) then i have got error here:
| Group.ToArray() |

value of 1 dimensional integer array cannot be converted into 1 dimensional string array, because integer is not derived from string.

How do i solve this?

Kuthuparakkal 26-Jul-13 2:50am

Modified soln, please try!

Kuthuparakkal 26-Jul-13 3:14am

_a is alias for dt3 in Linq query. I used Linq for solving this... You may look up MSDN for Linq(Language Integrated Query)

Thanks,

Kuthuparakkal

12345_abcde 26-Jul-13 3:20am

Hi, i tried, but my form still sort of hang there, it doesnt load the data out. Please help!

Dim query1 = (From a In dt3 Group Convert.ToString(a.Field(Of Int32)("PaperNo")) By AdminNo = a.Field(Of String)("AdminNo") Into Group Select dt.LoadDataRow(New Object() {String.Join(":", Group.ToArray()), Group.Count(), AdminNo}, False)).ToList().Count()

DataGridView3.AutoGenerateColumns = True
Me.DataGridView3.DataSource = dt

Kuthuparakkal 26-Jul-13 3:31am

Put a break point on "DataGridView3.AutoGenerateColumns = True" and view what's there in dt...

12345_abcde 26-Jul-13 4:21am

How do i actually sees if there is anything in it?
i know of putting a breakpoint, after that the line turns yellow.
sorry, im still learning new to vb.net

12345_abcde 26-Jul-13 4:46am

alias for dt3 meaning? column name?
sorry im more familiar with SQL statements );

12345_abcde 26-Jul-13 5:09am

By editing it to:

dt.Columns.Add("ConflictingPaper", GetType(Int32))
dt.Columns.Add("Numberofstudents", GetType(Int32))
dt.Columns.Add("AdminNo", GetType(String))

Dim query1 = (From PaperNo In dt3 Group Convert.ToString(PaperNo.Field(Of Int32)("PaperNo")) By AdminNo = PaperNo.Field(Of String)("AdminNo") Into Group Select dt.LoadDataRow(New Object() {String.Join(":", Group.ToArray()), Group.Count(), AdminNo}, False)).ToList().Count()

DataGridView3.AutoGenerateColumns = True
Me.DataGridView3.DataSource = dt

it prompts me the error:
"Input string was not in a correct format.Couldn't store <12:69> in ConflictingPaper Column. Expected type is Int32." ;(

Kuthuparakkal 26-Jul-13 8:34am

how do you store colon separated value inside int32 datatype it's utter wrong...
When you put break point(the original code by me) and point to dt with your mouse when break point turns yellow, you see many options like "Data Table Visualizer", "Text Visualizer", "XML .." etc... Select Data Table Visualizer to see what's there in the "dt" variable
Ref: http://msdn.microsoft.com/en-us/library/zayyhzts.aspx

12345_abcde 27-Jul-13 6:41am

you mean: "ConflictingPaper", GetType(Int32)) ?
if get type(integer)?
it still returns me error too

12345_abcde 27-Jul-13 11:36am

from the datatable visualizer it shows:
comflictingpaper ----- numofstudent ------- adminno
12 ------------------- 1 ------------------ 090578N

i tried type int64, integer, string, object. but none of them seemed to work );

Could you explain to me whats _a? i can't seem to get what it is. is it the column name of my dt3?

12345_abcde 29-Jul-13 3:40am

hi Kuthuparakkal , if you see this comment, do leave a reply, as i really need your help ):

Kuthuparakkal 29-Jul-13 8:49am

Dim dt3 As New DataTable
' Create four typed columns in the DataTable.
dt3.Columns.Add("ADminNo", GetType(String))
dt3.Columns.Add("PaperNo", GetType(Int32))
dt3.Rows.Add("111411H", "3")
dt3.Rows.Add("111411H", "18")
dt3.Rows.Add("172828z", "3")
dt3.Rows.Add("172828z", "18")
dt3.Rows.Add("111380Y", "93")
dt3.Rows.Add("111380S", "10")

Dim dt As New DataTable
' Create four typed columns in the DataTable.
dt.Columns.Add("ConflictingPaper", GetType(String))
dt.Columns.Add("Numberofstudents", GetType(String))
dt.Columns.Add("AdminNo", GetType(String))

Dim query1 = (From _a In dt3
Group Convert.ToString(_a.Field(Of Int32)("PaperNo"))
By AdminNo = _a.Field(Of String)("AdminNo")
Into Group
Select dt.LoadDataRow(New Object() {String.Join(":", Group.ToArray()), Group.Count(), AdminNo}, False)
).ToList().Count()

Dim gdv As New DataGridView
gdv.AutoGenerateColumns = True
gdv.DataSource = dt
Me.Control.Add(gdv)

12345_abcde 31-Jul-13 1:55am

Hi, this actually got it to work:

Dim dt As New DataTable

' Create 3 typed columns in the DataTable.
dt.Columns.Add("ConflictingPaper", GetType(String))
dt.Columns.Add("Numberofstudents", GetType(String))
dt.Columns.Add("AdminNo", GetType(String))
Dim query1 = (From a In dt3 Group Convert.ToString(a.Field(Of Integer)("PaperNo")) By AdminNo = (a.Field(Of String)("AdminNo")) Into Group Select dt.LoadDataRow(New Object() {String.Join(":", Group.ToArray()), Group.Count(), AdminNo}, False)).ToList().Count()

DataGridView3.DataSource = dt

End If

But the column "numberofstudents" is counting the number of paper conflicted instead of counting the students(adminno)

why is that so?

and i wanted it to show:

ModuleCode numofstud adminno
13:20 -------------2----------111411H
------------------------------111283W

but its showing:

12:18------------2-----------182739H
26:34:35---------1------------111411H
.
.
.

and my datagridview is taking really long to load the data out, approx 5mins >.<