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

Question

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 class="DataGrid">
                <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!

I always get a build error saying "DataBinding: 'System.Data.DataRowView' does not contain a property with the name '[Name]'." — LoaFia02, Jun 17 '22 at 17:27
I would suggest looking into using the Entity Framework for a project like this. As for your question itself, its a little unclear what you're trying to achieve. We need more information about how your database is set up. Can you put the output of Describe on that table into your question so we understand what your database looks like? — Ethan, Jun 17 '22 at 17:42
Are you able to insert data to the database using code? You sure the database is created? The error says the database doesn't have the requested column — yousif, Jun 17 '22 at 17:44
https://www.aspdotnet-suresh.com/2015/04/bind-data-from-datatable-to-dropdownlist-in-aspnet-using-csharp-vbnet.html — Bryan Dellinger, Jun 17 '22 at 17:55
`[Name]` in your query gets "translated" to `Name` in the resultset; remove the square brackets in `DDLSelectUser1.DataTextField = "[Name]"` — Josh Part, Jun 17 '22 at 18:57
Oh my goodness @JoshPart ! Thank you! It's working now!! Thanks a bunch for everyone's help too! — LoaFia02, Jun 17 '22 at 20:44
No problem, glad I could help; I'll post an answer both so you can accept it and to expand why this was the solution — Josh Part, Jun 17 '22 at 20:49

score 0 · Accepted Answer · answered Jun 17 '22 at 21:01

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

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

1 Answers1