1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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,285
    Likes Received:
    364
    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,285
    Likes Received:
    364
    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