Retrieving random data from sql database no repeat

Question

Hi im creating a program where when the form loads random data will retrieve from the database and my problem is the code for not repeating the data again. im using vb 2008 and sql database, programming language : C#

for example:

Data in Database

Word_ID | Word
1 | eye
2 | cheese
3 | mouse

Then form loads it will retrieve random data and display in the label

example output:

cheese

so cheese now will not display on the next random

my code:

SqlConnection conn =
new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\sony\Documents\Visual Studio 2008\Projects\Hangman_Final\Hangman_Final\hangman_db.mdf;Integrated Security=True;User Instance=True");

conn.Open();

SqlCommand command = conn.CreateCommand();
command.CommandText = "SELECT TOP 1 Words from word order by NEWID()";
command.CommandType = CommandType.Text;

SqlDataReader reader = command.ExecuteReader();

// display the results
while (reader.Read())
{
    string output = reader["Words"].ToString();
    label5.Text = output;
}

// close the connection
reader.Close();
conn.Close();

How many words are in the database? Depending on entries, it would be more reliable to cache the words and pop them off as you need them, insuring you never get a duplicate. otherwise, you're going to need to test WHERE word_id NOT IN (already found IDs) — Brad Christie
– Brad Christie, Commented Oct 7, 2011 at 13:23
Adrian and Daryl Teo -the question is how can i prevent the repetition of the data Brad -i have 40 words in the database — steph22
– steph22, Commented Oct 7, 2011 at 13:40

KV Prajapati · Accepted Answer · 2011-10-07 13:24:39Z

2

Store words into List<String>. Get an element at random index from the list , print it and remove it.

answered Oct 7, 2011 at 13:24

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bastardo Over a year ago

I always have the same solution you have, Mr.AVD and you are always quicker.^_^

Adriano Carneiro Over a year ago

The efficiency of this approach will depend on the amount of words in DB and how often this list will be reloaded from the DB

Paddy · Accepted Answer · 2011-10-07 13:23:24Z

0

Is it just a matter of including the word that was just loaded as a 'and WORD <> 'PreviousWord'' in the where clause of you select statement?

answered Oct 7, 2011 at 13:23

Paddy

34k15 gold badges82 silver badges121 bronze badges

1 Comment

Paul Sasik Over a year ago

You would need to track all previous words with this method not just the last one.

Jayanta Dey · Accepted Answer · 2011-10-07 13:24:31Z

0

Assuming that you are storing the words along with an integer index in the database table, why not generate a random number (between the max number/index of words you have) in C# (using Random) and then fetch the word matching the index.

answered Oct 7, 2011 at 13:24

Jayanta Dey

3071 silver badge9 bronze badges

Comments

Tan · Accepted Answer · 2011-10-07 13:27:03Z

0

Store the words in an collection and count it. Then use Random ? But this depends on how many words you got in the database.

answered Oct 7, 2011 at 13:27

Tan

2,1784 gold badges35 silver badges57 bronze badges

Comments

jfrankcarr · Accepted Answer · 2011-10-07 14:09:47Z

0

Here's how I've done this. As in your example, each record in the database has a numeric ID. I get the min and max ID and build a list of random numbers for whatever number of records I want. I exclude duplicate numbers during this process. Handling this in memory with an integer list is much faster than repeated database queries. Once the list of unique random numbers is created, then the records are pulled from the database using the members of the list as the criteria.

You can add in additional checks in case your ID's aren't sequential and for other conditions.

answered Oct 7, 2011 at 14:09

jfrankcarr

4813 silver badges6 bronze badges

Collectives™ on Stack Overflow

Retrieving random data from sql database no repeat

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related