getting percentages and display issues

If you want all the states on top and their percentages below you have to first loop over all dtr("state") and then insert a , then loop over all percentages.

To do this, since you are using an SQL reader, you will have to first read everything into a 2D array, and then generate the HTML in the required order.

To get the percentage simple just add a % sign after you've output the state count:
...align=""center"">'" & dtr("StateCount") & "%'"

Also, if you don't want the single quotes appearing, remove the single quotes right before you close and right after you open your double quotes before outputting a variable, although I'm sure you knew that.

Once all this is worked out, you can add a counter to your loop. Every time the counter MOD 8 equals 0, you can start a new row.

So it would look like this:

1. ExecuteReader
2. In a loop, save all states and statecounts in a 2D array
3. Initialize i = 0, j = 0
3. Start a loop that goes until all your entries have been output
   4. Start a while loop, while i+1 MOD 8 != 0
        5. Display state i
        6. i ++
   7. Start a new table row ()
   8  Start a while loop, while j+1 MOD 8 != 0
        9. Display statecount j
        10. j++
   11. Start a new table row
12. End the loop if you're done displaying everything

You can check if you've finished displaying everything with a boolean variable.
Also make sure that you're checking your index bounds in each of the internal loops. Neither i nor j should ever exceed the number of states you have. Once they do, set your boolean variable indicating that you are done to true, and exit the while loop.

Wow, all of that sounds really helpful, but unfortunately it's over my head. I'm not even sure how to load a 2D array with the states and state counts. Do you have any examples of this? Also, about the percentage...(and maybe you are telling me how to do it and I am just not catching on) I have the counts of how many times each state is in the database, but I need a count of the total states in order to divide and get my percentage. Of course, you may have been telling me how to do that with the 2D array and everything else you told me. I'm sorry to say I'm just lost. I really do appreciate you help and time.

Well you will have to calculate the percentages then. In the loop when you're adding all the values to an array, add the state count to a variable "TotalStateCount". Then when you go to display the percentages you will display cInt(StateCount(j)/TotalStateCount*100) & "%"


Your best bet if you're a beginner is to make two 1D arrays. Unfortunately for you, you don't know how many results you will get, so you'll have to use array lists.

I haven't tested the code below so it might need some massaging, but it's essentially what you need. You can add the formatting yourself after you check that it works.

Dim states as new ArrayList(), stateCounts as new ArrayList()
Dim totalCount = 0, tempCount as Integer
...
 
       If dtr.HasRows Then
            While dtr.Read()
                states.Add(dtr("state")) 'Add new state to the list'
                tempCount = cInt(dtr("StateCount")) 'Convert to an integer'
                stateCounts.Add(tempCount) 'Add new count to the list'
                totalCount += tempCount 'Add count to the total'
            End While
 
            'Now we can display the statistics'
            Dim done as boolean = false
            Dim i = 0, j as Integer = 0
            Do
                While i+1 Mod 8 <> 0 And i < states.Count
                    vHtml &= "<TD> " & states(i) & " </TD>"
                    i += 1
                Loop
                vHtml &= "</TR><TR>"
                While j+1 Mod 8 <> 0 And j < stateCounts.Count
                    vHtml &= "<TD> " & cInt(stateCounts(j) / totalCount * 100) & "% </TD>"
                    j += 1
                Loop
                vHtml &= "</TR><TR>"
                If j = stateCounts.Count then done = True 'Check if we are done'
            Loop While done = False
        End If

Select allOpen in new window

Hi,

Thank you so much for the help!  It works in that it has the states on top and the percentage below, but it's not starting a new row after 8 states. I'll attach the code, maybe I did something to the code you gave me that caused that part not to work. Another thing I was wondering about, in the list there is "none" (which means they did not answer the question) and "Other/Not Listed". Is there a way that I can list everything in alphabetical order (the way it is now) except to have the "none" and "Other/Not Listed" appear at the end of the list? Again, thank you so much for your help.  I know I'm difficult to deal with since I'm not very familiar with a lot of this.

Oh, sorry, I forgot to include the code:

