Avatar of savache27
savache27

asked on 

can this code be ran in a loop as a generic function?

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.
Private Sub Race()
        q3 = "3. Race:"
        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(race) AS RaceCount, race FROM tbl_Alumni_Survey_data GROUP BY race"
        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"">" & q3 & "</FONT>"
        vHtml = vHtml & "</th>"
        vHtml = vHtml & "</tr>"
        vHtml &= "<tr bgcolor=""E5E5E5"">"
        Mycomm = New SqlCommand(sql, Myconn)
        dtr = Mycomm.ExecuteReader()
        Dim race As New ArrayList, RaceCount As New ArrayList
        Dim totalCount = 0, tempCount As Integer
        If dtr.HasRows Then
            While dtr.Read()
                race.Add(dtr("race")) 'Add new state to the list'
                tempCount = CInt(dtr("RaceCount")) 'Convert to an integer'
                RaceCount.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 rowCount = 0, i = 0, j As Integer = 0
            Do
                vHtml = vHtml & "<TD align=""left""><FONT face=""Verdana"" size=""1""></FONT></TD>"
                While Int(i / 8) = rowCount And i < RaceCount.Count
                    If race(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"">" & race(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 / 8) = rowCount And j < RaceCount.Count
 
                    vHtml &= "<TD align=""left"" width=""11%""><FONT face=""Verdana"" size=""1"" align=""center""> " & CInt(RaceCount(j) / totalCount * 100) & "% </FONT></TD>"
                    vHtml &= "</th>"
                    j += 1
                End While
                vHtml &= "</TR><TR>"
                rowCount += 1
                If j = RaceCount.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/>"
    End Sub
    Private Sub CountState2()
        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"">"
        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 rowCount = 0, i = 0, j As Integer = 0
            Do
                vHtml = vHtml & "<TD align=""left""><FONT face=""Verdana"" size=""1""></FONT></TD>"
                While Int(i / 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
                While Int(j / 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>"
                rowCount += 1
                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
    End Sub

Open in new window

Visual Basic.NETASP.NET

Avatar of undefined
Last Comment
alainbryden

8/22/2022 - Mon