Getting a single random value from a database

Question

How do i retrieve a single record from a data base but in a random manner? Any help you put forward will be highly appreciated. Thanks!

This is what i have done so far

protected void Button1_Click(object sender, EventArgs e)
{
    string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ahmed'susopriore'\Documents\Visual Studio 2013\Projects\WebApplication11\WebApplication11\Questions.accdb";
    OleDbConnection connection = new OleDbConnection(ConnectionString);
    Random rnd = new Random();
    rnd.Next();
    string myQuery = "SELECT * FROM Questions ORDER BY rnd()";
    OleDbCommand command = new OleDbCommand(myQuery, connection);
    try
    {
        connection.Open();
        OleDbDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
        ListBox1.Items.Add(reader["Number"]+"" );
        }
    }
    catch (Exception ex)
    {
        Label1.Text = "ERROR!"+ex;
    }
    finally
    {
        connection.Close();
    }
}

http://stackoverflow.com/questions/580639/how-to-randomly-select-rows-in-sql — Soner Gönül, Nov 30 '13 at 15:41
What's the problem with your existing code? Do you get an error, or does it not give the expected results? What is it doing that you don't want it to do? — Joe White, Nov 30 '13 at 15:41
The existing code that i have now displays all the items in the column and does it in reverse order instead of doing it randomly — user3052409, Nov 30 '13 at 15:45
Just because it comes back in an order you recognize doesnt mean its not random. If you dont specify an order by clause, you have no guarantee of the order from a sql database. — crthompson, Nov 30 '13 at 15:47
there are no errors. My aim is to get a single random item from the database instead of a whole column. — user3052409, Nov 30 '13 at 15:48
If you want something that seems truly random, pull the table into your own list of objects with a new guid as one of the properties of your new object. Then order by the guid — crthompson, Nov 30 '13 at 15:50
Ok, how do i achieve in getting a single value from a database? — user3052409, Nov 30 '13 at 15:59

Seyed Morteza Mousavi · Accepted Answer · 2013-11-30T17:19:19.010

1

If primary key of questions is questionId Change your code from:

string myQuery = "SELECT * FROM Questions ORDER BY rnd()";

to:

string myQuery = "SELECT Top 1 * FROM Questions ORDER BY rnd(questionID)";

Or:

string myQuery = "SELECT Top 1 * FROM Questions ORDER BY Rnd(-(100000*questionID)*Time())

edited Nov 30 '13 at 17:19

answered Nov 30 '13 at 15:40

Seyed Morteza Mousavi

6,855
8
43
69

nearly there...it does it in a reverse order instead of a random one and display all items in that column – user3052409 Nov 30 '13 at 15:46
i did not follow you. what you mean with "`display all items in that column`" – Seyed Morteza Mousavi Nov 30 '13 at 15:48
iv got a column called "Number" in my database and its from 1-4. Using a while loop i added the numbers to a ListBox1.Items.Add(reader["Number"]+"" ); once i run the programme and click the button they come out as 4321 – user3052409 Nov 30 '13 at 15:55
it says ERROR!System.Data.OleDb.OleDbException (0x80040E14): Undefined function 'NEWID' in expression. How do i define "NEWID"? – user3052409 Nov 30 '13 at 16:02
sorry i thought you use Sql server. I change code for using Ms Access Database. – Seyed Morteza Mousavi Nov 30 '13 at 16:16
Much appreciated brother, what you did worked but it only shows the 4th question only as are 4 questions. How do i get it to display a random question every time i click the button? – user3052409 Nov 30 '13 at 16:36
Your welcome. take look at this: change `rnd(questionId)` with `Rnd(-(100000*questionID)*Time())`. – Seyed Morteza Mousavi Nov 30 '13 at 16:49
AFAIK, the only way to get random is to get it from db. Also look at: http://stackoverflow.com/questions/9937222/how-to-get-random-record-from-ms-access-database – Seyed Morteza Mousavi Nov 30 '13 at 16:54
It worked! Seyed your a legend!! i cant thank you enough god bless you brother! – user3052409 Nov 30 '13 at 17:07

Getting a single random value from a database

1 Answers1