compare values of datasets

Question

0.00/5 (No votes)

See more:

i have a database table containing:

SEG,AMK,101427Y,EG3902,EG3902-G1
SEG,AMK,101427Y,EGS650,EGS650-G1
SEG,AMK,103852S,EG3901,EG3901-G1
SEG,AMK,103852S,EG3904,EG3904-G1
SEG,AMK,103852S,EGS104,EGS104-G1
SEG,AMK,103852S,EGS650,EGS650-G1
SEG,AMK,106581C,EG3901,EG3901-G1
SEG,AMK,106581C,EG3902,EG3902-G1
SEG,AMK,111713M,EG3901,EG3901-G1
SEG,AMK,111713M,EG3902,EG3902-G1

another table containing:

1  SEG-AMK EG1832  Mechanics and Materials         286 1.5 EG1832
   2   SEG-AMK EG1833  Electrical Principles           375     1.5 EG1833  EG1952  EG1903
   3   SEG-AMK EG1835.1 Engineering Mathematics (1)    456 1.5 EG1835  EG1001
   4   SEG-AMK EG1835.2 Engineering Mathematics (2)    363 1.5 EG1907  EG1681  EG1951

i want to search for common students within exampapers
result:

example: if student 111411H is taking EG1832 in paper 1 and EG1952 in paper 2,

then:

Conflicting paper Numofstudents AdminNo
paper1 : paper2 1 111411H . . .

How to do i it?

my codes so far:

Dim dbprovider As String
       Dim dbsource As String
       Dim dspaperslist, dsmodulestudents As New DataSet
       Dim daPaperlist, daSegdata As OleDb.OleDbDataAdapter
       Dim con As New OleDb.OleDbConnection
       Dim paperslist As String
       Dim segdata As String

       dbprovider = "PROVIDER=Microsoft.Ace.OLEDB.12.0;"
       dbsource = "Data Source =" & Application.StartupPath & "\segdata.accdb"
       con.ConnectionString = dbprovider & dbsource


       con.Open()
       paperslist = "SELECT * FROM paperslist"
       daPaperlist = New OleDb.OleDbDataAdapter(paperslist, con)
       daPaperlist.Fill(dspaperslist, "paperslist")

       segdata = "SELECT * FROM ModuleStudents"
       daSegdata = New OleDb.OleDbDataAdapter(segdata, con)
       daSegdata.Fill(dsmodulestudents, "ModuleStudents")

       ''check if dataset has records
       'If dsmodulestudents.Tables(0).Rows.Count = 0 Then

       'MessageBox.Show("no records")

       ' Else

       'MessageBox.Show("records found")

       'End If

       For i As Integer = 0 To dspaperslist.Tables(0).Rows.Count - 1
           Dim found As Boolean = False
           Dim adminno As String
           For j As Integer = 0 To dsmodulestudents.Tables(0).Rows.Count - 1

           Next
       Next
       con.Close()

Posted 16-Jul-13 16:58pm

12345_abcde

Updated 17-Jul-13 21:29pm

Maciej Los

v2

Add a Solution

Comments

Priyanka7777 17-Jul-13 1:38am

Why are you comparing the values in the datasets,instead you can join the two tables in the SQL query.

12345_abcde 17-Jul-13 5:01am

i just learned about it but i dont understand despite learning it on google etc.
could you help me out?

Priyanka7777 17-Jul-13 7:01am

Join the two tables 'paperslist' and 'ModuleStudents' with the help of inner join.

12345_abcde 17-Jul-13 8:20am

what do i do after that to search for the conflicted values?

Priyanka7777 17-Jul-13 8:32am

You want to search for common students within exampapers,right?
You can do that using inner join.
What is the problem that you are facing?

12345_abcde 17-Jul-13 9:03am

yup, basically, only looking at
column adminno and modulecode in table segdata which is the first data

and papernumber(s/n) and modulecode1, modulecode2, modulecode3 ... in table paperslist.

can inner join do that?
and will i be able to get the sql statements so that i could put into my vb program?

Priyanka7777 18-Jul-13 1:36am

Were you able to solve your problem?

12345_abcde 18-Jul-13 2:15am

Hi, nope. i really got no idea how to do it.
i was told that my table design wrong, might not be able to do inner join. but the datas are given to me that i can't edit it. can i email you my data, so that you can have a look at it?

Priyanka7777 18-Jul-13 2:36am

Post the table structure along with data and let me know what are the common columns for both the tables.

12345_abcde 18-Jul-13 2:43am

table "SEGDATA":
SCHOOL CAMPUS ADMINNO MODULECODE MODULEGRP
SEG AMK 100810R EG3932 EG3932-G1
SEG AMK 100810R EG3933 EG3933-G3
SEG AMK 101427Y EG3901 EG3901-G1
SEG AMK 101427Y EG3902 EG3902-G1
SEG AMK 101427Y EGS650 EGS650-G1
SEG AMK 103852S EG3901 EG3901-G1
SEG AMK 103852S EG3904 EG3904-G2

table: paperslist

1 SEG-AMK EG1832 Mechanics and Materials 286 1.5 EG1832
2 SEG-AMK EG1833 Electrical Principles 375 1.5 EG1833 EG1952 EG1903
3 SEG-AMK EG1835.1 Engineering Mathematics (1) 456 1.5 EG1835 EG1001
4 SEG-AMK EG1835.2 Engineering Mathematics (2) 363 1.5 EG1907 EG1681 EG1951

only a part of the data.

basically, the number in the first column of 'paperslist' is the Id also the paper number.
the last few columns after'1.5' is the modulecodes

from the first table: segdata,
one adminNo(student) can be involved in several modules

comparing them to get example:
Conflictingpaper--- Numofstudents---- AdminNo
paper1 : paper2------ 2--------------- 111411H 111429B. . .

i dont know which columns are the same ><

appreciate your help.

12345_abcde 18-Jul-13 2:50am

like example:

seg amk 111411H EG1832 EG1832-G2
seg amk 111411H EG1952 EG1832-G2

then in paper 1, module EG1832 is involved,
paper2, EG1952 is involved. hence there is a conflict in paper1 & paper2
and the student is 111411H

so later on, the exam timetable scheduler will know that these 2 papers can't be scheduled under the same timeslot due to the common student.

Priyanka7777 18-Jul-13 3:28am

I did not get the purpose of 2nd table(paperlist),but still i have written a query for
your first table(SEGDATA) to get conflicting records.See if it helps you.

SELECT ADMINNO,
SUBSTRING(
(SELECT (':' + MODULECODE)
FROM SEGDATA1 S2
WHERE S1.ADMINNO=S2.ADMINNO
ORDER BY
ADMINNO,
MODULECODE
FOR XML PATH( '' ) ),3,1000) AS CONFLICTING_PAPERS
FROM SEGDATA1 S1
GROUP BY ADMINNO

12345_abcde 18-Jul-13 4:17am

the paperslist is exam paper list.

the segdata is students data.
meaning that the students in amk campus under seg is taking this module in this module group.

then as for the paperslist is the exam paper, number of students, duration of paper, and the modules covered.

students involving in paper 1's EG1832 and paper 2's EG1833 is said to be the common student within these 2 papers

then my output will be:

Conflictingpapers ------- Numberofstudent -------- AdminNo -------- Modulegroup
p1:p2 -------------------- 2 --------------------- 111411H 122356H -- G1
-------------------------- 1 ---------------------- 468209U ----------- G2

so i will need to compare the 2 list.

Maciej Los 18-Jul-13 3:30am

Do not repost: how to search for conflict data[^]!
Update your previous question and remove this one.

Priyanka7777 18-Jul-13 4:25am

I know i have reposted the answer,but the only purpose was to make things simpler for 12345_abcde.

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

compare values of datasets

Add your solution here

Preview 0

compare values of datasets

Add your solution here

Preview 0

Existing Members

...or Join us