Member Avatar for bhagawatshinde

Hi guys,
i used the query for randomly selecting questions from access but it will give me same result, i try it by passing different different values also passing time in rnd fuction but i will display same result.

DataSet ds = new DataSet();
            string strsel = "select * from starttest where stud_id='101301' order by rnd("+ kk +")";
            Oda = new OleDbDataAdapter(strsel, Connect.AccConn());
            Oda.Fill(ds, "starttest_new");
            DataTable dt = ds.Tables[0];
            Oda.Update(dt);
            dataGridView2.DataSource = dt;

can anybody help me....
Thanks in advance

  • 2 Contributors
  • 6 Replies
  • 323 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by bhagawatshinde

Dani AI

Generated

Short answer: this is a quirk of Access/Jet/ACE and the way Rnd is seeded when the query runs from an external client. tried several seeds and correctly suggested using a numeric column as the seed — that works reliably inside the Access UI, but when your SQL runs through OLEDB/ACE the engine initializes the random generator the same way for the session, so the ORDER BY Rnd(...) ends up deterministic from C#. See Microsoft’s Rnd documentation for how Rnd/Randomize behave. (support.microsoft.com)

Practical, reliable fixes:

  • Easiest and most predictable: randomize in .NET after you load the rows. For moderate-sized result sets this is fast and avoids provider quirks. Example (call immediately so enumeration fixes the order):
var rnd = new Random();
var dt = ds.Tables[0];
var shuffled = dt.AsEnumerable()
                .OrderBy(r => rnd.Next())
                .CopyToDataTable();
dataGridView2.DataSource = shuffled;

Create the Random once and enumerate (ToList/CopyToDataTable) immediately to freeze the order. This is a common, practical approach. (code-maze.com)

  • If you must do it inside Access SQL: use a “salted” expression so the seed changes each run (examples posted on several forums are things like using Timer()/Now() combined with the PK, or seeding with a negative expression). These work sometimes in the Access UI but are fragile when called via OLEDB; another workaround is to create a temp table with a random/autonumber GUID column and ORDER BY that. Both approaches have tradeoffs (portability, performance). Test inside Access first, then from C# — many people have seen the “same rows” symptom only when calling from ADO/OLEDB. (stackoverflow.com)

Recommendation: for quick, reliable results use the client-side shuffle in C#. If the dataset is huge and you need SQL-side sampling, precompute a random value column (or use a server that supports a true RAND() in SQL) and index it — then SELECT TOP N ORDER BY that column. This avoids the Access Rnd/OLEDB seed issues.

Recommended Answers

All 6 Replies

Member Avatar for skatamatic

What is kk? Instead of using a passed constant, try using an ID field in rnd():

Say you have a primary key called ID in this table:

string strsel = "select * from starttest where stud_id='101301' order by rnd([ID])"

According to passing a constant to rnd() will generate the same results everytime, but passing a numeric column(such as an auto-incrementing ID) will ensure that rnd() gets regenerated for each record.

Edited by skatamatic because: n/a
Member Avatar for bhagawatshinde

Hi skatamatic, even through i pass primary key it will display same result. Even i try date.Now.Second but still not succeed.

Member Avatar for skatamatic

Hmmm. Can you post the new query? Maybe try multiplying the current date.now.millisecond with the primary key. This way the random value is dependant on both the PK and the current time.

Member Avatar for bhagawatshinde

Here is my new query,

string strsel = "select * from starttest where stud_id='101301' order by rnd([que_id]), rnd("+ DateTime.Now .Second +")";
Member Avatar for skatamatic

Are you sure que_id is an auto int pk? And you should try multiplying the que_id by the time insid of rnd:

string strsel = "select * from starttest where stud_id='101301' order by rnd([que_id]*Second(Now()))";

I tested it and it works on my machine (with a different database obviously).

Member Avatar for bhagawatshinde

Hi skatamatic,
please check my code and tell me where i am going wrong

dataGridView1.Rows.Clear();
            DataSet ds = new DataSet();
          
            string strsel = "select * from starttest where stud_id='101301' order by rnd([que_id]*Second(Now()))";
            Oda = new OleDbDataAdapter(strsel, Connect.AccConn());
            Oda.Fill(ds, "starttest_new");
            DataTable dt = ds.Tables[0];
            Oda.Update(dt);
            dataGridView2.DataSource = dt;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.