How link/bind textbox, dropdown list and SQL database together on C#?-CodePudding

Basically what the task is that you search someone's name in the textbox, click search, the users with that name will populate the dropdown list.

This is when I run the code, it looks like this

SQL Server: this is the query I am try to add to the search button on my page:

SELECT Users.Forename   ' '   Users.Surname AS [Name], Users.ID
From Users
Order by [Name]

Aspx side:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Page1.aspx.cs" Inherits="SearchRecords.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <p> Search User </p>
            <p> Filter Users: <asp:TextBox ID="TextBox1" runat="server" ></asp:TextBox>
            <asp:Button ID="SearchButton1" runat="server" Text="Search" OnClick="BtnSearch_Click" />

            <br />

            <br />
            Select a user:
            <asp:DropDownList ID="DDLSelectUser1" runat="server"> </asp:DropDownList>

           &nbsp; <asp:Label ID="lblUserCount" runat="server" Text=""></asp:Label>
            
            <br /> 
            

            <div >
                <asp:GridView ID="DataGrid1" runat="server" ShowHeaderWhenEmpty="false" EmptyDataText="No data found!">

                </asp:GridView>
            </div>

            </p>

            <a href="Page1.aspx"> <p> Link to page 1 </p> </a>
        </div>
    </form>
</body>
</html>

C# side: I've got this already (this is me attempting to fetch data from the Database and bind the list of names to the dropdown list):

private void LoadIntoDropdown()
{
    using (SqlConnection connection = new SqlConnection("Data Source=DEV-WEB;Initial Catalog=Isabelle;Integrated Security=True"))
    {
        connection.Open();

        string sqlQuery = "SELECT Users.Forename   ' '   Users.Surname AS [Name], Users.ID From Users WHERE Forename like '%' @Forename '%'";

        using (SqlCommand cmd = new SqlCommand(sqlQuery, connection))
        {
            cmd.Parameters.AddWithValue("Forename", TextBox1.Text);

            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();

                adapter.Fill(dt);                  

                DDLSelectUser1.DataTextField = "[Name]";

                DDLSelectUser1.DataValueField = "Users.ID";

                DDLSelectUser1.DataSource = dt;

                DDLSelectUser1.DataBind();
            }
        }

        connection.Close(); 
    }
}

It's never working, this is what I get:

"DataBinding: 'System.Data.DataRowView' does not contain a property with the name '[Name]'."

And I don't understand why? I combined two columns from the database, the Forename and Surname columns, together and called it [Name], so it does exist.

I can't seem to find online the right answer to my question as they all are 'select from the dropdown list to fill the textbox' I want the other way around, if it's possible!

CodePudding user response：

The alias [Name] in your query becomes just Name once the database returns the resultset to the client (in this case, your program). So, when you reference that column in your code, you should do it as Name instead of [Name]

DDLSelectUser1.DataTextField = "Name";

Square bracket names and aliases are used in SQL Server queries both to specify that you want the name/alias to be exactly as written (including "invalid" characters for names like spaces e.g. [First Name]) and to differentiate such names/aliases from keywords (althought using keywords as field names is not recommended).

You can find more information in this SO question and in this Microsoft Docs' question