I would create a function that get's the users count then call that function on the page load. I'm going to assume you are storing the username in a session variable and assume your table names as well. This code is just an example of what to do you would need to adapt it to suite your needs.
Code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
Dim Count As String = GetUserCount(Session("UserName").ToString).ToString
If Not String.IsNullOrEmpty(Count) Then
lblUserCount.Text = Count
Else
lblUserCount.Text = "Sorry could not retrieve count."
End If
End If
End Sub
In the above code i am calling the function GetUserCount and passing the username which i assume is stored in a session variable and assigning it's return value to a variable named Count. I then check if that variable has a value. If it does then I assign the label lblUserCount text the count. If there is no value for count I tell the user with the text of the label.
Below is the actual GetUserCount function. First I create my connection to the DB then my query and my reader. I then create a string variable and assign it the UserName value. I then check to make sure it has a value if not i dispose of all the variables then return nothing and exit the function.
otherwise i open my connection run the query check to make sure that i return rows then if there are rows i return the result of the query. then close my connection and reader and dispose my variables.
Code:
Private Function GetUserCount(ByVal UserName As String) As String
Dim sqlCon As New System.Data.SqlClient.SqlConnection("Connection String Here")
Dim sqlCmd As New System.Data.SqlClient.SqlCommand("Select Count(UserName) As UserCount From tblUsers Where UserName=@UserName")
Dim sqlDR As System.Data.SqlClient.SqlDataReader = Nothing
Dim strUserName As String = Nothing
Try
strUserName = UserName
Catch
strUserName = Nothing
Count = Nothing
End Try
If String.IsNullOrEmpty(strUserName) Then
sqlCon.Dispose()
sqlCon = Nothing
sqlCmd.Dispose()
sqlCmd = Nothing
sqlDR = Nothing
strUserName = Nothing
Count = Nothing
Return Nothing
Exit Function
End If
Try
sqlCon.Open()
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.AddWithValue("@UserName", strUserName)
sqlCmd.Connection = sqlCon
sqlDR = sqlCmd.ExecuteReader
sqlDR.Read()
If sqlDR.HasRows Then
Return sqlDR("UserCount").ToString
End If
Finally
sqlDR.Close()
sqlDR = Nothing
sqlCmd.Dispose()
sqlCmd = Nothing
sqlCon.Close()
sqlCon.Dispose()
sqlCon = Nothing
strUserName = Nothing
End Try
End Function
Like I said this is an EXAMPLE you would have to adapt it to suite your structure and names. But this should give you a pretty good Idea. There may be some overkill in there but i like to to be safe rather than sorry.
How to update a label controll from a db
