View Single Post
  #6 (permalink)  
Old December 23rd, 2008, 02:28 PM
alliancejhall alliancejhall is offline
Friend of Wrox
 
Join Date: Nov 2007
Location: Central Florida, USA.
Posts: 207
Thanks: 2
Thanked 15 Times in 15 Posts
Default I would..

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.
__________________
Jason Hall

Follow me on Twitter @jhall2013