asked on 5/17/2011

Order by last time not working

Im trying to display the last post by a user in a forum.

    Shared Function lastpost(ByVal title As String) As Results
        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
        cmd.Parameters.AddWithValue("@title", title)
        Try
            con.Open()
            Dim Res As Results
            cmd.CommandText = "Select Count(*) From forum where Forum =@title and numrep <> 0  "
            Res.Result2 = cmd.ExecuteScalar()
            cmd.CommandText = "Select  title + '|' + LastPoster +'|' + Format(lasttime, 'dd/mm/yyyy hh:mm:ss') from forum where Forum =@title order by Format(lasttime, 'dd/mm/yyyy hh:mm:ss') ASC"
            Res.Result3 = cmd.ExecuteScalar()
            Return (Res)
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try
    End Function

Open in new window

where title is the name of the forum

in my db i have

1 Jp 03/12/2011 13:47:13
2 BOB 17/05/2011 17:32:57

But no matter what it displays Jp always

Visual Basic.NET SQL

Last Comment

Nasir Razzaq

5/18/2011

David L. Hansen

5/17/2011

Can you please give a bit more of the layout of the table and describe how the variable "LastPoster" gets populated?

ASKER CERTIFIED SOLUTION

Swindle

5/17/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

David L. Hansen

5/17/2011

Swindle has it right. By the way, using the TOP function should help too.
Note: If you want the more recent comment use DESC instead of ASC

cmd.CommandText = "Select TOP(1) title + '|' + LastPoster +'|' + Format(lasttime, 'dd/mm/yyyy hh:mm:ss') from forum where Forum =@title order by CONVERT(Datetime, lasttime) DESC"

Open in new window

Member_2_5230414

5/18/2011

ASKER

I get ... Undefined function 'CONVERT' in expression.

in

 Shared Function noposts(ByVal forumname As String) As Results
        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
        cmd.Parameters.AddWithValue("@forumname", forumname)
        Try
            con.Open()
            cmd.CommandText = "Select Count(*) From forum where Forum =@forumname"
            Dim Res As Results
            Res.Result1 = cmd.ExecuteScalar()
            cmd.CommandText = "Select Count(*) From forum where Forum =@forumname and numrep = 0  "
            Res.Result2 = cmd.ExecuteScalar()

            cmd.CommandText = "Select TOP 1  title + '|' + LastPoster +'|' + Format(lasttime, 'dd/mm/yyyy hh:mm:ss') from forum where Forum =@forumname AND numrep = 0 order by CONVERT(Datetime, lasttime) DESC"
            Res.Result3 = cmd.ExecuteScalar()
            Return (Res)
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
        End Try
    End Function

Open in new window

Nasir Razzaq

5/18/2011

Convert is SQL Server function. You can use cdate but as the LastTime is already a datetime column, you dont need to

cmd.CommandText = "Select TOP 1 title + '|' + LastPoster +'|' + Format(lasttime, 'dd/mm/yyyy hh:mm:ss') from forum where Forum =@forumname AND numrep = 0 order by lasttime DESC"