Displaying a random row from MS Access database
Hello everyone, I'm having problems getting a random row from a table in MS Access. I need to be able to call up a random row in the database, right now it just goes down the row in order and I have no idea how to make it do that randomly. quistionID should the number I need to make random, then from that I should be able to pull the info I need according to each random number.
This is used to find a random number, I want to be able to use this random number to call a random ID from the Access table
Code Snippet
//Defined above all of the code
intquestionNum = 0;
inttotalQuestions = 0;int randomID = 0;intNumberOfQuestions = 0;
privateint RandomNumber(int min,int max)
{
Random random =newRandom();
return random.Next(min, max);
}
This is finding the max number. The max number is the number of rows in the table.
Code Snippet
private
void FindMax(){
IEnumerable questions = SelectRows("Select * From Questions");foreach (DataRowView questionin questions){
NumberOfQuestions++;
}
totalQuestions = NumberOfQuestions;
randomID = RandomNumber(1, totalQuestions);
}
This is where each row is read from the database and then formatted properly to show up organized in IE7
Code Snippet
private
void ReadQuestionsIntoTable(){
FindMax();
NumberOfQuestions = 0;
IEnumerable questions = SelectRows("Select * From Questions"); // go through every row in the questions table
// and place each question in the Table Web Controlforeach (DataRowView questionin questions){
questionNum++;
// create a row for the question and read it from the databaseTableRow tr =newTableRow();Table1.Rows.Add(tr);
TableCell aCell =newTableCell();// get the text for the question and stick it in the cellaCell.Text =
"<b>" + questionNum +".</b> " + question["QuestionText"].ToString() ;tr.Cells.Add(aCell);
string questionNumber =String.Format("Answer{0}", question["QuestionID"].ToString());Session[questionNumber] = question[
"Answer"].ToString();// create a row for the choices and read from the database
int count = 0;// add a blank row to pad between question and choices
TableRow blankRow =newTableRow();TableCell cellPad =newTableCell();cellPad.BorderWidth = 5;
blankRow.Cells.Add(cellPad);
Table1.Rows.Add(blankRow);
}
Thank you in advance, if I didn't give enough info, please let me know.
If you just want a random row you can use the Random number function to generate the questionID value and then specify the value in your SELECT statement criteria:"SELECT * FROM Questions WHERE questionID = " + RandomNumber();
You just need to determine the maximum questionID value (using the SQL MAX keyword) so that you can pass it to the RandomNumber function.
That's a pretty simple solution. However bear in mind that not every ID value below the maximum ID value will exist. Not only could rows be deleted, but also Sql Server doesn't use continous ID values. Insert all the rows in one go and you'll get continuous values, otherwise you generally won't.
So basically you'd need to do it in a loop, with different random numbers, until you get a row back.
You can instead use this Sql:
Code Snippet
SELECT
TOP 1 * FROM MyTable ORDER
BY NEWID() I tried it on a large table and it seems reasonably quick.
Sean
Thanks for the help guys, I think in the future I'll chose SQL Server instead of MS Access for my database. The first solution given only showed one row at a time when I needed all of the rows shown. It also would sometimes show a blank, I'm assuming there was something wonky going on with how it was choosing the numbers. I put in a simple for statement to show all the rows, that fixed the blank outputs as well.
Is there anything I can add to my current sql to prevent a number being used more than once? I've noticed it will display the same row more than once instead of all the rows one time. If not I'll have to do some fancy work with my RandomNumber function.
My Current SQL
("Select * From Questions WHERE questionID ="
+ RandomNumber(1, totalQuestions)); Thanks again for the help.
OK, let's clarify what you need to do. Do you want to display all rows, but in a random order? It sounded from your description that you just wanted to grab a random row each time.
Sorry, I missed that it was for MS Access, as you've probably realised my solution only works for Sql Server.
Here's an MS knowledge base article on how to do exactly what you're talking about (I think).
Yes Sql Server is far more sophisticated, scalable etc than Access, so given the choice it's definitely the one to go for.
Sean
 Paul P Clement IV wrote: |
| OK, let's clarify what you need to do. Do you want to display all rows, but in a random order? It sounded from your description that you just wanted to grab a random row each time. | |
I'm sorry for the confusion: I want to display all rows in a random order without any of the rows being displayed more than once.
And thanks for finding that article, I might give that a shot sometime soon.
I am tryin to do similar thig n but using MS Access instead of MS SQL Server. I used this command
<
SELECT TOP 1 userID FROM Users Order by Rnd(userID)
>
Must of the time, it returns same number..
What could be the problem?
