How do I populate cascading dropdownlist from database in ASP.NET ?

See more:

C#

ASP.NET

SQL-Server

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

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

2) Exam table

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

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

4) Exam_Section table

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 :-

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:

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;
    }
}