Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

funny loop question

Posted on 2010-08-12
15
Medium Priority
?
273 Views
Last Modified: 2012-05-10
i have a loop that reads from a database and it gets a name from a database field. i need some way to make it where when the names start to change i add the letter the name starts with

EXMAPLE
J
john doe
john smith

K
Keith willams
Kim Loving



etc............
0
Comment
Question by:Chris Jones
  • 6
  • 4
  • 2
  • +3
15 Comments
 
LVL 5

Expert Comment

by:scgstuff
ID: 33421152
What are you doing with the loop?  Are you adding it to a dataset?  Are you just displaying the info?  

dim curLetter as string = ""
dim newLetter as string = ""

for each name in ______________
     newLetter = left(name, 1)

     if curLetter <> newLetter then
          'Do something with the letter
     end if

     'Do whatever with the name

     curLetter = newLetter

Next
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33421175
Well normally you would do something like the following psuedo-code:
char lastStartLetter = ''c;

while ( not_at_end_of_table )
{
    if ( first_letter_of_current_record != lastStartLetter)
    {
         output_letter_to_screen
    }

    output_row_data

    lastStartLetter = first_letter_of_current_record

    read_next_record
}

Open in new window

0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33421213
i am displaying the letter to the screen with the names i pul from the database
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Accepted Solution

by:
Pieter Jordaan earned 668 total points
ID: 33421244

I would loop from a to z, and then check which entries start with the current alphabet character.

Look at the attached code.

Create a form with a textbox called TextBox1, and add the following code to Form1_Load
        Dim a As New ArrayList
        a.Add("john doe")
        a.Add("john smith")
        a.Add("Keith willams")
        a.Add("Kim Loving")

        Dim alphabet As String = "abcdefghijklmnopqrstuvwxyz"
        Dim CH As Char = ""

        For abc As Integer = 1 To alphabet.Length
            CH = Mid(alphabet, abc, 1)
            TextBox1.Text += vbCrLf & UCase(CH) & vbCrLf

            For Each s As String In a
                If Mid(LCase(s), 1, 1) = CH Then
                    TextBox1.Text += s & vbCrLf
                End If
            Next
        Next

Open in new window

0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33421266
BitFreeze: this will take a long time and the sql statement i am using puts everything in order for me
0
 
LVL 11

Expert Comment

by:Pieter Jordaan
ID: 33421306

Use SQL to pull a list of all user names that start with a, then b, then c etc.
0
 
LVL 17

Assisted Solution

by:Zhaolai
Zhaolai earned 1332 total points
ID: 33421339
Try this:
strNames is an array of names pulled from database.

        For Each strName As String In strNames
            If Not strName.StartsWith(sFirstLetter, System.StringComparison.CurrentCultureIgnoreCase) Then
                sFirstLetter = strName.Substring(0, 1).ToUpper
                'display the first letter here
            End If
            'display name here
        Next

Open in new window

0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33421351
Zhaolai: do you think the code below will run faster than storeing it into a data array



            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            temp = name.Substring(0, 1)

            If temp IsNot temp Then
                'Insert a paragraph at the beginning of the document.
                oPara1 = oDoc.Content.Paragraphs.Add
                oPara1.Range.Text = temp
                oPara1.Range.Font.Bold = True
                oPara1.Format.SpaceAfter = 24    '24 pt spacing after paragraph.
                oPara1.Range.InsertParagraphAfter()
            End If

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Open in new window

0
 
LVL 17

Assisted Solution

by:Zhaolai
Zhaolai earned 1332 total points
ID: 33421354
Oooop, complete code here:


        Dim sFirstLetter As String = " "
        For Each strName As String In strNames
            If Not strName.StartsWith(sFirstLetter, System.StringComparison.CurrentCultureIgnoreCase) Then
                sFirstLetter = strName.Substring(0, 1).ToUpper
                'display the first letter here
            End If
            'display name here
        Next

Open in new window

0
 
LVL 11

Expert Comment

by:Pieter Jordaan
ID: 33421386
It depends on the latency between server and client, also processing power, because you are switching the processing between server pulling a, b, c separately,  or client pulling everything then separating it.
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33421400
it may help if i display the sql statemnt that i am using this may help show the steps i am using to get the data.
        sqlstmt = "SELECT * FROM TPerson AS P INNER JOIN TDIR AS L ON L.TDIR_PIDM = P.TPERSON_PIDM" & _
                 " INNER JOIN TAccounts AS S ON S.Acct = L.TDIR_Acct" & _
                 " INNER JOIN TCables AS A ON A.DN = L.TDIR_DN " & _
                 " INNER JOIN TBuildings AS B ON B.Bldg3d = A.Bldg3d WHERE COALESCE( L.TDIR_DN, '') NOT IN ('n/a', '')  AND  S.dateend IS NULL AND A.dateend IS NULL ORDER BY P.TPERSON_Name"

Open in new window

0
 
LVL 11

Expert Comment

by:Pieter Jordaan
ID: 33421455

So the server will either build and sort the entire list in one go, or do it in 26 parts.

The difference should be insignificant.
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33421496
ok i got this to work using an exmaple in the post
            temp = name.Substring(0, 1)
            If temp <> temp2 Then

                'Insert a paragraph at the beginning of the document.
                oPara1 = oDoc.Content.Paragraphs.Add
                oPara1.Range.Text = temp
                oPara1.Range.Font.Bold = True
                oPara1.Format.SpaceAfter = 0    '24 pt spacing after paragraph.
                oPara1.Range.InsertParagraphAfter()
            End If
     temp2 = name.Substring(0, 1)

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 33422247
why not just add the Letters into the result set from the database....e.g. select firstname+' '+lastName as Name, Lastname from yourtableUnion Select Substring(Alpha,y.number,1) as Name, Substring(Alpha,y.number,1) as Lastname from (Select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' as Alpha, (Select number from master..spt_values where type='P' and number between 1 and 26) as x   ) as yOrder By lastnamethen you can just display the data....
0
 
LVL 1

Author Closing Comment

by:Chris Jones
ID: 33510438
Awesome greatr work guys
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question