Looping

Hi there,

this is my first post, so hello everyone.

I've been coding in asp.net for about a year now, and I've written some code that I know can be tidied up .. I'm just not sure how. My page has a table with a number of links and labels, the code behind the page returns the number of rows for each table the code I have at present is:

con.Open()
 Dim sql As String = "SELECT COUNT (*) FROM [" & HyperLink1.Text & "]"
 Dim sqlcom As New SqlCommand(sql, con)
 Label1.Text = sqlcom.ExecuteScalar
 HyperLink1.NavigateUrl = "edittable.aspx?id=" & HyperLink1.Text
 sql = "SELECT COUNT (*) FROM [" & HyperLink2.Text & "]"
 sqlcom.CommandText = sql
 Label2.Text = sqlcom.ExecuteScalar
 HyperLink2.NavigateUrl = "edittable.aspx?id=" & HyperLink2.Text
 sql = "SELECT COUNT (*) FROM [" & Trim(HyperLink3.Text) & "]"
 sqlcom.CommandText = sql
 Label3.Text = sqlcom.ExecuteScalar
 HyperLink3.NavigateUrl = "edittable.aspx?id=" & HyperLink3.Text
 sql = "SELECT COUNT (*) FROM [" & HyperLink4.Text & "]"
 sqlcom.CommandText = sql
 Label4.Text = sqlcom.ExecuteScalar
 HyperLink4.NavigateUrl = "edittable.aspx?id=" & HyperLink4.Text
 sql = "SELECT COUNT (*) FROM [" & HyperLink5.Text & "]"
 sqlcom.CommandText = sql
 Label5.Text = sqlcom.ExecuteScalar
 HyperLink5.NavigateUrl = "edittable.aspx?id=" & HyperLink5.Text
 sql = "SELECT COUNT (*) FROM [" & HyperLink6.Text & "]"
 sqlcom.CommandText = sql
 Label6.Text = sqlcom.ExecuteScalar
 HyperLink6.NavigateUrl = "edittable.aspx?id=" & HyperLink6.Text
 sql = "SELECT COUNT (*) FROM [" & HyperLink7.Text & "]"
 sqlcom.CommandText = sql
 Label7.Text = sqlcom.ExecuteScalar
 HyperLink7.NavigateUrl = "edittable.aspx?id=" & HyperLink7.Text
 sql = "SELECT COUNT (*) FROM [" & HyperLink8.Text & "]"
 sqlcom.CommandText = sql
 Label8.Text = sqlcom.ExecuteScalar
 HyperLink8.NavigateUrl = "edittable.aspx?id=" & HyperLink8.Text
 sql = "SELECT COUNT (*) FROM [" & HyperLink9.Text & "]"
 sqlcom.CommandText = sql
 Label9.Text = sqlcom.ExecuteScalar
 HyperLink9.NavigateUrl = "edittable.aspx?id=" & HyperLink9.Text
 sql = "SELECT COUNT (*) FROM [" & Hyperlink10.Text & "]"
 sqlcom.CommandText = sql
 Label10.Text = sqlcom.ExecuteScalar
 Hyperlink10.NavigateUrl = "edittable.aspx?id=" & Hyperlink10.Text
 sql = "SELECT COUNT (*) FROM [" & Hyperlink11.Text & "]"
 sqlcom.CommandText = sql
 Label11.Text = sqlcom.ExecuteScalar
 Hyperlink11.NavigateUrl = "edittable.aspx?id=" & Hyperlink11.Text
 sql = "SELECT COUNT (*) FROM [" & Hyperlink12.Text & "]"
 sqlcom.CommandText = sql
 Label12.Text = sqlcom.ExecuteScalar
 Hyperlink12.NavigateUrl = "edittable.aspx?id=" & Hyperlink12.Text
 sql = "SELECT COUNT (*) FROM [" & Hyperlink13.Text & "]"
 sqlcom.CommandText = sql
 Label13.Text = sqlcom.ExecuteScalar
 Hyperlink13.NavigateUrl = "edittable.aspx?id=" & Hyperlink13.Text
 sql = "SELECT COUNT (*) FROM [" & Hyperlink14.Text & "]"
 sqlcom.CommandText = sql
 Label14.Text = sqlcom.ExecuteScalar
 Hyperlink14.NavigateUrl = "edittable.aspx?id=" & Hyperlink14.Text
 sql = "SELECT COUNT (*) FROM [" & Hyperlink15.Text & "]"
 sqlcom.CommandText = sql
 Label15.Text = sqlcom.ExecuteScalar
 Hyperlink15.NavigateUrl = "edittable.aspx?id=" & Hyperlink15.Text
 sql = "SELECT COUNT (*) FROM [" & Hyperlink16.Text & "]"
 sqlcom.CommandText = sql
 Label16.Text = sqlcom.ExecuteScalar
 Hyperlink16.NavigateUrl = "edittable.aspx?id=" & Hyperlink16.Text
 sql = "SELECT COUNT (*) FROM [" & Hyperlink17.Text & "]"
 sqlcom.CommandText = sql
 Label17.Text = sqlcom.ExecuteScalar
 Hyperlink17.NavigateUrl = "edittable.aspx?id=" & Hyperlink17.Text
 con.Close()

CAn someons put this in a loop for me as I couldn't seem to get it working?

M3ckonTry this:

 For i = 1 To 17
 Dim hyperlink As HyperLink = CType(FindControl("Hyperlink" & i), HyperLink)
 Dim label As Label = CType(FindControl("Label" & i), Label)
 sql = "SELECT COUNT (*) FROM [" & hyperlink.Text & "]"
 sqlcom.CommandText = sql
 label = sqlcom.ExecuteScalar
 hyperlink.NavigateUrl = "eddittable.aspx?id=" hyperlink.Text
 Next i

Thank you!