• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Looping through results

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
runnerjp2005
Asked:
runnerjp2005
  • 5
  • 3
  • 3
1 Solution
 
Pratima PharandeCommented:
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
 
runnerjp2005Author 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
0
 
Pratima PharandeCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Pratima PharandeCommented:
MyDataReader = usersonpage("Send forumname from here")


send the forumname as a parameter to this function as per your requirment
0
 
runnerjp2005Author 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

0
 
CodeCruiserCommented:
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
 
runnerjp2005Author 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
0
 
CodeCruiserCommented:
>.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
 
runnerjp2005Author 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
0
 
runnerjp2005Author 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
0
 
CodeCruiserCommented:
You can change this

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


to
            Usersnames.Text = ("Name: " & UsersS)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now