Hi, i have a database with the following details:

SCHOOL CAMPUS ADMINNO MODULECODE MODULEGRP
SEG AMK 100810R EG3932  EG3932-G1
SEG AMK 100810R EG3933  EG3933-G1
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-G1

now i managed to get distinct students(adminno), modulecode(subject) populate in a datagrid
also, module-students in dgv which looks like:

EG1001 ---- Student 1
----------- Student 2
----------- Student 3

EG1002 ---- Student 2
----------- Student 4

But now i want to achieve:

Modulecode --- ModuleGrp ---- numberofstudents ---- students adminno
EG3932 ------- G1 ----------- 58 ------------------ 111411H 11253U 178292I...

Can anyone advice how can i go about achieving this?
Listview? or multi Line textbox? or other choices?

using vb.net, SQL commands with OleDB to retrieve data.
Thanks in advance!

Recommended Answers

All 14 Replies

You could put your data into a datatable. From there you can iterate through the rows and re-order the data and add it to another control.

could you help me with some sample codings as im new to vb.net, not really sure how to start doing this.
i appreciate your help

Since you're already populating a dgv you should already know how to populate a datatable

You can use a For Each loop to iterate through the rows:

For Each row As DataRow In MyDataTable

Then use the ItemsArray property(row.ItemsArray(0)) to access individual items in the row, and the values to individual controls.

hi, i think i got the idea of data table. but its in columns too right?
for the students adminno column:
STUDENT
111411H 111380Y 110709R

then next 2 col doesnt have a header and i dont know how many students there will be.

could you explain or give some examples of itemsarray? im a beginner in vb.net i dont know about itemsarray yet, couldnt get any examples from the net.

Thanks

my codes are as follows:

   Dim connect As String
    connect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\segdata.accdb"
    Dim conn As New OleDbConnection(connect)
    Dim cmd As OleDbCommand = New OleDbCommand
    cmd.Connection = conn



    conn.Open()
    cmd.CommandText = "SELECT DISTINCT ModuleCode, AdminNo FROM(SEGDATA)ORDER BY ModuleCode ASC, AdminNo ASC"

    Dim dt As New DataTable
    dt.Load(cmd.ExecuteReader)





    With dgvModStud
        .AutoGenerateColumns = True
        .DataSource = dt

    End With

    Dim currentModuleCode As String = String.Empty

    For i = 0 To dgvModStud.Rows.Count - 1

        If dgvModStud.Rows(i).Cells(0).Value = currentModuleCode Then

            dgvModStud.Rows(i).Cells(0).Value = String.Empty
        Else
            currentModuleCode = dgvModStud.Rows(i).Cells(0).Value

        End If



    Next i

how do i edit to achieve what i wanted for the output?

could you explain or give some examples of itemsarray?

The itemsarray is exactly what it says, an array of the items in the row. ItemsArray(0) is the first item, ItemsArray(1) the second and so on.

It's hard to say exactly how to re-order the items without seeing exactly what's in the datatable. You can easily what's in each row by setting a break point right after line 13. when it stops execution and shows you the line with the break point, you can hover the mouse cursor over dt in line 13 and drill down through the properties expand Rows then expand ItemsArray and you will see exactly what data is in each item. This will guide you on which items you want and where.

sorry, i really cant understand. wished to show you my database as it might be clearer than i explained.
and i need to count the number of students matching with the data

i have a table containing the original data:

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

only part of it.

i really dont know how to do it. really hope you could help me out.

Assuming you have a datatable(dt) with the data formatted exactly as you've shown, and a listview(ListView1) with 4 columns and the View property set to Details this should work:

        For Each row As DataRow In dt.Rows
            Dim TempItem As New ListViewItem(row.Item(3).ToString)
            'only add if the module hasn't been added yet
            If Not ListView1.Items.ContainsKey(TempItem.Text) Then
                TempItem.SubItems.AddRange(New String() {row.Item(4).ToString.Substring(7), "1", row.Item(2).ToString})
                TempItem.Name = TempItem.Text
                ListView1.Items.Add(TempItem)
            Else
                'If the module has already been added, increase the student count by 1 and add the admin no to the last subitem.
                TempItem = ListView1.Items.Find(TempItem.Text, False)(0)
                TempItem.SubItems(2).Text = (Integer.Parse(TempItem.SubItems(2).Text) + 1).ToString
                TempItem.SubItems(3).Text &= row.Item(2).ToString & ","
            End If
        Next

