如何从ASP.NET中的数据库填充级联下拉列表？ [英] How do I populate cascading dropdownlist from database in ASP.NET ?
问题描述
我正在尝试从SQL Server数据库填充ASP.Net中的Cascading DropDownList，这是DropDownLists，它依赖于前一个或父DropDownList来获取数据。
这里在这个例子中，DropDownLists填充了Course，Exam State和Section数据。
Cascading DropDownList表示一系列依赖的DropDownLists，其中一个DropDownList依赖于父级或之前的DropDownList和基于用户选择的项目填充。在很多情况下，我们需要使用Cascading DropDownLists，因为我有这里
课程 - 考试 - 部分
部分取决于考试，而考试依赖于课程，因此我们需要一系列级联DropDownList。
For我创建了四张桌子： -
1）课程表
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）考试表
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])
);
< b> 3）章节表
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表
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
我收到一个不明确的列名错误。我无法弄清楚为什么。我想我在sql查询中犯了错误。任何帮助将不胜感激。
我尝试过：
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;
}
}
推荐答案
select SectionId, SectionName, ExamId from Section s INNER JOIN Exam_Section es ON s.SectionId=es.SectionId
您正在加入两个表，Section和Exam_Section，并且都有一个SectionId字段，所以当您选择SectionId时，您指的是哪个sectionId？部分表中的那个，或者Exam_section表？这就是错误告诉你的。当你在多个表中拥有相同的字段时，你必须通过在表格名称或表别名前加上明确的含义来明确你的意思
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
select s.SectionId, SectionName, ExamId from Section s INNER JOIN Exam_Section es ON s.SectionId=es.SectionId
查询
the query
String strQuery = "select SectionId, SectionName, ExamId from Section s INNER JOIN Exam_Section es ON s.SectionId=es.SectionId " + "where ExamId=@ExamId";
将是问题所在。 Table部分和Exam_section都有一个名为SectionID的列。
更好地将查询更改为：
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";
这篇关于如何从ASP.NET中的数据库填充级联下拉列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
