如何在asp.net中使用下拉列表筛选查询

本文关键字:下拉列表 筛选 查询 asp net | 更新日期: 2023-09-27 18:24:35

我使用sql查询从一个下拉列表中获取数据,并希望通过筛选数据更改第二个下拉列表。

好心帮忙,提前感谢

这是我的aspx.cs代码

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.Data.SqlClient;

namespace StackOver
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                LoadOptions();
            }
        }
        protected void LoadOptions()
        {
            DataTable CardCode = new DataTable();
            string id, name, newName, name2;
            SqlConnection connection = new SqlConnection("Data Source=adfsadf;Initial Catalog=TestDatabse;Persist Security Info=True;User ID=asd;Password=asdf");
            using (connection)
            {
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT T1.CardCode , T1.CardName, T2.OpprId,T1.CntctPrsn, T2.CprCode,T2.MaxSumLoc  FROM OCRD T1 left  join OOPR T2 on T1.CardCode=T2.CardCode" , connection);
                adapter.Fill(CardCode);
                if (CardCode.Rows.Count > 0)
                {
                    for (int i = 0; i < CardCode.Rows.Count; i++)
                    {
                        id = CardCode.Rows[i]["CardCode"].ToString();
                        name = CardCode.Rows[i]["CardName"].ToString();
                        newName = id + " ---- " + name;
                        //name2 = id;
                        DropDownList1.Items.Add(new ListItem(newName, id));

                        name2 = CardCode.Rows[i]["CntctPrsn"].ToString();
                        DropDownList2.Items.Add(new ListItem(name2, name2));
                    }
                }
                //adapter.Fill(CardCode);
                //DropDownList1.DataValueField = "CardCode";
                //DropDownList1.DataTextField = "CardCode";
                //DropDownList1.DataBind();
            }
        }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {

            string selected = DropDownList1.SelectedItem.Value;
            SqlConnection connection = new SqlConnection("Data Source=mydtasrc;Initial Catalog=TestDatabs;Persist Security Info=True;User ID=asf;Password=asdfgh");
            using (connection)
            {
                // SqlCommand theCommand = new SqlCommand("SELECT CardCode, CardName, OpprId, CprCode,MaxSumLoc  FROM OOPR WHERE CardCode = @CardCode", connection);
                SqlCommand theCommand = new SqlCommand("SELECT T1.CardCode , T1.CardName, T2.OpprId, T1.CntctPrsn,T2.CprCode  FROM OCRD T1 left  join OOPR T2 on T1.CardCode=T2.CardCode  where T1.CardCode=@CardCode", connection);

                connection.Open();
                theCommand.Parameters.AddWithValue("@CardCode", selected);
                theCommand.CommandType = CommandType.Text;
                SqlDataReader theReader = theCommand.ExecuteReader();
                if (theReader.Read())
                {
                    // Get the first row
                    // theReader.Read();
                    // Set the text box values
                    this.TextBox1.Text = theReader["CardCode"].ToString();
                    this.TextBox2.Text = theReader["CardName"].ToString();
                    this.TextBox5.Text = theReader["CprCode"].ToString();
                    this.TextBox3.Text = theReader["OpprId"].ToString();
                    this.DropDownList2.Text = theReader["CntctPrsn"].ToString();
                    //   this.TextBox3 = reader.IsDBNull(TextBox3Index) ? null : reader.GetInt32(TextBox3Index)
                    // GenreID = reader.IsDBNull(genreIDIndex) ? null : reader.GetInt32(genreIDIndex)
                    // this.TextBox4.Text = theReader.GetString(3);
                    //  TextBox5.Text = theReader.GetString(4);
                    //  TextBox6.Text = theReader.GetString(5);
                    //  TextBox7.Text = theReader.GetString(6);
                }
                connection.Close();
            }
        }

        public object TextBox3Index { get; set; }

        //       protected void Button1_Click(object sender, EventArgs e)
        //    {
        //        SqlConnection connection = new SqlConnection();
        //        connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["TestDataBaseConnectionString2"].ConnectionString;
        //        connection.Open();
        //        SqlCommand cmd = new SqlCommand();
        //        cmd.CommandText = "select * from  OOPR";
        //        cmd.Connection = connection;
        //        SqlDataAdapter da = new SqlDataAdapter();
        //        da.SelectCommand = cmd;
        //        DataSet ds = new DataSet();
        //        da.Fill(ds, " OOPR");
        //        SqlCommandBuilder cb = new SqlCommandBuilder(da);
        //        DataRow drow = ds.Tables["OOPR"].NewRow();
        //        drow["CardCode"] = TextBox1.Text;
        //        drow["CardName"] = TextBox2.Text;
        //        drow["OpprId"] = TextBox3.Text;
        //        drow["CprCode"] = TextBox4.Text;
        //        drow["MaxSumLoc"] = TextBox5.Text;
        //        ds.Tables["OOPR"].Rows.Add(drow);
        //        da.Update(ds, " OOPR ");
        //        string script = @"<script language=""javascript"">
        //        alert('Information have been Saved Successfully.......!!!!!.');
        //       </script>;";
        //        Page.ClientScript.RegisterStartupScript(this.GetType(), "myJScript1", script);
        //    }
        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection connection = new SqlConnection("Data Source=192.168.0.65;Initial Catalog=TestDataBase;Persist Security Info=True;User ID=sa;Password=mushko");
            using (connection)
            {
                // connection.Open();
                SqlCommand insert = new SqlCommand("Insert into  OOPR(CardCode, CardName, OpprId, CprCode,MaxSumLoc) values (@TextBox1,@TextBox2,@TextBox3,@TextBox4,@TextBox5  )", connection); //('" + TextBox1.Text + "','" + TextBox2.Text + "','" + DropDownList1.Text + "','" + TextBox4.Text + "')", connection);
                insert.Parameters.AddWithValue("@TextBox1", TextBox1.Text);
                insert.Parameters.AddWithValue("@TextBox2", TextBox2.Text);
                insert.Parameters.AddWithValue("@TextBox3", TextBox3.Text);
                insert.Parameters.AddWithValue("@TextBox4", TextBox4.Text);
                insert.Parameters.AddWithValue("@TextBox5", TextBox4.Text);
                connection.Open();
                // connection.CommandType=CommandType.Text;
                // connection.commandType=CommandType.Text;
                //  try
                //  {
                insert.ExecuteNonQuery();
                connection.Close();
                //   }
                //catch
                //{
                //    TextBox5.Text = "Error when saving on database";
                //    connection.Close();
                //}
                //TextBox1.Text="";
                //TextBox2.Text = "";
                //TextBox3.Text = "";
                //TextBox4.Text="";
            }
        }
        protected void Button2_Click(object sender, EventArgs e)
        {
            this.ClientScript.RegisterClientScriptBlock(this.GetType(), "Close", "window.close()", true);
            //    this.close();
        }
        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
        }

        }
    }

