pulling data from a SQL database table

Question

I am pulling 25 random numbers between the range of 1-35 questions stored in SQL based on there ID number (1,2..20 etc) this is so a user taking the test will be given random questions from the question pool in SQL I am trying to get the SQL parameter to increment within the loop variable "a" is being used as the key to enter values into the respected arrays.

Sample code is below

    protected void Question_Fetch(int[] Mixed_Questions) 
    //this array is loaded with my mixed numbers between 1-35(no duplicates) the array length //is 25 
    {
        Question_String_list = new string[25];
        Question_Answers = new string[25];
        Num_Answers = new string[25];
        Types_Of_Question = new string[25];
        User_Answers = new string[25];
        isLocked = new string[25];

        using (SqlCommand Comd = new SqlCommand("SELECT Question_String, Question_Answer, Type_Of_Question, Possible_Answers FROM Question_Pool WHERE Question_ID = @Question_ID", Conn))
        {
            SqlParameter IDParam = Comd.Parameters.Add("@Question_ID", SqlDbType.Int);
            for (int a = 0; a < Mixed_Questions.Length; a++)
            {
                int Random_Number = Mixed_Questions[a];
                Comd.Parameters.AddWithValue("@Question_ID", Random_Number);
                Conn.Open();                                        
                SqlDataReader rdr = Comd.ExecuteReader();
                if (rdr.Read())
                {
                    IDParam = Mixed_Questions[a];
                    //Random_Number = Mixed_Questions[a];
                    //Comd.Parameters.AddWithValue("@Question_ID", Random_Number);
                    Question_String_list[a] = rdr.GetValue(0).ToString();
                    Question_Answers[a] = rdr.GetValue(1).ToString();
                    Types_Of_Question[a] = rdr.GetValue(2).ToString();
                    Num_Answers[a] = rdr.GetValue(3).ToString();
                    Conn.Close();
                }
            }
        }
        Answer_Controls();
        Init_Test_Details();
    }

Are you really using classic ASP? Also why are you using parallel arrays instead of an array of objects? — Matti Virkkunen
– Matti Virkkunen, Commented May 17, 2012 at 18:42
no sorry the classic asp tag is a mistake. this is still early in this application development and I am a co op student I will Look into array of objects just seemed easier at the time when I was coding any assistance you can provide will be greatly appreciated. — Adam Pacey
– Adam Pacey, Commented May 17, 2012 at 18:46

Paul Stovell · Accepted Answer · 2012-05-17 19:02:04Z

2

A better way to pull 35 random questions might be to do something like this:

select top 35 * from Question_Pool order by (newid())

This effectively randomises the questions before taking 35 of them. This way you can do it all in a single query rather than 35 queries.

answered May 17, 2012 at 19:02

Paul Stovell

32.8k16 gold badges83 silver badges110 bronze badges

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

2 Comments

mellodev Over a year ago

This is how I would do it as well. Also, If the user has the ability to take the "test" multiple times, you might want to include a where clause to ensure the user always sees questions they have never been asked before: select top 35 * from Question_Pool where QuestionId not in (select QuestionId from Answers where AnsweredByUserId=@UserId) order by (newid())

Adam Pacey Over a year ago

Great Thank You very much I will give this a try. It works perfectly!! @Paul Stovell Thank you again I have spent far too much time today on this.

Collectives™ on Stack Overflow

pulling data from a SQL database table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related