We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Looping through results

Member_2_5230414
on
Medium Priority
255 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??

Comment
Watch Question

CERTIFIED EXPERT

Commented:
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  

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT

Commented:
MyDataReader = usersonpage("Send forumname from here")


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

Author

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
>.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?

Author

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.