none
getting error when accessing multiple tables RRS feed

  • Question

  • hi,

      i am designing an application using asp.net, in which i have following sql tables.

     

        Question(IdQuestion,QName,QType,Answer) , idquestion is IDENTITY & PrimaryKey

       Answer_Detail(idAnswerDetail,idQuestion,Options),dAnswerDetail is IDENTITY

      Category(idCategory,category),  IdCategory is IDENTITY & PrimaryKey

      Category_Detail(idCategoryDetail,idCategory,idQuestion),idCategoryDetail is IDENTITY.

     

    i have on drop down list to select categories. if i select a particular category, all the questions related to that particular category should be shown with the help of grid view for this i had written the following stored procedure.

     

    CREATE PROC useSelectCategory(@CategoryName Varchar(50))
    As
    Begin
      SELECT Qname FROM Question WHERE idquestion=(SELECT idquestion FROM Category_Details WHERE idCategory=(SELECT idCategory FROM Category WHERE CategoryName=@CategoryName))
     RETURN SCOPE_IDENTITY()
    END
     

    and my .net code is

     SqlConnection cn = new SqlConnection("user id=sa;password=sa123;database=questionbank;data source=MERCURY-987BECC");

    SqlCommand cmd = new SqlCommand("useSelectCategory", cn);

    cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@CategoryName",SqlDbType.VarChar,50));
            cmd.Parameters["@CategoryName"].Value = DropDownList1.SelectedValue;

            cn.Open();
            cmd.ExecuteNonQuery();    
            cn.Close();

     

    but i get an eror saying:  "

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression "

    how can i get the questions from Question table when i select a Category.............
    Tuesday, July 8, 2008 10:31 AM

All replies

  • The error occurs because your sub query(s) are returning multiple rows. See the following article for an explanation:

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/31/771.aspx

    If you are attempting to create a one-to-one or many query then you need to construct a join instead.

    http://www.devx.com/dbzone/Article/17403/1954


    Tuesday, July 8, 2008 12:23 PM
  • As already explained, the basic problem is you are using ExecuteNonQuery, when you have a Select and then a return value.

     

    So you either use ExecuteScalar (if you know you will have always 1 result), ExecuteReader (for processing the select results), or you modify your store procedure to not use a select (but set or other instruction that doesn't produce a recordset).

     

    Taking the second approach, your code would see like:

     

    Code Snippet

    SqlConnection cn = new SqlConnection("user id=sa;password=sa123;database=questionbank;data source=MERCURY-987BECC");

    SqlCommand cmd = new SqlCommand("useSelectCategory", cn);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@CategoryName",SqlDbType.VarChar,50));
    cmd.Parameters["@CategoryName"].Value = DropDownList1.SelectedValue;

    cn.Open();

    String QuestionList = "";

    String QuestionSeparator = "";


    SqlReader rdr = cmd.ExecuteReader();     

            while (rdr.Read())
            {
                QuestionList += QuestionSeparator + rdr[0];

                QuestionSeparator = ", ";
            }


    cn.Close();

     

     

    Bruno

    Sunday, July 13, 2008 4:05 AM