-
September 30th, 2016, 09:31 AM
#1
Help needed sorting data in datatable from MS SQL
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:
Code:
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
-
September 30th, 2016, 10:21 AM
#2
Re: Help needed sorting data in datatable from MS SQL
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.
Code:
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
Always use [code][/code] tags when posting code.
-
October 3rd, 2016, 07:29 AM
#3
Re: Help needed sorting data in datatable from MS SQL
Hi thanks for the reply and pointing me in the right direction I have solved the problem with the following code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|