Private Sub CountState2()
        Dim vHtml, q4, numrec
        q4 = "4. State of Current Residence:"
        Dim Myconn As SqlConnection
        Dim Mycomm As SqlCommand
        Dim sql As String
        Dim dtr As SqlDataReader
        Myconn = New SqlConnection(ConfigurationSettings.AppSettings("gDataSource"))
        Myconn.Open()
        sql = "SELECT DISTINCT Count(state) AS StateCount, state FROM tbl_Alumni_Survey_data GROUP BY state"
        vHtml = vHtml & "<table cellpadding=""3"" cellspacing=""1"" width=""100%"" align=""center"">"
        vHtml = vHtml & "<tr bgcolor=""#8B96B1"">"
        vHtml = vHtml & "<th colspan=""10"" valign=""bottom"" align=""left"">"
        vHtml = vHtml & "<FONT face=""Verdana"" size=""2"" align=""center"">" & q4 & "</FONT>"
        vHtml = vHtml & "</th>"
        vHtml = vHtml & "</tr>"
        Mycomm = New SqlCommand(sql, Myconn)
        dtr = Mycomm.ExecuteReader()
        Dim states As New ArrayList, stateCounts As New ArrayList
        Dim totalCount = 0, tempCount As Integer
        If dtr.HasRows Then
            While dtr.Read()
                states.Add(dtr("state")) 'Add new state to the list'
                tempCount = CInt(dtr("StateCount")) 'Convert to an integer'
                stateCounts.Add(tempCount) 'Add new count to the list'
                totalCount += tempCount 'Add count to the total'
            End While
 
            'Now we can display the statistics'
            Dim done As Boolean = False
            Dim i = 0, j As Integer = 0
            Do
                While i + 1 Mod 8 <> 0 And i < states.Count
                    'vHtml &= "<tr bgcolor=""E5E5E5"">"
                    vHtml &= "<TD align=""left"" bgcolor=""E5E5E5""><FONT face=""Verdana"" size=""1"">" & states(i) & " </FONT></TD>"
                    'vHtml &= "<TD> " & states(i) & " </TD>"
                    i += 1
                End While
                vHtml &= "</TR><TR>"
                While j + 1 Mod 8 <> 0 And j < stateCounts.Count
                    vHtml &= "<TD align=""left"" bgcolor=""#B9C0D0""><FONT face=""Verdana"" size=""1"" align=""center""> " & CInt(stateCounts(j) / totalCount * 100) & "% </FONT></TD>"
                    'vHtml &= "<TD> " & CInt(stateCounts(j) / totalCount * 100) & "% </TD>"
                    j += 1
                End While
                vHtml &= "</TR><TR>"
                If j = stateCounts.Count Then done = True 'Check if we are done'                
            Loop While done = False
        End If
        vHtml = vHtml & "</tr>"
        vHtml = vHtml & "<tr bgcolor=""#E5E5E5"">"
        vHtml = vHtml & "<td colspan=""10"" valign=""bottom"" align=""left"">"
        vHtml = vHtml & "&nbsp;</td>"
        vHtml = vHtml & "</tr>"
        vHtml = vHtml & "<tr bgcolor=""#8B96B1"">"
        vHtml = vHtml & "<td colspan=""10"" valign=""bottom"" align=""left"">"
        vHtml = vHtml & "&nbsp;</td>"
        vHtml = vHtml & "</tr>"
        vHtml = vHtml & "</table>"
        lblResults.Text = vHtml
    End Sub

Select allOpen in new window

Hey. I know why it's not working. Silly order of operations. Replace This:
                While i + 1 Mod 8 <> 0 And i < states.Count
                While j + 1 Mod 8 <> 0 And j < stateCounts.Count
With This:
                While (i + 1) Mod 8 <> 0 And i < states.Count
                While (j + 1) Mod 8 <> 0 And j < stateCounts.Count


That will put them in groups of 8.

Changing the sorted order isn't such an easy modification to make. If everything you initially asked for works, you should close the question, and open up a new question with the working code attached and ask the follow-up question about special case sorting.

Cheers :)

--
Alain

Hi,

