Avatar of Member_2_5230414
Member_2_5230414

asked on 

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.NETSQL

Avatar of undefined
Last Comment
Nasir Razzaq
Avatar of David L. Hansen
David L. Hansen
Flag of United States of America image

Can you please give a bit more of the layout of the table and describe how the variable "LastPoster" gets populated?
ASKER CERTIFIED SOLUTION
Avatar of Swindle
Swindle
Flag of United States of America image

Blurred text
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
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

Avatar of Member_2_5230414
Member_2_5230414

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

Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

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"
Visual Basic.NET
Visual Basic.NET

Visual Basic .NET (VB.NET) is an object-oriented programming language implemented on the .NET framework, but also supported on other platforms such as Mono and Silverlight. Microsoft launched VB.NET as the successor to the Visual Basic language. Though it is similar in syntax to Visual Basic pre-2002, it is not the same technology,

96K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo