C# SQL Random Sample using local database

Question

I was wondering how can i get n number of samples randomly from a local database using c#

What I have done so far.

"SELECT TOP "+n+" QUESTION,C1,C2,C3,C4,ANSWER FROM qPIPE WHERE CONTENT = 'Topic' ORDER BY RAND()"

"SELECT QUESTION,C1,C2,C3,C4,ANSWER FROM qPIPE WHERE CONTENT = 'Topic' ORDER BY RAND() LIMIT "+n+""

error: Additional information: Undefined function 'RAND' in expression.

"SELECT TOP "+n+" QUESTION,C1,C2,C3,C4,ANSWER FROM qPIPE WHERE CONTENT = 'Topic' ORDER BY NEWID()"

Additional information: Undefined function 'NEWID' in expression.

My connection string

ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0DataSource=|DataDirectory|\questionbank.accdb";

But when I try to query it to MS SQL

SELECT TOP n QUESTION, C1, C2, C3, C4, ANSWER FROM qPIPE WHERE CONTENT = 'Topic' ORDER BY NEWID()

It works fine. Get n number of questions from the table with the matching topic.

Im using MVS 2015, MSSMS 2017. Working on a win form.

edit:

using ORDER BY RND()

edit:

connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "SELECT TOP 2 QUESTION,C1,C2,C3,C4,ANSWER FROM qPIPE WHERE CONTENT = 'Thermodynamics' ORDER BY RND()";
            OleDbDataAdapter dAdap = new OleDbDataAdapter(command);
            DataTable dTable = new DataTable();
            dAdap.Fill(dTable);
            dataGridView1.DataSource = dTable;
connection.Close();

Why SQL Server when it states clearly that you connect to Access `questionbank.accdb`? — Lukasz Szozda, Oct 08 '17 at 17:14
This might be of interest: https://stackoverflow.com/q/13040246/533120 — Branko Dimitrijevic, Oct 08 '17 at 17:50

Gordon Linoff · Answer 1 · 2017-10-08T17:49:46.660

3

In SQL Server, you can use the NEWID() function:

SELECT TOP "+n+" QUESTION,C1,C2,C3,C4,ANSWER
FROM qPIPE
WHERE CONTENT = 'Topic'
ORDER BY NEWID();

In MS Access, you can use RND():

SELECT TOP "+n+" QUESTION,C1,C2,C3,C4,ANSWER
FROM qPIPE
WHERE CONTENT = 'Topic'
ORDER BY RND();

edited Oct 08 '17 at 17:49

answered Oct 08 '17 at 16:49

Gordon Linoff

1,242,037
58
646
786

hi gordon. i already tried that. but still having an error. but when i remove the ORDER BY NEWID(). i can select the first n samples. but i want to select n samples. – Jepher John Chang Oct 08 '17 at 16:51
This answer works fine for me (SQL Server). Whats your error? – sa-es-ir Oct 08 '17 at 17:09
@SaeedEsmaeelinejad OP connects to MS Access. And there is no `RAND/NEWID` function. – Lukasz Szozda Oct 08 '17 at 17:13

Lukasz Szozda · Accepted Answer · 2017-10-08T17:59:22.170

1

You should avoid concatenating SQL string (possible SQL injection). You could pass n as parameter:

DECLARE @n INT = 5;

SELECT TOP (n) QUESTION, C1, C2, C3, C4, ANSWER
FROM qPIPE
WHERE CONTENT = 'Topic'
ORDER BY NEWID();

EDIT:

Based on your connection string I assume that you are connecting to MS Access Database. Then you may use ORDER BY RND(-(100000*ID)*Time()). Based on kobik's comment

edited Oct 08 '17 at 17:59

answered Oct 08 '17 at 16:56

Lukasz Szozda

162,964
23
234
275

hi lad. i have converted string to int. my problem is usig the newid() or rand() condition. – Jepher John Chang Oct 08 '17 at 17:00
@JepherJohnChang Try `ORDER BY RND()` – Lukasz Szozda Oct 08 '17 at 17:02
it disregards the n number of samples. but select all samples. – Jepher John Chang Oct 08 '17 at 17:16
@JepherJohnChang Did you use `TOP` clause? – Lukasz Szozda Oct 08 '17 at 17:17
yes. "SELECT TOP "+n+" QUESTION,C1,C2,C3,C4,ANSWER FROM qPIPE WHERE CONTENT = 'Thermodynamics' ORDER BY RND()" i have edited my post. you can see the picture of the query. – Jepher John Chang Oct 08 '17 at 17:22
@JepherJohnChang Probably somethig wrong with your query (concatenation). Try: `SELECT TOP 2 QUESTION,C1,C2,C3,C4,ANSWER FROM qPIPE WHERE CONTENT = 'Thermodynamics' ORDER BY RND()` – Lukasz Szozda Oct 08 '17 at 17:31
still the same samples are shown. what I have observed, yes It gives a random order but everytime i run the query it always give the same order. – Jepher John Chang Oct 08 '17 at 17:32
still having the same result. – Jepher John Chang Oct 08 '17 at 17:37
sadly. no changes. "SELECT TOP 10 QUESTION,C1,C2,C3,C4,ANSWER FROM qPIPE WHERE CONTENT = 'Thermodynamics' ORDER BY RND(100000 * Time())"; – Jepher John Chang Oct 08 '17 at 17:40
i used ORDER BY RND(ID), it gives n samples. but still everytime i query it. same order. – Jepher John Chang Oct 08 '17 at 17:44
@JepherJohnChang Did you add `-`? – Lukasz Szozda Oct 08 '17 at 17:44
ORDER BY RND(ID -100000*Time())? like this? or ORDER BY RND(ID ,-100000*Time()) if i use ORDER BY RND(-100000*Time()), same results from the picture. – Jepher John Chang Oct 08 '17 at 17:48
@JepherJohnChang Like this `ORDER BY RND(-(100000*ID)*Time())` – Lukasz Szozda Oct 08 '17 at 17:49

C# SQL Random Sample using local database

2 Answers2