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

Question

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.

Why don't you just consult the [official Microsoft documentation](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15) on `SELECT`?? It would have clearly showed that **(1)** `LIMIT` is **NOT** a valid T-SQL keyword, and **(2)** the correct syntax is `SELECT TOP (n) ......` — marc_s, Nov 07 '20 at 14:14

score 2 · Accepted Answer · edited Nov 07 '20 at 14:10

2

The correct query is:

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

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

edited Nov 07 '20 at 14:10

marc_s

732,580
175
1,330
1,459

answered Nov 07 '20 at 14:09

NEWID() is not found in your table column. – Bhadresh Patel Nov 09 '20 at 12:27

score 0 · Answer 2 · edited Nov 07 '20 at 14:12

0

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

edited Nov 07 '20 at 14:12

Chris Catignani

5,040
16
42
49

answered Nov 07 '20 at 13:53

Bhadresh Patel

1,671
17
18

1

When I try this I get the following error: SqlException: Incorrect syntax near the keyword 'BY'. – Nov 07 '20 at 13:55

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

2 Answers2