Hopefully this gives you enough to proceed. If so please remember to mark this solved. If not don't hesitate to provide more details and/or ask further. Thanks

it gives me an error cannot find table, i sub your codes:
  ''Connections
        Dim connect As String
        connect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\segdata.accdb"
        Dim conn As New OleDbConnection(connect)
        Dim cmd As OleDbCommand = New OleDbCommand
        cmd.Connection = conn



    conn.Open()
    cmd.CommandText = "SELECT DISTINCT ModuleCode, AdminNo FROM(SEGDATA)ORDER BY ModuleCode ASC, AdminNo ASC"

    Dim dt As New DataTable
    dt.Load(cmd.ExecuteReader)


    'With dgvModStud
    '.AutoGenerateColumns = True
    '.DataSource = dt

    'End With

    For Each row As DataRow In dt.Rows
        Dim TempItem As New ListViewItem(row.Item(3).ToString)
        'only add if the module hasn't been added yet
        If Not ListView1.Items.ContainsKey(TempItem.Text) Then
            TempItem.SubItems.AddRange(New String() {row.Item(4).ToString.Substring(7), "1", row.Item(2).ToString})
            TempItem.Name = TempItem.Text
            ListView1.Items.Add(TempItem)
        Else
            'If the module has already been added, increase the student count by 1 and add the admin no to the last subitem.
            TempItem = ListView1.Items.Find(TempItem.Text, False)(0)
            TempItem.SubItems(2).Text = (Integer.Parse(TempItem.SubItems(2).Text) + 1).ToString
            TempItem.SubItems(3).Text &= row.Item(2).ToString & ","
        End If
    Next

    conn.Close()
    cmd.Dispose()

btw, my data is in access database table

SCHOOL CAMPUS ADMINNO MODULECODE MODULEGRP
SEG AMK 100810R EG3932  EG3932-G1
SEG AMK 100810R EG3933  EG3933-G1
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-G1

What line gives the error? I mocked up a datatable with the sample of your data and tested my code with that and it worked properly.

Dim TempItem As New ListViewItem(row.Item(3).ToString)

this line. it says cant find table 3.

my listview table has column:
ModuleCode-ModuleGroup-Numberofstudents-adminNo

hi, i edit and got it to work.
but for my AdminNo, the first and the second one is not seperated. What can i do to get it done?

e.g: 111310P291039S 111380U 291029G

and my moduleGroup
like:

ModuleCode   ModuleGroup   No.OfStudent   AdminNo
EG1001       G1            90             111411H 111293B ...
             G3            3              103829N 392801P ...
EG1003       G1            3              293840U 191923V ...

Currently its blank. How do i do it?

What edits did you make to the code? If data is missing from the listview then it probably wasn't loaded into the table.

        For Each row As DataRow In dt.Rows
            Dim TempItem As New ListViewItem(row.Item(0).ToString)
            'only add if the module hasn't been added yet
            If Not ListView1.Items.ContainsKey(TempItem.Text) Then
                TempItem.SubItems.AddRange(New String() {row.Item(1).ToString.Substring(7), "1", row.Item(1).ToString})
                TempItem.Name = TempItem.Text
                ListView1.Items.Add(TempItem)
            Else
                'If the module has already been added, increase the student count by 1 and add the admin no to the last subitem.
                TempItem = ListView1.Items.Find(TempItem.Text, False)(0)
                TempItem.SubItems(2).Text = (Integer.Parse(TempItem.SubItems(2).Text) + 1).ToString
                TempItem.SubItems(3).Text &= row.Item(1).ToString & "    "
            End If
        Next

changing the column index.

only the modulegroup is missing. the above code, only iterates the modulecodes and students i think? from what i studied from you code.

btw, a really great thanks to you!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.