基于ASP.NET的多表WHERE子句查询：通过AuthorID展示数据问题

Hey there! As someone new to ASP.NET and web design, this is a super common (and totally solvable) task. Let's walk through it step by step using basic ADO.NET— it's easier to grasp as a beginner.

First, let's assume your tables look something like this (adjust if your actual tables differ):

• Authors
  • AuthorID (int, primary key)
  • AuthorName (varchar)
  • Bio (text)
• Books
  • BookID (int, primary key)
  • AuthorID (int, foreign key to Authors)
  • BookTitle (varchar)
  • PublishYear (int)

Step 1: Set Up Your DropDownList & Display Controls in ASPX #

Add a DropDownList to your page (with auto-postback enabled to trigger selection changes) plus controls to show the author's details and their books:

<asp:DropDownList ID="ddlAuthors" runat="server" AutoPostBack="True" 
    OnSelectedIndexChanged="ddlAuthors_SelectedIndexChanged">
</asp:DropDownList>

<h3>Author Details</h3>
<asp:Label ID="lblAuthorName" runat="server" Font-Bold="True"></asp:Label>
<br/>
<asp:Label ID="lblAuthorBio" runat="server" TextMode="MultiLine" Width="400px"></asp:Label>

<h3>Books by This Author</h3>
<asp:GridView ID="gvBooks" runat="server" AutoGenerateColumns="False" CssClass="table">
    <Columns>
        <asp:BoundField DataField="BookTitle" HeaderText="Book Title" />
        <asp:BoundField DataField="PublishYear" HeaderText="Publish Year" />
    </Columns>
</asp:GridView>

Step 2: Populate the DropDownList on Page Load #

In your code-behind (.aspx.cs), fill the DropDownList with authors from your database when the page first loads:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        // Replace with your actual connection string from Web.config
        string connectionString = ConfigurationManager.ConnectionStrings["YourDBConnection"].ConnectionString;
        
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "SELECT AuthorID, AuthorName FROM Authors";
            SqlCommand cmd = new SqlCommand(query, conn);
            
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            
            // Map database fields to DropDownList properties
            ddlAuthors.DataValueField = "AuthorID";
            ddlAuthors.DataTextField = "AuthorName";
            ddlAuthors.DataSource = reader;
            ddlAuthors.DataBind();
            
            // Add a default "Select an Author" option
            ddlAuthors.Items.Insert(0, new ListItem("Select an Author", ""));
        }
    }
}

Step 3: Handle Selection Changes to Fetch & Display Data #

When the user picks an author, use the selected AuthorID to pull their details and associated books, then bind the data to your page controls:

protected void ddlAuthors_SelectedIndexChanged(object sender, EventArgs e)
{
    // Check if a valid author is selected
    if (!string.IsNullOrEmpty(ddlAuthors.SelectedValue))
    {
        int selectedAuthorID = int.Parse(ddlAuthors.SelectedValue);
        string connectionString = ConfigurationManager.ConnectionStrings["YourDBConnection"].ConnectionString;
        
        // Fetch and display author details
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string authorQuery = "SELECT AuthorName, Bio FROM Authors WHERE AuthorID = @AuthorID";
            SqlCommand cmd = new SqlCommand(authorQuery, conn);
            cmd.Parameters.AddWithValue("@AuthorID", selectedAuthorID);
            
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            
            if (reader.Read())
            {
                lblAuthorName.Text = reader["AuthorName"].ToString();
                lblAuthorBio.Text = reader["Bio"].ToString();
            }
            reader.Close();
        }
        
        // Fetch and display author's books
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string booksQuery = "SELECT BookTitle, PublishYear FROM Books WHERE AuthorID = @AuthorID";
            SqlCommand cmd = new SqlCommand(booksQuery, conn);
            cmd.Parameters.AddWithValue("@AuthorID", selectedAuthorID);
            
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            
            gvBooks.DataSource = dt;
            gvBooks.DataBind();
        }
    }
    else
    {
        // Clear all controls if no author is selected
        lblAuthorName.Text = "";
        lblAuthorBio.Text = "";
        gvBooks.DataSource = null;
        gvBooks.DataBind();
    }
}

Step 4: Add Your Database Connection String #

Don't forget to add your database connection string in Web.config (replace with your actual server and database details):

<configuration>
  <connectionStrings>
    <add name="YourDBConnection" 
         connectionString="Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DB_NAME;Integrated Security=True;" 
         providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

Quick Tips for Beginners: #

• Always use parameterized queries (like @AuthorID) to avoid SQL injection—never paste user input directly into your query!
• The using statement ensures database connections are properly closed, which keeps your app efficient and secure.
• IsPostBack prevents reloading the DropDownList every time the page refreshes, so the user's selection stays intact.

Adjust the queries and controls to match your actual table structure—if you don't have a Books table, just focus on displaying the author's core details.

内容的提问来源于stack exchange，提问作者Moose