How do I populate an asp dropdownlist with data from an sql database based on the text from an asp textbox using c#?

Question

I have a small web form that is used to archive jobs. The user puts in the job number in a textbox and then a dropdown needs to be filled with the customers that are bidding the job. The user also chooses whether the job was won or lost or a duplicated. Then they submit the form and the sql database is updated. I can't find and examples of populating a dropdown list based on a textbox. All the examples I find are populating textboxes based on the dropdown.

<div class="container">
    <h2>Job Archive Form</h2>
        <div class="form-group">
            <asp:Label ID="lblJobNumber" runat="server">Job Number</asp:Label><br />
            <asp:TextBox ID="archiveJobNumber" runat="server" AutoPostBack="True" OnTextChanged="archiveJobNumber_TextChanged"/>
        </div>
        <div class="form-group">
            <asp:Label ID="lblJobStatus" runat="server">Job Status</asp:Label><br />
            <asp:DropDownList ID="archiveJobStatus" runat="server">
                <asp:ListItem value="">--Select--</asp:ListItem>
                <asp:ListItem value="1">Active</asp:ListItem>
                <asp:ListItem value="2">Won</asp:ListItem>
                <asp:ListItem value="3">Lose</asp:ListItem>
                <asp:ListItem value="4">Duplicate</asp:ListItem>
            </asp:DropDownList>
        </div>
        <div class="form-group">
            <asp:Label ID="lblCustomer" runat="server">Customer</asp:Label><br />
            <asp:DropDownList ID="archiveCustomer" runat="server" DataSourceID="archiveSqlDSCustomer" DataTextField="Name" DataValueField="CustID" AutoPostBack="True"></asp:DropDownList>
        </div>                                                        
        <asp:Button ID="submit" runat="server" Text="Submit" OnClick="submit_Click"></asp:Button>
</div>

Code Behind:

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.Configuration;
using System.Web.Security;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;

namespace JobsApp
{
    public partial class Archive : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["UID"] == null)
                Response.Redirect(@"Account\Login.aspx");
        }

        protected void submit_Click(object sender, EventArgs e)
        {
            string constring = "";
            constring = ConfigurationManager.ConnectionStrings["JobsConstr"].ToString();
            SqlConnection con = new SqlConnection(constring);
            con.Open();
            SqlCommand cmd = new SqlCommand("usp_changeJobStanding", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter jobNumber = cmd.Parameters.Add("@jobNumber", SqlDbType.VarChar);
            SqlParameter jobStanding = cmd.Parameters.Add("@jobStanding", SqlDbType.Int);
            jobNumber.Value = archiveJobNumber.Text;
            jobStanding.Value = archiveJobStatus.Text;
            cmd.ExecuteNonQuery();
            con.Close();
            Response.Redirect("~/Archive.aspx");
        }

        protected void archiveJobNumber_TextChanged(object sender, EventArgs e)
        {
            archiveSqlDSCustomer.SelectCommand = "GetCustomerListByJobNumber";
            archiveCustomer.DataSourceID = "archiveSqlDSCustomer";
        }
    }
}

It looks like you are getting the data based on the textbox value, do you not know how to build up drop down items, or data bind the drop down control? — Seano666
– Seano666, Commented Apr 12, 2017 at 15:41
i don't know how to build the drop down or bind the data. ASP.Net is new to me I usually work with node. I've inherited this from a programmer who left. — jaz1976
– jaz1976, Commented Apr 12, 2017 at 18:56
@Win the archiveSqlDSCustomer is nothing. I was trying to follow an incomplete example and I forgot to remove it from the post. — jaz1976
– jaz1976, Commented Apr 12, 2017 at 18:58
One other thing I tried was this in the dropdownlist element. <%--<asp:SqlDataSource ID="archiveSqlDSCustomer" runat="server" ConnectionString="<%$ ConnectionStrings:JobsConStr %>" SelectCommand="GetCustomerListByJobNumber" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter Name="jobNumber" DbType="String" ControlID="archiveJobNumber" PropertyName="Text" ConvertEmptyStringToNull="false" /> </SelectParameters> </asp:SqlDataSource>--%>I didn't know how to trigger this — jaz1976
– jaz1976, Commented Apr 12, 2017 at 19:10

Win · Accepted Answer · 2017-04-12 19:28:22Z

1

You could retrieve data from database inside archiveJobNumber_TextChanged event, and bind the data to DropDownList.

For example,

<asp:TextBox ID="archiveJobNumber" runat="server" AutoPostBack="True"
    OnTextChanged="archiveJobNumber_TextChanged" />

<asp:DropDownList ID="archiveCustomer" runat="server"
    DataTextField="Name" DataValueField="CustID" AutoPostBack="True">
</asp:DropDownList>


protected void archiveJobNumber_TextChanged(object sender, EventArgs e)
{
    var query = "SELECT CustID, Name FROM Users WHERE CustID= @Id";
    using (var con = new SqlConnection(connectionString))
    using (var da = new SqlDataAdapter(query, con))
    {
        con.Open();
        var dt = new DataTable();
        da.SelectCommand.Parameters.AddWithValue("@Id", archiveJobNumber.Text);

        da.Fill(dt);
        archiveCustomer.DataSource = dt;
        archiveCustomer.DataBind();
    }
}

Please make sure query is correct.

answered Apr 12, 2017 at 19:28

Win

62.4k13 gold badges106 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jaz1976 Over a year ago

This did work for me. Thank you Win. I modified it a little to use a stored procedure instead of imbedding the sql statement in the code.

Collectives™ on Stack Overflow

How do I populate an asp dropdownlist with data from an sql database based on the text from an asp textbox using c#?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related