troubleshooting Question

getting percentages and display issues

Avatar of savache27
savache27 asked on
Visual Basic.NETASP.NETWeb Languages and Standards
19 Comments1 Solution269 ViewsLast Modified:
Hi,

I have an online survey and the results are stored in one table. I need to display the results as percentages so I'm trying to figure out how to pull (in this example) the States (if they exist in the table) from the table with the count and then display them with a percentage. I need a count of the total states listed so that I can divide by that to get the percentage. I'm not sure how to do that, also, my display is way off. I wanted to make it look like this:

AL   AR       CA      IL       KY
2%   15%    5%     0%    9%

Whatever states are in the table, with the percentage below. I only wanted to display about 8 per row
and then go to another row to complete the next 8. But of course I'm doing it wrong, so I get this

'AK'
'2' 'AR'
'3' 'CA'
'2' 'IL'
'1' 'KY'
'1' 'none'
'3' 'OR'
'1' 'Other/Not Listed'
'1' 'TX'
'1' 'WY'

If anyone could help me with the percentage or the display I would really appreciate it. I'm attaching my code below:
Private Sub CountState()
        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=""9"" valign=""bottom"" width=""25%"" align=""left"">"
        vHtml = vHtml & "<FONT face=""Verdana"" size=""2"" align=""center"">" & q4 & "</FONT>"
        vHtml = vHtml & "</th>"
        vHtml = vHtml & "</tr>"
        vHtml = vHtml & "<tr bgcolor=""E5E5E5"">"
        Mycomm = New SqlCommand(sql, Myconn)
        dtr = Mycomm.ExecuteReader()
        If dtr.HasRows Then
            While dtr.Read()
 
                vHtml = vHtml & "<TD align=""left""><FONT face=""Verdana"" size=""1"">'" & dtr("state") & "'</FONT></TD>"
                vHtml = vHtml & "</tr>"
                vHtml = vHtml & "<tr bgcolor=""#B9C0D0"">"
                vHtml = vHtml & "<th valign=""bottom"" width=""11%"" align=""left"">"
                vHtml = vHtml & "<FONT face=""Verdana"" size=""1"" align=""center"">'" & dtr("StateCount") & "'</FONT>"
                vHtml = vHtml & "</th>"
            End While
        End If
        dtr.Close()
        Myconn.Close()
        lblResults.Text = vHtml
    End Sub
ASKER CERTIFIED SOLUTION
alainbryden

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros