C# Drop Down List Web Form ASP.NET SQL not populating

Question

protected void CompanyDropDown_Click(object sender, EventArgs e)
{
    string connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
    using (SqlConnection cn = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand("[dropdownCompany]", cn);
        cn.Open();
        CompanyDropDown.DataSource = cmd.ExecuteReader();
        CompanyDropDown.DataBind();
    }
}

The stored procedure dropdownCompany is just one column with a list of company names.

For some reason this is not populating the drop down when I execute the web form.

Have you tried executing the stored procedure in SQL Server Management Studio to see if there is actually data being returned? — maccettura
– maccettura, Commented Mar 8, 2017 at 18:03
Yeah the stored procedure populated the results in sql server management studio — RA19
– RA19, Commented Mar 8, 2017 at 18:28

zanussi · Accepted Answer · 2017-03-08 19:46:11Z

1

Before binding, specify DataTextField and DataValueField for the dropdown.

DataTextField and DataValueField should be the name of columns from the result set which you get after executing your stored procedure.

You should specify which field will be visible to the user in the dropdown. This will be your DataTextField.

DataValueField is not visible to the user. but you will need this value when you are performing some other operation based on the selected item in the dropdown.

CompanyDropDown.DataTextField = "Name";
CompanyDropDown.DataValueField = "ID";
CompanyDropDown.DataBind();

edited Mar 8, 2017 at 19:46

zanussi

1,3062 gold badges22 silver badges29 bronze badges

answered Mar 8, 2017 at 18:55

sbs

913 bronze badges

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

11 Comments

RA19 Over a year ago

this is my sql stored procedure: SELECT distinct companyid, companyname from Company

maccettura Over a year ago

Did you change the values from Name and ID to companyname and companyid?

RA19 Over a year ago

Yep: CompanyDropDown.DataSource = cmd.ExecuteReader(); CompanyDropDown.DataTextField = "companyname"; CompanyDropDown.DataValueField = "companyid"; CompanyDropDown.DataBind();

sbs Over a year ago

Btw, why is your code in Dropdown's click event? You want the dropdown to be populated when u click on it? Or when the page is loading?

RA19 Over a year ago

I want the drop down to be populated when you click on it, but would it make a difference if its when the page is loaded and then the user clicks on it?

|

maccettura · Accepted Answer · 2017-03-08 18:31:46Z

0

You will have to set the CommandType property of your SqlCommand object to CommandType.StoredProcedure

protected void CompanyDropDown_Click(object sender, EventArgs e)
{
    string connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
    using (SqlConnection cn = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand("[dropdownCompany]", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cn.Open();
        CompanyDropDown.DataSource = cmd.ExecuteReader();
        CompanyDropDown.DataBind();
    }
}

answered Mar 8, 2017 at 18:31

maccettura

10.9k3 gold badges31 silver badges37 bronze badges

1 Comment

RA19 Over a year ago

I have added this line also: cmd.CommandType = CommandType.StoredProcedure;

Collectives™ on Stack Overflow

C# Drop Down List Web Form ASP.NET SQL not populating

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related