CODEWITHSUNDEEP
c#asp.netms-access
user3052409
user3052409

Reputation: 69

Getting a single random value from a database

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();
    }
}

Upvotes: 3

Views: 4176

Answers (1)

Seyed Morteza Mousavi
Seyed Morteza Mousavi

Reputation: 6963

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())

Upvotes: 1

Related Questions