Gary King

Gary King

  • NA
  • 83
  • 209.2k

Multiple queries in a single code behind page

Sep 30 2010 9:39 AM

Hi,
Please go easy on me - I'm very new to .net development (come from classic asp background).
I have is 2 tables GROUPS and QUESTIONS and what I want to do is return the data from Groups and create a query against QUETSIONS - based on the Group_ID.
So, for example I have 2 Groups - GroupA (Group id 1)  and GroupB (Group id 2)
I then have 4 Questions - Question 1 & Question 2 (both with Group ID 1) and Question 3 & Question 4 (both with Group ID 2).
The 2 SQL commands would be "SELECT * FROM Groups" and then "SELECT * FROM Questions WHERE Group_ID = " + the current Group ID value from the first query.
Now I've tried my best to get this to work, but with little luck.
I did manage to get it working with just the 1st (Group) query, but when I then try and introduce the 2nd (Question) query, I get an error....
      "There is already an open DataReader associated with this Command which must be closed first."
Here's my working version which only returns the Groups, not the Questions.
Please could someone advise how to introduce the Questions data?
 using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page 
{
   
    protected void Page_Load(object sender, EventArgs e)
    {
        Table tbl = myTable;
        SqlDataReader drGroups,Questions;
        using (SqlConnection dbConn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ToString()))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = dbConn;
            cmd.Connection.Open();
            cmd.CommandText = "Select * from GROUPS";
            drGroups = cmd.ExecuteReader();
           
            if (drGroups.HasRows)
            {
                while (drGroups.Read())
                {
                    TableRow tr = new TableRow();
                    tbl.Rows.Add(tr);
                    TableCell Group = new TableCell();
                    Group.Text = drGroups.GetValue(1).ToString();
                    Group.Width = Unit.Percentage(100);
                    tr.Controls.Add(Group);
                }
            }
        }
       
    }
}

Many Thanks
Gary
 

Answers (9)