select questions from sql database using c#

[virusisfound]

Hi..

I am trying to create question paper generator application in C# windows application with sql server as back end.
Now I need your help to select the questions from database.
Ex: User Selection like:
Chapter selection from listbox then
select
1 mark – 5 question
2 mark -3 questions
How to retrieve questions from database?
**If user select chapter 1 and 2
And
1 mark – 5 question
And
2 mark – 3 question from both chapter
Then if should select from 1st chapter 3 question and from 2nd chapter 2 question
Or wise versa. **

thanx

[Ketsuekiame]

I'm not sure what you're asking but I'll guess at that you have a list of questions subdivided by chapters (presumably in a study book) and you want the user to select questions?

By looking at the whole problem it seems a little complex, but you need to break it down.

First forget about "Marks", this isn't anything to do with selecting your questions :)

Second, it comes down to your User Interface. I would first have them select which chapters they wish to pick questions from. You can decide how they are able to do this, but if the maximum they can select is two chapters, make sure you enforce this in your code!

Third, get a list of the questions from both chapters. How you do this depends on how you arrange your database. Your table layout might look like:

Chapter Table
---------------
ChapterId, ChapterNumber, ChapterName

Question Table
----------------
QuestionId, ChapterId, QuestionName, QuestionText


(If your answers can be checked automatically, ie. Multiple Choice, then include an answers column too)

When you perform your query, you can get a list of all the chapters, making sure that you make a record of the Ids the user selects, then you can use that Id to constrain the select statement on your Quesion Table.

Make sure you separate the Questions by Chapter in the User Interface. Once they've completed their selection, you can worry about what marks they can achieve. Take the list of questions they've selected and assign them a "top possible score" value that is equivalent to your conditions.

Then you're done :D

[virusisfound]

Thank you for reply.

Table Details :
table Name : questions
standerd_id
Subject_id
Chapter_id
question
marks

I have write some code for selection but the problem is it work for only two chapters. i want it for multiple.
I think i have to use multiple for loop. one for loop for chapters and one for marks.
but i don't know how ?
If you have better solution please help.

 string[] a = listBox1.Items.Cast<string>().ToArray(); // for selected chapters
                SqlConnection cn = new SqlConnection(s);
                cn.Open();
                DataSet ds = new DataSet();
                    int val1 = 0;
                    int val2 = 0;
                   if (one_index_flag == 1)
                {
                    val1 = one_index / 2;
                    val2 = one_index - val1;
                }
                for (int i = 0; i < listBox1.Items.Count; i++)
                {
                   if (val1 != 0)
                        {
                            SqlDataAdapter da = new SqlDataAdapter("select top(" + val1 + ") question from question where subjectid='" + lbl_sub.Text.ToString() + "'and standerdid='" + lbl_std.Text.ToString() + "'and chapterid='" + a[i] + "'" + "ORDER BY NEWID()", cn);
                            da.Fill(ds);
                            val1 = 0;
                        }
                        else if (val2 != 0)
                        {
                            SqlDataAdapter da1 = new SqlDataAdapter("select top(" + val2 + ") question from question where subjectid='" + lbl_sub.Text.ToString() + "'and standerdid='" + lbl_std.Text.ToString() + "'and chapterid='" + a[i] + "'" + "ORDER BY NEWID()", cn);
                            da1.Fill(ds);
                            val2 = 0;
                        }

                } 
                dataGridView1.DataSource = ds.Tables[0];
                dataGridView1.Columns["question"].HeaderText = "question";