I apologize for taking so long to respond. When I add the parenthesis like you suggested it basically just stalls out. It's like it keeps trying to run, but the display never comes up. Again, I thank you for taking so much time with me. It's been a huge help. I'm including the code just so you can look it over and make sure I'm adding this in the right place.

 Dim q4
        q4 = "4. State of Current Residence:"
        Dim Myconn As SqlConnection
        Dim Mycomm As SqlCommand
        Dim sql As String
        Dim dtr As SqlDataReader
        Myconn = New SqlConnection(ConfigurationSettings.AppSettings("gDataSource"))
        Myconn.Open()
        sql = "SELECT DISTINCT Count(state) AS StateCount, state FROM tbl_Alumni_Survey_data GROUP BY state"
        vHtml = vHtml & "<table cellpadding=""3"" cellspacing=""1"" width=""100%"" align=""center"">"
        vHtml = vHtml & "<tr bgcolor=""#8B96B1"">"
        vHtml = vHtml & "<th colspan=""11"" valign=""bottom"" align=""left"">"
        vHtml = vHtml & "<FONT face=""Verdana"" size=""2"" align=""center"">" & q4 & "</FONT>"
        vHtml = vHtml & "</th>"
        vHtml = vHtml & "</tr>"
        vHtml &= "<tr bgcolor=""E5E5E5"">"
        vHtml = vHtml & "<TD align=""left""><FONT face=""Verdana"" size=""1""></FONT></TD>"
        Mycomm = New SqlCommand(sql, Myconn)
        dtr = Mycomm.ExecuteReader()
        Dim states As New ArrayList, stateCounts As New ArrayList
        Dim totalCount = 0, tempCount As Integer
        If dtr.HasRows Then
            While dtr.Read()
                states.Add(dtr("state")) 'Add new state to the list'
                tempCount = CInt(dtr("StateCount")) 'Convert to an integer'
                stateCounts.Add(tempCount) 'Add new count to the list'
                totalCount += tempCount 'Add count to the total'
            End While
 
            'display the statistics
            Dim done As Boolean = False
            Dim i = 0, j As Integer = 0
            Do
 
                While (i + 1) Mod 8 <> 0 And i < states.Count
                    'vHtml &= "<tr bgcolor=""E5E5E5"">"
                    If states(i) = "" Then
                        vHtml &= "<TD align=""left""><FONT face=""Verdana"" width=""11%"" size=""1"">Omit</FONT></>"
                    Else
 
                        vHtml &= "<TD align=""left""><FONT face=""Verdana"" width=""11%"" size=""1"">" & states(i) & " </FONT></TD>"
                    End If
                    i += 1
                End While
                vHtml &= "</TR><TR  bgcolor=""#B9C0D0"">"
                vHtml = vHtml & "<TD align=""left"" width=""11%""><FONT face=""Verdana"" size=""1""></FONT></TD>"
                While (j + 1) Mod 8 <> 0 And j < stateCounts.Count
 
                    vHtml &= "<TD align=""left"" width=""11%""><FONT face=""Verdana"" size=""1"" align=""center""> " & CInt(stateCounts(j) / totalCount * 100) & "% </FONT></TD>"
                    vHtml = vHtml & "</th>"
                    'vHtml &= "<TD> " & CInt(stateCounts(j) / totalCount * 100) & "% </TD>"
                    j += 1
                End While
                vHtml &= "</TR><TR>"
                If j = stateCounts.Count Then done = True 'Check if we are done'                
            Loop While done = False
        End If
        vHtml = vHtml & "</tr>"
        vHtml = vHtml & "<tr bgcolor=""#E5E5E5"">"
        vHtml = vHtml & "<td colspan=""11"" valign=""bottom"" align=""left"">"
        vHtml = vHtml & "&nbsp;</td>"
        vHtml = vHtml & "</tr>"
        vHtml = vHtml & "<tr bgcolor=""#8B96B1"">"
        vHtml = vHtml & "<td colspan=""11"" valign=""bottom"" align=""left"">"
        vHtml = vHtml & "&nbsp;</td>"
        vHtml = vHtml & "</tr>"
        vHtml = vHtml & "</table>"
        vHtml = vHtml & "<br/>"
        lblResults.Text = vHtml

Select allOpen in new window

okay, replace the above code with this:

            'display the statistics'
            Dim done As Boolean = False
            Dim rowCount = 0, i = 0, j As Integer = 0
            Do
                While Int((i + 1) / 8) = rowCount And i < states.Count
                    If states(i) = "" Then
                        vHtml &= "<TD align=""left""><FONT face=""Verdana"" width=""11%"" size=""1"">Omit</FONT></>"
                    Else
 
                        vHtml &= "<TD align=""left""><FONT face=""Verdana"" width=""11%"" size=""1"">" & states(i) & " </FONT></TD>"
                    End If
                    i += 1
                End While
                vHtml &= "</TR><TR  bgcolor=""#B9C0D0"">"
                vHtml &= "<TD align=""left"" width=""11%""><FONT face=""Verdana"" size=""1""></FONT></TD>"
                While Int((j + 1) / 8) = rowCount And j < stateCounts.Count
 
                    vHtml &= "<TD align=""left"" width=""11%""><FONT face=""Verdana"" size=""1"" align=""center""> " & CInt(stateCounts(j) / totalCount * 100) & "% </FONT></TD>"
                    vHtml &= "</th>"
                    j += 1
                End While
                vHtml &= "</TR><TR>"
                rRowCount = rowCount + 1
                If j = stateCounts.Count Then done = True 'Check if we are done'                
            Loop While done = False

Select allOpen in new window

typo:

replace
                rRowCount = rowCount + 1
with
                rowCount += 1

That works great, except for on the first row.....it only goes to 7 and then starts and new row. The the two rows after that go to 8.

not true - 0 is a valid index. It should write
State0, State1, State2, State3, State4, State5, State6, State7, (8 states total)

is this not the case when you run it?

If it's not appearing as it should I'm guessing the problem is that in your database one of your states being returned is an empty string ("")

AWESOME!!!!!! That works perfectly!!! I'm sorry I've been so much trouble, but I can't tell you how much I appreciate your help. I do have one more question. I have several questions that this code would work with on a page. Currently I'm trying to run it like this (code below). I was wondering if there is some way that I could set it up as a generic function and run different questions through it? Like some type of loop? Or do you think it will matter if I am doing it the way I currently am. There is a total of 13 questions on the screen and I could use the code for about 8 of them. I can open another question if you think it's possible. I'm just wondering what you think about it. Thanks so much for everything!!!!

Go ahead an open another question (and this time remember to attach the code ;) ) and I'll see if I can help.

lol, I'm sorry, I always forget the code. I just thought about that!

Thanks so much for your help!

Make sure you link to the new question!

alainbryden,

I opened a new question called "can this code be ran in a loop as a generic function?" I appreciate you help so much. Thanks again!