help with a dataset and a loop

A

andycharger

Well-known member
Joined
Apr 2, 2003
Messages
152
HELP!
im in some trouble here....
Im trying to write a set of questions and answers from a database into a datagrid.
I have a list of questions and I want to select a random 10 from this list.

This is fine. I write the following
"Select top 10 * from QUESTIONS order by newid()"

This gives me 10 random questions. However, I now need the answers.

I need to build some form of loop to go through the answers table, pull out the matching answers and put them in a dataset or some similar way to hold data. I then need to bind this data to a datagrid so I can display it in asp.net.

Any idea as to how I will do this?
 
Sounds like a cunning plan to me, but you could also use a join in your select statment to pull the data from both tables. The must have a common filed to allow you to do this. Me I would use 1 table.
 
why not one table?

Because I am ordering by newid. I.e. I am taking the random 10 rows. Using 1 table, I might only pull one question answer combination out of the database!!!!
I need 4 question/answer combos for each question.
 
If newid is common to both tables then you can still use the join in your select statment.

You could also use a dataview for the data in your 2nd table and setup a Filter which is a combination of all your newid in table 1. The 2nd table/grid just needs to use the datview as the datasource and you will have what yu want in the 2nd grid.
 
If youre using SQL Server, Id say you probably want a temp table. You would/should fill a temp table with your 10 random questions, filling in the primary key (your Question table should have some unique identifier, or Primary Key). Then do a SELECT from the Questions table joining to the temp table to get your questions. Follow that with a second query (in the same stored procedure or dynamic SQL) which SELECTs from the Answers table using the QuestionID from the temp table.

If youre using Access (this would also work for SQL Server), fill a DataSet with the data returned from your First Query. Then loop through each row returned and run a separate query to get the Answers for that QuestionID (whatever the Primary Key column is called). You can merge the returned rows into the original DataSet, in an Answers table. After all data is in one DataSet (two tables), you can add a relationship to hook up the answers to the questions. Or, you may just need a DataView with the proper filter - depends on how youre going to get at the answers for each question and what youre doing with them.

Regardless of your solutions, youre going to HAVE to have a Primary Key of some kind on the Questions table and have a matching Foreign Key on the Answers table.

I would definitely NOT use a single join to return questions and answers as youd be returning the same questions 4 times (once per answer) - almost always a no-no when using a relational database.

-ner
 
Back
Top