Order by last time not working

Member_2_5230414
Member_2_5230414 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
David L. HansenCEO

Commented:
Can you please give a bit more of the layout of the table and describe how the variable "LastPoster" gets populated?
Commented:
That is happening because you are converting the date into a string value with the FORMAT statement.  03 comes before 17 every time.  There is no need to format a date in the order by clause.

Just change it to:

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

Or, if lasttime is not in a date format you may need to put it in one:

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

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

Author

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

CodeCruiser
Most Valuable Expert 2012
Top Expert 2014

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial