Help needed sorting data in datatable from MS SQL

[Phill81]

I have a table in Microsoft SQL Called Files, with 2 columns called FileName and Section.

I want to add the data from this table into a variable as a sorted list to pass into a word document later on.

here is an example of the data in the table

FileName Section

Doc1.doc SectionA
Doc2.doc SectionB
Doc3.doc SectionB
Doc4.doc SectionA
Doc5.doc SectionA

I have created an SQL command and passed it into a dataset as follows:

cmd.CommandText = "SELECT FileName, Section FROM Files ORDER BY Section, FileName"

            cmd.ExecuteNonQuery()

Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim myDataSet As DataSet = New DataSet()

myDA.Fill(myDataSet, "MyTable")

For Each r As DataRow In myDataSet.Tables(0).Rows
	
Dim Section As String
     Section = r("Section").ToString()

Dim Documents As String
Documents = Documents & Section & vbCr

Dim FileN As String = r("FileName").ToString()

Documents = Documents & FileN.ToString & vbCrLf

Next

With this code I get the following output which sorts it but adds the section underneath every document :

SectionA

Doc1.doc
SectionA

Doc4.doc
SectionA

Doc5.doc
SectionB

Doc2.doc
SectionB

Doc3.doc

I need it to display as follows

SectionA

Doc1.doc
Doc4.doc
Doc5.doc

SectionB
Doc2.doc
Doc3.doc

any help with this would be great

Regards

Phill

[DataMiser]

cmd.CommandText = "SELECT FileName, Section FROM Files ORDER BY Section, FileName"
cmd.ExecuteNonQuery()

Well first of all you do not use executenonquery to execute a select query.

Looks like you should be using a datareader instead of a dataadapter and dataset

But in any case the answer is simple. You execute a select query with the order by clause and then read the data that is returned and do with it what you want.

You need to add some logic to get what you want out of it though. Right now you are blindly appending the section value on every pass through the loop. If that is not what you want to do then you need to add some conditional logic so that it only writes the section name when it is different than the last one you added.

Dim Documents As String
Dim Section As String, LastSection as string
     Section = r("Section").ToString()
     If Section<>LastSection then
        LastSection=Section
        Documents = Documents & Section & vbCr
     End If

This way the section only gets written when it changes

[Phill81]

Hi thanks for the reply and pointing me in the right direction I have solved the problem with the following code:

 Dim LastSection As String

            For Each r As DataRow In myDataSet.Tables(0).Rows

                Dim Section As String
                Section = r("Section").ToString()

                Dim Documents As String
                
                Dim FileN As String = r("FileName").ToString()

                 If Not Section = LastSection Then

                    LastSection = Section

                    Documents = Documents & vbCr & Section & vbCr & vbCr & FileN & vbCr

                Else

                    Documents = Documents & FileN & vbCr

                End If

                RichTextBox2.Text = Documents
            
            Next

Hopefully this will help others