Click here to Skip to main content
14,872,052 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am trying to populate Cascading DropDownList in ASP.Net from SQL Server Database that is DropDownLists which depend on the previous or parent DropDownList for the data.

Here in this example the DropDownLists are populated with Course, Exam State and Section data.

Cascading DropDownList means a series of dependent DropDownLists where one DropDownList is dependent on the parent or previous DropDownList and is populated based on the item selected by the user. On many occasions we need to make use of Cascading DropDownLists as I have here

Course - Exam - Section

Section is dependent on Exam and in turn Exam is dependent on the Course thus we need a series of Cascading DropDownList here.

For this I have created four tables :-

1) Course table

SQL
CREATE TABLE [dbo].[Course] (
    [CourseId]   INT  IDENTITY (1, 1) NOT NULL,
    [CourseName] TEXT NOT NULL,
    PRIMARY KEY CLUSTERED ([CourseId] ASC)
);


2) Exam table

SQL
CREATE TABLE [dbo].[Exam] (
    [ExamId]       INT          IDENTITY (1, 1) NOT NULL,
    [Title]        VARCHAR (50) NULL,
    [ExamName]     TEXT         NOT NULL,
    [CourseId]     INT          NOT NULL,
    [TotalMarks]   BIGINT       NOT NULL,
    [NegativeMark] INT          NOT NULL,
    [PositiveMark] INT          NOT NULL,
    [TotalTime]    VARCHAR (50) NOT NULL,
    [NoOfQues]     INT          NOT NULL,
    PRIMARY KEY CLUSTERED ([ExamId] ASC),
    CONSTRAINT [FK_Exam_Course] FOREIGN KEY ([CourseId]) REFERENCES [dbo].[Course] ([CourseId])
);


3) Section table

SQL
CREATE TABLE [dbo].[Section] (
    [SectionId]   INT  IDENTITY (1, 1) NOT NULL,
    [SectionName] TEXT NOT NULL,
    PRIMARY KEY CLUSTERED ([SectionId] ASC)
);


4) Exam_Section table

SQL
CREATE TABLE [dbo].[Exam_Section] (
    [Exam_SectionId] INT IDENTITY (1, 1) NOT NULL,
    [ExamId]         INT NOT NULL,
    [SectionId]      INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Exam_SectionId] ASC),
    CONSTRAINT [FK_Exam_Section_Section] FOREIGN KEY ([SectionId]) REFERENCES [dbo].[Section] ([SectionId]),
    CONSTRAINT [FK_Exam_Section_Exam] FOREIGN KEY ([ExamId]) REFERENCES [dbo].[Exam] ([ExamId])
);


I get below error whenever I select item from second dropdownlist :-

C#
Exception Details: System.Data.SqlClient.SqlException: Ambiguous column name 'SectionId'.

Source Error: 


Line 127:        catch (Exception ex)
Line 128:        {
Line 129:            throw ex;
Line 130:        }
Line 131:        finally

Source File: e:\Way2Success\Adding questions\again.aspx.cs    Line: 129 



I get an Ambiguous column name error . I can't figure out why.I think I am making mistake in sql query. Any help would be greatly appreciated.

What I have tried:

C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public partial class Adding_questions_again : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ddlCourse.AppendDataBoundItems = true;
            String strConnString = ConfigurationManager
                .ConnectionStrings["ConnectionString"].ConnectionString;
            String strQuery = "select * from Course";
            SqlConnection con = new SqlConnection(strConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strQuery;
            cmd.Connection = con;
            try
            {
                con.Open();
                ddlCourse.DataSource = cmd.ExecuteReader();
                ddlCourse.DataTextField = "CourseName";
                ddlCourse.DataValueField = "CourseId";
                ddlCourse.DataBind();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
    }

    protected void ddlCourse_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlExam.Items.Clear();
        ddlExam.Items.Add(new ListItem("--Select Exam--", ""));
        ddlSection.Items.Clear();
        ddlSection.Items.Add(new ListItem("--Select Section--", ""));

        ddlExam.AppendDataBoundItems = true;
        String strConnString = ConfigurationManager
            .ConnectionStrings["ConnectionString"].ConnectionString;
        String strQuery = "select ExamId, ExamName from Exam " +
                           "where CourseId=@CourseId";
        SqlConnection con = new SqlConnection(strConnString);
        SqlCommand cmd = new SqlCommand();
        cmd.Parameters.AddWithValue("@CourseId",
            ddlCourse.SelectedItem.Value);
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strQuery;
        cmd.Connection = con;
        try
        {
            con.Open();
            ddlExam.DataSource = cmd.ExecuteReader();
            ddlExam.DataTextField = "ExamName";
            ddlExam.DataValueField = "ExamId";
            ddlExam.DataBind();
            if (ddlExam.Items.Count > 1)
            {
                ddlExam.Enabled = true;
            }
            else
            {
                ddlExam.Enabled = false;
                ddlSection.Enabled = false;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }


    protected void ddlExam_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlSection.Items.Clear();
        ddlSection.Items.Add(new ListItem("--Select Section--", ""));
        ddlSection.AppendDataBoundItems = true;
        String strConnString = ConfigurationManager
                   .ConnectionStrings["ConnectionString"].ConnectionString;
        String strQuery = "select SectionId, SectionName, ExamId from Section s INNER JOIN Exam_Section es ON s.SectionId=es.SectionId " 
            + "where ExamId=@ExamId";
        SqlConnection con = new SqlConnection(strConnString);
        SqlCommand cmd = new SqlCommand();
        cmd.Parameters.AddWithValue("@ExamId",
                              ddlExam.SelectedItem.Value);
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strQuery;
        cmd.Connection = con;
        try
        {
            con.Open();
            ddlSection.DataSource = cmd.ExecuteReader();
            ddlSection.DataTextField = "SectionName";
            ddlSection.DataValueField = "SectionId";
            ddlSection.DataBind();
            if (ddlSection.Items.Count > 1)
            {
                ddlSection.Enabled = true;
            }
            else
            {
                ddlSection.Enabled = false;
            }

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }



    protected void ddlSection_SelectedIndexChanged(object sender, EventArgs e)
    {
        lblResults.Text = "You Selected " +
                          ddlCourse.SelectedItem.Text + " -----> " +
                          ddlExam.SelectedItem.Text + " -----> " +
                          ddlSection.SelectedItem.Text;
    }
}
Posted
Updated 12-Apr-16 23:18pm

SQL
select SectionId, SectionName, ExamId from Section s INNER JOIN Exam_Section es ON s.SectionId=es.SectionId


You are joining two tables, Section and Exam_Section and both have a SectionId field so when you "select SectionId" which sectionId are you referring to? The one in the section table, or the Exam_section table? That is what the error is telling you. When you have the same field in multiple tables you have to be explicit about which one you mean by prefixing it with the table name or table alias

SQL
select s.SectionId, SectionName, ExamId from Section s INNER JOIN Exam_Section es ON s.SectionId=es.SectionId 
   
Comments
Member 12170781 13-Apr-16 5:28am
   
Thank you.My problem is solved
the query
String strQuery = "select SectionId, SectionName, ExamId from Section s INNER JOIN Exam_Section es ON s.SectionId=es.SectionId " + "where ExamId=@ExamId";

will be the problem. Both Table section and Exam_section have a column named SectionID.
Better change the query to:
String strQuery = "select s.SectionId, s.SectionName, es.ExamId from Section s INNER JOIN Exam_Section es ON s.SectionId=es.SectionId " + "where es.ExamId=@ExamId";
   
v2
Comments
Member 12170781 13-Apr-16 5:28am
   
Thank you so much

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900