这是apsx代码

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="StackOver._Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h2>
        Welcome to ASP.NET!
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" 
        onselectedindexchanged="DropDownList1_SelectedIndexChanged">
    </asp:DropDownList>
        <asp:DropDownList ID="DropDownList2" runat="server" 
            onselectedindexchanged="DropDownList2_SelectedIndexChanged">
        </asp:DropDownList>
    </h2>
           <br />
    <p>
    Business Partner Code :&nbsp; 
        <asp:TextBox ID="TextBox1" runat="server" Width="192px" ></asp:TextBox>
    </p>
    <p>
    Business Partner Name :
        <asp:TextBox ID="TextBox2" runat="server" Width="192px" ></asp:TextBox>
    </p>
    <p>
     Opportunity No. :&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="TextBox3" runat="server" Width="196px" ></asp:TextBox>
    </p>
    <p>
    Contact Person Name :&nbsp; 
        <asp:TextBox ID="TextBox4" runat="server" Width="196px" ></asp:TextBox>
    </p>
     <p>
      Total Amount Invoiced:&nbsp; 
        <asp:TextBox ID="TextBox5" runat="server" Width="193px" ></asp:TextBox>
    </p>
     <p>
    Business partner Territory:&nbsp; 
        <asp:TextBox ID="TextBox6" runat="server" Width="174px" ></asp:TextBox>
    </p>
     <p>
      Sales Employee:&nbsp; 
        <asp:TextBox ID="TextBox7" runat="server" Width="235px" ></asp:TextBox>
    </p>
    <p>
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
         <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Add" 
            Width="140px" />
        &nbsp;&nbsp; &nbsp;
        <asp:Button ID="Button2" runat="server" Text="Cancel" Width="149px" 
            onclick="Button2_Click" />
    </p>

