MS Access Query: Returning a random row problem

Discussion in 'MS Access' started by cjtemple, Jul 2, 2007.

  1. cjtemple

    cjtemple New Member

    Joined:
    Jul 2, 2007
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    I have a C# application that I am writing but everytime it runs the query it only returns the very first row. Any ideas what I am doing wrong?

    Code:
            private void pickWinnerToolStripMenuItem_Click(object sender, EventArgs e)
            {
                DataConnector dc = new DataConnector();
                try
                {
                    string msg = "";
                    Random rand = new Random();
                    int seed = rand.Next();
                    string query = "SELECT TOP 1 RND(" + seed + "), * FROM Members LEFT JOIN Winners ON Members.[MemberID] = Winners.[MemberID] WHERE (((Winners.MemberID) Is Null))";
                    DataSet dsMembers = dc.GetMemberData(query, "Members");
                    DataRowCollection drCollection = dsMembers.Tables["Members"].Rows;
                    foreach (DataRow dr in drCollection)
                    {
                        MessageBox.Show(query);
                        msg += dr["MemberFName"] + " " + dr["MemberLName"] + "\n" + dr["Address1"] + "\n" + dr["Address2"] + "\n" + dr["City"] + ", " + dr["State"] + " " + dr["ZipCode"] + "\nHome" + dr["PhoneH"] + "\nWork" + dr["PhoneW"] + "\nCell" + dr["PhoneC"];
                        MessageBox.Show("Congradulations to:\n\n" + msg, "Winner", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        dc.AddToDB("INSERT INTO Winners (MemberID) values(" + dr[1] + ")");
                    }
                }
                catch (OleDbException ole)
                {
                    ErrorBox("Query Error: " + ole);
                }
                catch (Exception ex)
                {
                    ErrorBox("General Error: " + ex);
                }
            }
    
    The purpose of the program is to pick a new winner from the table and to ensure that the individual has NOT already been choosen as a winner.
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    TOP 1 means it will return the Top Row which is why its returning only one ROW.
     
  3. cjtemple

    cjtemple New Member

    Joined:
    Jul 2, 2007
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Yes I only want 1 row, but I don't want it to always be the very first row in the database, I want it to be only 1 random row.
     
  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    Your random is not really random. See for multiple run and check whats the value of the seed.
    Random rand = new Random();
    int seed = rand.Next();
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice