locked
Selecting a group of records RRS feed

  • Question

  • Hi, I am trying to retrieve the last 3 records using VB.net and Access Database, I have tried both the following statements and still cannot get it to work,   

    1)  MySql3 = "SELECT TECH,REM1,TDATE,NOTES FROM (SELECT TOP(3) TECH,REM1,TDATE,NOTES FROM JOB WHERE CLCODE=@Debtor ORDER BY TDATE DESC)"

    2)  MySql3 = "SELECT TOP(3) TECH,REM1,TDATE,NOTES FROM JOB WHERE CLCODE = @Debtor ORDER BY TDATE DESC"

    Please help

    Regards

    Navin


    Navin

    Tuesday, April 14, 2015 9:25 AM

Answers

  • Thank you very much all I had to do was change "TOP (3)" to "TOP 3" and it works fine

    Regards

    Navin


    Navin

    Tuesday, April 14, 2015 1:17 PM

All replies

  • Hi Navin,

    this question is not really related to the VB language. For best support this forum should be most helpful:
    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=accessdev

    (there you can also tell if CLCODE is a unique field)


    Armin

    Tuesday, April 14, 2015 11:19 AM
  • What sort of results are you getting? I did something similar and it works fine for me.

            Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\Test Files\db1 XP.mdb"
            Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            AccessConnection.Open()
    
            Dim AccessDataAdapter = New System.Data.OleDb.OleDbDataAdapter
            Dim AccessCommand As New OleDbCommand("SELECT TOP 3 * FROM ORDERS3 WHERE ShipCountry = @Country ORDER BY OrderDate DESC", AccessConnection)
            AccessCommand.Parameters.AddWithValue("@Country", "France")
            AccessDataAdapter.SelectCommand = AccessCommand
    
    
            Dim AccessTable As New DataTable
            AccessDataAdapter.Fill(AccessTable)
    


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 14, 2015 12:52 PM
  • Thank you very much all I had to do was change "TOP (3)" to "TOP 3" and it works fine

    Regards

    Navin


    Navin

    Tuesday, April 14, 2015 1:17 PM