C#, ASP.NET core | SqlException: Incorrect syntax near the keyword 'TOP'

I was experimenting in ASP.NET Core with getting data from my small database.
I want to select 2 random rows from my table, but I can not get it to work.

My database table:

I figured the error I because my SqlCommand is not correct, but I don’t know how to make it correct.

My DAL:

public IEnumerable<IKarakter> GetSortedKarakters()
{
    using (SqlConnection connection = GetConnection())
    {
        connection.Open();

        var command = new SqlCommand("SELECT * FROM Karakter ORDER BY RAND() LIMIT 2;", connection);

        var reader = command.ExecuteReader();
        var sortedKarakters = new List<IKarakter>();

        while (reader.Read())
        {
            var karakter = new KarakterDTO
                        {
                            KarakterId = (int)reader["KarakterId"],
                            KarakterSoort = reader["KarakterSoort"]?.ToString(),
                            KarakterNaam = reader["KarakterNaam"]?.ToString()
                        };
            sortedKarakters.Add(karakter);
        }

        return sortedKarakters;
    }
}

The queries that I have tried:

SELECT * FROM Karatker ORDER BY RAND() LIMIT 10;
SELECT * FROM Karakter ORDER BY RAND() TOP 2;
SELECT * FROM Karakter TOP 2;
SELECT * FROM Karakter Limit 2;
SELECT TOP 2 * FROM Karakter ODER BY RAND();
SELECT TOP 2 FROM Karakter ODER BY RAND();

All the above queries throw the same error except for the keyword.

Can anyone help?

Edit: when I tried this query:

SELECT TOP 2 * 
FROM dbo.Karakter;

it displays the first 2 records of the database, this prooves that the error is not a problem from the other layers, the only problem is the query itself.

Contents hide

Answers:

Method 1

Method 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The correct query is:

SELECT TOP 2 * 
FROM Karakter 
ORDER BY NEWID();

Found it on this site: https://www.petefreitag.com/item/466.cfm

Method 2

You can use SELECT TOP 2 * FROM Karakter ORDER BY ID ;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating