使用c#在asp.net中的sqlserver的下拉列表中显示数据

show data in dropdownlist from sqlserver in asp.net using c#

提问by Syed Azy

I have three dropdownlist the code below

我有三个下拉列表下面的代码

  <asp:DropDownList ID="ForumTitleList" runat="server"

                        AutoPostBack="True">
                    </asp:DropDownList>
                    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                    <asp:DropDownList ID="ForumSubTitleList" runat="server" AutoPostBack="True"
                        >
                    </asp:DropDownList>
                    &nbsp;&nbsp;&nbsp;
                    <asp:DropDownList ID="ForumSubjectTitleList" runat="server" AutoPostBack="True"
                       >
                    </asp:DropDownList>

and the code behind is

后面的代码是

enter code here 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Net;
using System.Net.Mail;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

namespace Auzine.Forums
{
    public partial class ForumIT : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)

    {
        ConfigurationFuntion();

        DropForumTitle();
        DropForumSubTitle();
        DropForumSubjectTitle();
    }


protected void DropForumTitle()
{
    if (!Page.IsPostBack)
    {

        string connection = System.Configuration.ConfigurationManager.ConnectionStrings["AuzineConnection"].ConnectionString;

        string selectSQL = "select DISTINCT ForumTitlesID,ForumTitles from ForumTtitle";
        SqlConnection con = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand(selectSQL, con);
        SqlDataReader reader;
        try
        {

            ListItem newItem = new ListItem();
            newItem.Text = "Select";
            newItem.Value = "0";
            ForumTitleList.Items.Add(newItem);
            con.Open();
            reader = cmd.ExecuteReader();



            while (reader.Read())
            {
                ListItem newItem1 = new ListItem();
                newItem1.Text = reader["ForumTitles"].ToString();
                newItem1.Value = reader["ForumTitlesID"].ToString();
                ForumTitleList.Items.Add(newItem1);



            }
            reader.Close();
            reader.Dispose();
            con.Close();
            con.Dispose();
            cmd.Dispose();


        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        //////////////////

    }
}
protected void DropForumSubjectTitle()
{
    if (Page.IsPostBack)
    {
       // ForumSubjectTitleList.Items.Clear();
        string connection = System.Configuration.ConfigurationManager.ConnectionStrings["AuzineConnection"].ConnectionString;
        SqlConnection con = new SqlConnection(connection);

        con.Open();



        SqlCommand com = new SqlCommand("select DISTINCT ForumSubjectTitle from ForumSubject where ForumSubTitlesID='" + ForumSubTitleList.SelectedValue.ToString() + "'", con);
        SqlDataReader reader = com.ExecuteReader();
        // ForumTitleList.Items.Clear();

        while (reader.Read())
        {
            ForumSubjectTitleList.Items.Add(reader[0].ToString());


        }

        reader.Close();
        con.Close();



    }

}

protected void DropForumSubTitle()
{


    if (Page.IsPostBack)
    {
        ForumSubTitleList.Items.Clear();
        string connection = System.Configuration.ConfigurationManager.ConnectionStrings["AuzineConnection"].ConnectionString;
        string selectSQL = "select DISTINCT ForumTitlesID,ForumSubTitles from ForumSubtitle where ForumTitlesID='" + ForumTitleList.SelectedValue.ToString() + "' ";
         SqlConnection con = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand(selectSQL, con);
        SqlDataReader reader;
        try
        {

            ListItem newItem = new ListItem();
            newItem.Text = "Select";
            newItem.Value = "0";
            ForumSubTitleList.Items.Add(newItem);
            con.Open();
            reader = cmd.ExecuteReader();



            while (reader.Read())
            {
                ListItem newItem1 = new ListItem();
                newItem1.Text = reader["ForumSubTitles"].ToString();
                newItem1.Value = reader["ForumTitlesID"].ToString();
                ForumSubTitleList.Items.Add(newItem1);



            }
            reader.Close();
            reader.Dispose();
            con.Close();
            con.Dispose();
            cmd.Dispose();


        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        //////////////////

    }
    }
}

DropForumTitle() for dropdown1(ForumTitleList) list one is working fine and then for dropdown2(ForumSubTitleList) I want to search according to the selected value of dropdown1(ForumTitleList) and when do that as i write the code for dropdown1(ForumTitleList) then it is not showing any thing but when change the code from if (!Page.IsPostBack) to if (Page.IsPostBack) then it shows but the selected index goes aoutomatically to 0... It display right but when select any option from dropdown2(ForumSubTitleList) then it goes to selected index 0 bydefault and for this error the dropdown3(ForumSubjectTitleList) can recieve the selected item valu and does not shows subjectlist from database ... each of dropdown list are connected with an ID if dropdown displays any thing then second dropdown = to selected value of dropdown 1 and same as dropdown3 = to the selected valu of dropdown2

DropForumTitle() 用于 dropdown1(ForumTitleList) 列表一个工作正常，然后用于 dropdown2(ForumSubTitleList) 我想根据 dropdown1(ForumTitleList) 的选定值进行搜索，当我编写 dropdown1(ForumTitleList) 的代码时，什么时候这样做没有显示任何内容，但是当将代码从 if (!Page.IsPostBack) 更改为 if (Page.IsPostBack) 时，它会显示但所选索引会自动变为 0...它显示正确，但是当从 dropdown2( ForumSubTitleList) 然后它默认转到选定的索引 0，对于此错误，dropdown3(ForumSubjectTitleList) 可以接收选定的项目值，并且不显示数据库中的主题列表...如果下拉列表显示任何内容，则每个下拉列表都与一个 ID 相关联，然后第二个下拉列表 = 到下拉列表 1 的选定值，与下拉列表 3 = 到下拉列表 2 的选定值

but I getting error with bothe dropdown2 and dropdown3

但我在 dropdown2 和 dropdown3 上都出错

In short:

简而言之：

1-dropdown2 does not stay to the value I selected: let suppose in the LIst A, b, C, and D. when I click on A it posrback and the selected value is again A;

1-dropdown2 不停留在我选择的值上：假设在列表中的 A、b、C 和 D。

2- dropdown3 can not access the selected value of dropdown2 therefore it is not showing anything...

2- dropdown3 无法访问 dropdown2 的选定值，因此它没有显示任何内容...

回答by David

In every post-back you're doing two things with each drop-down list:

在每次回传中，您对每个下拉列表都做两件事：

1. Re-populate it with data
2. Populate the next one

1. 用数据重新填充它
2. 填充下一个

That first step is what's getting rid of your selected value. It can't retain the selected value when you clear the values and add new ones.

第一步是摆脱您选择的价值。当您清除值并添加新值时，它无法保留所选值。

You need to separate these actions. For starters, let's assume you have DropDownList1and its selection should drive DropDownList2. Then Page_Loadshould only be populating DropDownList1and only when it's not a post-back. Something like this:

您需要将这些操作分开。首先，让我们假设您有DropDownList1并且它的选择应该驱动DropDownList2. 然后Page_Load应该只填充DropDownList1并且仅当它不是回发时。像这样的东西：

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
        PopulateDropDownList1();
}

To populate DropDownList2, you would respond to the SelectedIndexChangedevent of DropDownList1. Something like this:

要填充DropDownList2，您将响应 的SelectedIndexChanged事件DropDownList1。像这样的东西：

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
    var value = DropDownList1.SelectedValue;
    PopulateDropDownList2(value);
}

Keep in mind that Page_Loadgets calls on everyload of the page, even post-backs, and it's called beforeevents like SelectedIndexChanged. So if you re-populate your parent list in Page_Loadthen there will no longer be a selected value in SelectedIndexChanged.

请记住，它Page_Load会在每次加载页面时调用，甚至是回发，并且在诸如SelectedIndexChanged. 因此，如果您重新填充父列表，Page_Load则SelectedIndexChanged.

With the above scenario, the order of events would be:

在上述情况下，事件的顺序将是：

1. User loads the page.
2. Page_Loadexecutes.
3. It's not a post-back, so DropDownList1gets populated with values.
4. User selects a value in DropDownList1and triggers a post-back.
5. Page_Loadexecutes.
6. It isa post-back, so Page_Loaddoesn't do anything.
7. DropDownList1_SelectedIndexChangedexecutes.
8. DropDownList2gets populated with values.

1. 用户加载页面。
2. Page_Load执行。
3. 这不是回发，因此DropDownList1填充了值。
4. 用户选择一个值DropDownList1并触发回发。
5. Page_Load执行。
6. 这是一个回传，所以Page_Load不做任何事情。
7. DropDownList1_SelectedIndexChanged执行。
8. DropDownList2填充了值。

At this point the user can now see what they selected in DropDownList1and the new values in DropDownList2. Extending this to a third DropDownListis the same pattern. You'd create a DropDownList2_SelectedIndexChangedwhich does the same thing as DropDownList1_SelectedIndexChanged, but with the next cascading list.

此时，用户现在可以看到他们在 中选择的内容DropDownList1以及 中的新值DropDownList2。将此扩展到第三个DropDownList是相同的模式。您将创建一个DropDownList2_SelectedIndexChanged与 执行相同操作的DropDownList1_SelectedIndexChanged，但使用下一个级联列表。

回答by David

Sample code to populate:

要填充的示例代码：

Method

方法

private void Bind()
{

    var dt = YourFunctionReturningDataTable(); 
    //Assuming your table has two columns Id,Name

    dropdownlist1.datasource = dt;
    dropdownlist1.DataTextField = "Name";
    dropdownlist1.DataValueField="Id";
    dropdownlist1.DataBind();
    dropdownlist1.Items.Insert(0, new ListItem("---Select---","-1"));
}

private void Page_Load()
{


  if(!IsPostback)
  {
  Bind();   
  }
}

回答by John

I think it is best way to show all genre name in dropdown list. I used EDMX and LINQ query.

我认为这是在下拉列表中显示所有流派名称的最佳方式。我使用了 EDMX 和 LINQ 查询。

 ASPX:
===============
<asp:DropDownList ID="GenreList" runat="server" SelectMethod="GenreList_GetData" DataTextField="Name" DataValueField="Id">
    </asp:DropDownList>

    C#:
    ============
     public IEnumerable<Genre> GenreList_GetData()
        {
            using(PlanetWroxEntities myEntities= new PlanetWroxEntities())
            {
                return (from genre in myEntities.Genres
                        orderby genre.SortOrder
                        select genre).ToList();
            }
        }