ADO.NET issue in my application.

Question

0.00/5 (No votes)

See more:

Dear All,

I am having 3 questions and for each question 4 options. I am using Labels and Radio Buttons for the answers.

I am trying to fetch the values from these controls dynamically using ADO.NET.

But I am unable to do so, as I am not able to iterate through the tables properly. I am able to fetch only a single row for both the question as well as the choices.

I am maintaining 2 tables, 1 for the question and another for the choices.

Here is the code in the code behind file.

SqlConnection conn = new SqlConnection(ConnectionString);
        conn.Open();
        string add_questions = "select * from table1";
        string add_choices = "select * from table2";

        SqlCommand cmd = new SqlCommand(add_questions, conn);
        SqlCommand cmd1 = new SqlCommand(add_choices, conn);

        SqlDataReader read_questions = cmd.ExecuteReader();
        //SqlDataReader read_answers = cmd1.ExecuteReader();

        if(read_questions.HasRows)
        {
            read_questions.Read();
            for (int i = 0; i < 2; i++)
            
            {
                q1.Text = read_questions.GetString(i);
               //q1 is the id of the Label1. Similarly there are 2
               //other labels.    
                
            }

        }
        read_questions.Close();

        SqlDataReader read_answers = cmd1.ExecuteReader();

        if (read_answers.HasRows)
        {
            read_answers.Read();
            for (int i = 0; i < 3; i++)
            {
                RadioButton1.Text = read_answers.GetString(i);
            }
        }

Only the 1st question and the first option is displayed.

Kindly assist

Thanks and Regards

Posted 2-Oct-10 8:56am

Mukund Kallapur

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MalikRizwan · Answer 1 · 2010-10-02T18:57:00

Solution 3

your code has many problems but one main reason that is causing to show only one question and one answer is

in your for loop you are assigning values to just ONE control

like this q1.text = .... and RadioButton1.Text=....

so every time loop iterates, your code assign last value to it. so at end you will see only one question and one answer.

you need to use RadioButtonList and use this property to pull all questions.

RadioButtonList1.DataSource = reader;

Posted 2-Oct-10 18:57pm

MalikRizwan

Comments

Mukund Kallapur 3-Oct-10 6:48am

It is right thatI cab use a RadioButtonList, but what for Labels ??? How I can iterate through all the questions and then display in the labels

MalikRizwan 3-Oct-10 10:49am

use gridview

Drag a GridView on your page and set
GridView1.DataSource = reader2;

Don't forget to call databind function for both..

RadioButtonList1.DataSource = reader1;
RadioButtonList1.DataBind();
GridView1.DataSource = reader2;
GridView1.DataBind();

[no name] 3-Oct-10 16:24pm

If you want to display all questions along with all answers at the same time then you need to use nested databound controls. A Repeater will give you the most flexibility to supply a template for the questions and a RadioButtonList nested within it will display the answers related to the question

[no name] 3-Oct-10 16:27pm

MalikRizwan: Explain how a GridView will solve this issue.

MalikRizwan 4-Oct-10 7:06am

Hi Mark,
by looking on his code, i believe he just needs to show whatever he pulls from database.. He nowhere mentioned if he needs to show in parent-child view.
so instead of looping through data, i recommended him to use Gridview (considering he is newbie), GridView with take care of data population..

[no name] 4-Oct-10 8:35am

GridView is extreme overkill for this application. At most the OP needs to display ONE column. GridView comes with quite a lot of overkill for such a simple application.

MalikRizwan 4-Oct-10 9:20am

he no where mentioned he needs one column. check his query
SELECT *

Mukund Kallapur 4-Oct-10 14:51pm

Thank you all for the valuable suggestions..but I have no yet tried the above things. I ll try and let u guys know whether I am successful or no...

Not Active · Answer 2 · 2010-10-02T10:02:00

Solution 1

You have a considerable amount to learn about using ADO.NET and database design in general it seems.

In your code you are only getting one record because that is all you are asking for. You only call Read once.

using(SqlConnection conn = new SqlConnection(...))
{
  using(SqlCommand cmdQ = new SqlCommand("...", conn))
  using(SqlCommand cmdA = new SqlCommand("...", conn))
  {
    SqlDataReader readerQ = cmdQ.ExecuteReader();
    while(readerQ.Read())
    {
      ....
    }
  }
}

You should never use * in a select statement as it does not give SQL Server query engine a change to optimize the query and return stream. It also does not document your code at all. How will someone else know, or you remember in the future, what is being returned?

There should also be a parent/child relationship between the questions and answers. Then using DataRelation[^] you can query a DataSet for related records.

Posted 2-Oct-10 10:02am

Not Active

Updated 2-Oct-10 11:17am

v2

Comments

Mukund Kallapur 2-Oct-10 16:17pm

Your answer is right..But can you elaborate a bit more ?? as you have said that,I should not *, then what should be used ??

And how to iterate through al the labels ?? that here 3 labels and total 12 Radio Buttons ??

Please assist a bit more ???

Mukund Kallapur 2-Oct-10 16:57pm

And also the link in your answer no longer exists !!

[no name] 2-Oct-10 17:23pm

The link has been corrected.
You may want to look a Repeater control and DataBinding a DataSet with the proper relationships established.

What to use instead of *?? The columns names, what else!

Mukund Kallapur 2-Oct-10 17:42pm

Can u pls give a brief syntax or an example of the Repeater and Data Binding as I have not yet used that control !!!

[no name] 2-Oct-10 18:00pm

Look here http://www.codeproject.com/KB/aspnet/AspNetNestedRepeaters.aspx

In the future though you must show some initiative and look it up on your own. We are here to help not do the work for you. A simple search on ASP.NET and Repeater find 281,000 results.

Areff · Answer 3 · 2010-10-02T10:59:00

1. There is no relation between this tables (parent child relation).
2. you must use SqlDataTable and SqlAdapter to fetch data , it is a much bettter .

SqlDataConnection cnn = new .....;
SqlCommand quesCmnd = cnn.CreateCommand();
SqlCommand answrCmnd = cnn.CreateCommand();

quesCmnd.ComandText = "select * from table1";       
answrCmnd.CommandText = "select * from table2";

DataTable quesDt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(quesCmnd);
adapter.Fill(quesDt);

DataTable answrDt = new DataTable();
adapter = new SqlDataAdapter(answrCmnd);
adapter.Fill(answrDt);

foreach(DataRow dr in quesDt.Rows)
{
// do some operation you want 
//dr["ColumnName"];
//dr[ColumnIndex];
}

you can do foreach loop on answrDt like quesDt instead of DataReader.
but important thing in here is that your tables have not correct relation to each other.