</asp:Content>

如何在asp.net中使用下拉列表筛选查询

将过滤后的客户放入DataTable(dt)中,并像这样填充:

DropDownList2.DataTextField = "CustomerName";
DropDownList2.DataValueField = "CustomerID";
DropDownList2.DataSource = dt;
DropDownList2.DataBind();

一旦list1触发selectedindexchanged事件,list2将更改其值。

        protected void LoadOptions()
        {
            DataTable CardCode = new DataTable();
            string id, name, newName, name2;
            SqlConnection connection = new SqlConnection("Data Source=adfsadf;Initial Catalog=TestDatabse;Persist Security Info=True;User ID=asd;Password=asdf");
            using (connection)
            {
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT T1.CardCode , T1.CardName, T2.OpprId,T1.CntctPrsn, T2.CprCode,T2.MaxSumLoc  FROM OCRD T1 left  join OOPR T2 on T1.CardCode=T2.CardCode" , connection);
                adapter.Fill(CardCode);
                if (CardCode.Rows.Count > 0)
                {
                    for (int i = 0; i < CardCode.Rows.Count; i++)
                    {
                        id = CardCode.Rows[i]["CardCode"].ToString();
                        name = CardCode.Rows[i]["CardName"].ToString();
                        newName = id + " ---- " + name;
                        //name2 = id;
                        DropDownList1.Items.Add(new ListItem(newName, id));
                        //*******HERE*****//
                        DropDownList2.DataSource = CardCode;
                        DropDownList2.DataBind();
                        DropDownList2.DataValueField = "CardCode";
                        DropDownList2.DataTextField = "CntctPrsn";
                        //*******HERE*****//
                    }
                }
            }
        }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {

            string selected = DropDownList1.SelectedItem.Value;
            SqlConnection connection = new SqlConnection("Data Source=mydtasrc;Initial Catalog=TestDatabs;Persist Security Info=True;User ID=asf;Password=asdfgh");
            using (connection)
            {
                // SqlCommand theCommand = new SqlCommand("SELECT CardCode, CardName, OpprId, CprCode,MaxSumLoc  FROM OOPR WHERE CardCode = @CardCode", connection);
                SqlCommand theCommand = new SqlCommand("SELECT T1.CardCode , T1.CardName, T2.OpprId, T1.CntctPrsn,T2.CprCode  FROM OCRD T1 left  join OOPR T2 on T1.CardCode=T2.CardCode  where T1.CardCode=@CardCode", connection);

                connection.Open();
                theCommand.Parameters.AddWithValue("@CardCode", selected);
                theCommand.CommandType = CommandType.Text;
                SqlDataReader theReader = theCommand.ExecuteReader();
                if (theReader.Read())
                {
                    this.TextBox1.Text = theReader["CardCode"].ToString();
                    this.TextBox2.Text = theReader["CardName"].ToString();
                    this.TextBox5.Text = theReader["CprCode"].ToString();
                    this.TextBox3.Text = theReader["OpprId"].ToString();

                    //*******AND HERE*****//
                    this.DropDownList2.SelectedValue = selected; 
                    //*******AND HERE*****//
                }
                connection.Close();
            }
        }