Fill multiple textboxes after selecting a data from DropDownList in ASP.NET

Question

I have a DropDownList that is linked to a SQL database. It currently shows a list of CardCode. I am trying to make it so that once a CardCode is selected, multiple textboxes are automatically filled (such as CardNum, CntctPerson,ListNum etc). I am able to automatically fill the "CardCode" and CardNum textbox as the value is the one selected but it show the First row in the database,I want to show the related row to the CardCode, I do not know how to fill the other rows that is related to the selected CardCode .How can i do this? Thanks In Advance

Here is my aspx.cs Code below

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();

            SqlConnection connection = new SqlConnection(my connection here);
            using (connection)
            {
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT CardCode, CardName, Address, CntctPrsn FROM OCRD", connection);

                adapter.Fill(CardCode);
               // DropDownList1.DataSource = customers;
                DropDownList1.DataTextField = "CardName";
                DropDownList1.DataValueField = "CardName";
                DropDownList1.DataBind();
            }
        }

       protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string selected = DropDownList1.SelectedItem.Value;

            SqlConnection connection = new SqlConnection(my conection here);
            using (connection)
            {
                SqlCommand theCommand = new SqlCommand("SELECT CardCode, CardName, Address, CntctPrsn FROM OCRD", 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.GetString(0);
                        this.TextBox2.Text = theReader.GetString(1);
                        this.TextBox3.Text = theReader.GetString(2);
                        // this.TextBox4.Text = theReader.GetString(3);
                        //  TextBox5.Text = theReader.GetString(4);
                        //  TextBox6.Text = theReader.GetString(5);
                        //  TextBox7.Text = theReader.GetString(6);

                    }
                    connection.Close();
                }

            }


        }

}

And also this is my .aspx code

<%@ 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" 
            DataSourceID="SqlDataSource1" DataTextField="CardCode" 
            DataValueField="CardCode" 
            onselectedindexchanged="DropDownList1_SelectedIndexChanged">
        </asp:DropDownList>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:myconnection string here %>" 
            SelectCommand="SELECT [CardCode], [CardName], [Address], [CntctPrsn] FROM [OCRD]">
        </asp:SqlDataSource>
    </h2>
    <p>
        <asp:TextBox ID="TextBox1" runat="server" ></asp:TextBox>
    </p>
    <p>
        <asp:TextBox ID="TextBox2" runat="server" ></asp:TextBox>
    </p>
    <p>
        <asp:TextBox ID="TextBox3" runat="server" ></asp:TextBox>
    </p>

    <p>
        <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
    </p>

</asp:Content>

Answer 1

Replace below code in your code behind code:

DropDownList1.DataValueField = "CardCode";

SqlCommand theCommand = new SqlCommand("SELECT CardCode, CardName, Address, CntctPrsn FROM OCRD where CardCode=@CardCode ", connection);

You are bind drop down on both side using code behind and data source so Remove Sql datasource like below:

<%@ 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>

</h2>
<p>
    <asp:TextBox ID="TextBox1" runat="server" ></asp:TextBox>
</p>
<p>
    <asp:TextBox ID="TextBox2" runat="server" ></asp:TextBox>
</p>
<p>
    <asp:TextBox ID="TextBox3" runat="server" ></asp:TextBox>
</p>

<p>
    <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
</p>

Answer 2

First, you need to store the card code in the drop down as well, not only the card name

DropDownList1.DataValueField = "CardCode";

Then, you are not specifying the WHERE filter while filling text boxes. Try this:

SqlCommand theCommand = new SqlCommand("SELECT CardCode, CardName, Address, CntctPrsn FROM OCRD WHERE CardCode = @CardCode", connection);