?
Solved

Looping through results

Posted on 2011-04-27
11
Medium Priority
?
246 Views
Last Modified: 2012-06-21
Im grabbing the users names currently viewing the mainforum in the last 5min
   Shared Function usersonpage(ByVal forumname As String) As String
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        Try
            con.Open()
            cmd.CommandText = "Select Username from  nousersonline where PageUrl =@forumname and (ActivityDate BETWEEN DATEADD('n', -5, Now()) and Now())"
            cmd.Parameters.AddWithValue("@forumname", forumname)
            Dim res As String
            res = cmd.ExecuteScalar()
            Return res
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try

    End Function

Open in new window


and then i thought i was looping through the results like this:
    Dim myString As String

        For Each myString In users
            Usersnames.Text = users
        Next

Open in new window


but it only shows 1 result??

0
Comment
Question by:runnerjp2005
  • 5
  • 3
  • 3
11 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35474715
Dim res As String
            res = cmd.ExecuteScalar()


Excute sclar will aways return only one result

you need to use dataset or datareader to get list

try this

Dim MyDataReader As System.Data.SQLClient.SqlDataReader

MyDataReader = cmd.ExecuteReader()  
 
        ' Loop through the DataReader and write out each entry  
        While MyDataReader.Read  
            Response.Write("Name: " & MyDataReader.Item("Username "))  
        End While  
0
 

Author Comment

by:runnerjp2005
ID: 35474880
Ok so far i have (using a accessdb)

    Shared Function usersonpage(ByVal forumname As String) As String
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        Try
            con.Open()
            cmd.CommandText = "Select Username from  nousersonline where PageUrl =@forumname and (ActivityDate BETWEEN DATEADD('n', -5, Now()) and Now())"
            cmd.Parameters.AddWithValue("@forumname", forumname)
            Dim MyDataReader As System.Data.OleDb.OleDbDataReader
            MyDataReader = cmd.ExecuteReader()
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try

    End Function

Open in new window


then on my aspx.vb page i have

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim testpage As String = "Main Forum"

        trackusers(testpage)
        Dim users As String = onforum.usersonpage("Main Forum")
        Dim yes As Integer = onforum.noposts("Main Forum")
        Userson.Text = yes.ToString
        Dim myString As String

        While MyDataReader.Read
            Response.Write("Name: " & MyDataReader.Item("Username"))
        End While


    End Sub

Open in new window


bu how do i declare MyDataReader
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35474914
Shared Function usersonpage(ByVal forumname As String) As System.Data.OleDb.OleDbDataReader

        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        Try
            con.Open()
            cmd.CommandText = "Select Username from  nousersonline where PageUrl =@forumname and (ActivityDate BETWEEN DATEADD('n', -5, Now()) and Now())"
            cmd.Parameters.AddWithValue("@forumname", forumname)
            Dim MyDataReader As System.Data.OleDb.OleDbDataReader
            MyDataReader = cmd.ExecuteReader()
return MyDataReader 
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try

    End Function

Open in new window

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim testpage As String = "Main Forum"

        trackusers(testpage)
        Dim users As String = onforum.usersonpage("Main Forum")
        Dim yes As Integer = onforum.noposts("Main Forum")
        Userson.Text = yes.ToString
          Dim MyDataReader As System.Data.OleDb.OleDbDataReader
MyDataReader = usersonpage("Send forumname from here")

        While MyDataReader.Read
            Response.Write("Name: " & MyDataReader.Item("Username"))
        End While


    End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35474926
MyDataReader = usersonpage("Send forumname from here")


send the forumname as a parameter to this function as per your requirment
0
 

Author Comment

by:runnerjp2005
ID: 35474968
Invalid attempt to call Read when reader is closed.

What does this mean?

how do i open and close the reader around

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim testpage As String = "Main Forum"

        trackusers(testpage)

        Dim yes As Integer = onforum.noposts("Main Forum")
        Userson.Text = yes.ToString
        Dim MyDataReader As System.Data.OleDb.OleDbDataReader
        MyDataReader = onforum.usersonpage(testpage)

        While MyDataReader.Read
            Response.Write("Name: " & MyDataReader.Item("Username"))
        End While


    End Sub

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474992
To reduce the memory usage and the chances of an always open connection to the DB, dont pass reader between functions


 
Shared Function usersonpage(ByVal forumname As String) As List(of String)
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        Try
            con.Open()
            cmd.CommandText = "Select Username from  nousersonline where PageUrl =@forumname and (ActivityDate BETWEEN DATEADD('n', -5, Now()) and Now())"
            cmd.Parameters.AddWithValue("@forumname", forumname)
            Dim MyDataReader As System.Data.OleDb.OleDbDataReader
            MyDataReader = cmd.ExecuteReader()
            Dim Res As New List(of String)
            While MyDataReader.Read
               Res.Add(MyDataReader.Item(0))
            End While
            Return Res
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try

    End Function

Open in new window



Then in calling code


 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim testpage As String = "Main Forum"

        trackusers(testpage)
        Dim users As List(of String) = onforum.usersonpage("Main Forum")
        Dim yes As Integer = onforum.noposts("Main Forum")
        Userson.Text = yes.ToString
        For Each User In Users
            Response.Write("Name: " & User)
        Next


    End Sub

Open in new window

0
 

Author Comment

by:runnerjp2005
ID: 35475034
Thats grate...how could i add a comma between the names??

Also could you explain the connection codes to me so i know what they do
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35475343
>.how could i add a comma between the names
Why you need that?

Dim UsersS As String = String.Join(",", Users.ToArray)

>Also could you explain the connection codes
Which codes are you referring to?
0
 

Author Comment

by:runnerjp2005
ID: 35475536
Dim MyDataReader As System.Data.OleDb.OleDbDataReader
            MyDataReader = cmd.ExecuteReader()
            Dim Res As New List(of String)
            While MyDataReader.Read
               Res.Add(MyDataReader.Item(0))
            End While
0
 

Author Comment

by:runnerjp2005
ID: 35475556
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim testpage As String = "Main Forum"

        trackusers(testpage)
        Dim users As List(Of String) = onforum.usersonpage("Main Forum")
        Dim UsersS As String = String.Join(",", users.ToArray)

        Dim yes As Integer = onforum.noposts("Main Forum")
        Userson.Text = yes.ToString
        For Each User1 In users
            Usersnames.Text = ("Name: " & User1)
        Next


    End Sub

Open in new window


where do i call usersS??

I want to display it like this

Users currently viewing the page are : runnerjp,codecruser

or

Users currently viewing the page are : runnerjp
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 35475628
You can change this

        For Each User1 In users
            Usersnames.Text = ("Name: " & User1)
        Next


to
            Usersnames.Text = ("Name: " & UsersS)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question