How to bind dropdownlist from database using c# and stored procedure

Question

I would like to know, what is the correct way to bind data on a drop down list from a database using stored procedure and C#. I have a drop down list ddlEmployees and an Employee table which has a column of empFname aswell as a stored procedure selectEmployeePaycheck which has a parameter of @fname, that matches the value in the Employee table which has a column of empFname. I'm using the code in the bottom to bind the data from database, but unfortunately it doesn't work.

SqlConnection con;
SqlCommand cmd;
SqlDataAdapter adr;
DataTable dt;
string conStr;

conStr = ConfigurationManager.ConnectionStrings["payRollConnection"].ConnectionString;
con = new SqlConnection(conStr);
con.Open();
cmd = new SqlCommand("selectEmployeePayCheck", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@fname", ddlEmployees.SelectedValue);
adr = new SqlAdapter(cmd);
dt = new DataTable();
adr.Fill(dt);
cmd.ExecuteReader();
ddlEmployees.DataTextField = "empFname";
ddlEmployees.DataValueField = "empFname";
ddlEmployees.DataSource = dt;
ddlEmployees.DataBind();
cmd.Dispose();
con.Close();

Any suggestions, corrections, answers are very well accepted.

When you say it does not work what is happening that should not be? Also, as a side note, be careful when using something like a first name to be your unique column. If you have 2 Johns working for you than you are going to get incorrect data returned. The best way usually is to have a unique id column in your database and use that to query, join etc. — ThatChris
– ThatChris, Commented Nov 14, 2016 at 6:11
I think problem is here.. cmd.Parameters.AddWithValue("@fname", ddlEmployees.SelectedValue); You're passing FName as unique value before binding any data to ddlEmployees ? — Rahul Hendawe
– Rahul Hendawe, Commented Nov 14, 2016 at 6:19
@ChristopherTrevor has made a valid point. Another issue I can sense is with the web design. You are trying to pass empFName to stored proc from ddlEmployees and the result you are binding to the same ddlEmployees. If you are expecting just one result from stored proc, use label or different controls. If multiple results are expected, use a different ddl to bind. — Vicky_Burnwal
– Vicky_Burnwal, Commented Nov 14, 2016 at 6:24
@ChristopherTrevor the drop down list does not bind the data from the database. — doria_aries
– doria_aries, Commented Nov 14, 2016 at 6:25
@RahulHendawe the "@fname" parameter is from the selectEmployeePayCheck procedure which is associated with the Employee Table — doria_aries
– doria_aries, Commented Nov 14, 2016 at 6:27

Dheeraj Sharma · Accepted Answer · 2016-11-14 08:35:01Z

I have made small modifications in your code, just replace it with this:

SqlConnection con; 
SqlCommand cmd;
SqlDataAdapter adr;
DataTable dt;
string conStr;

conStr = ConfigurationManager.ConnectionStrings["payRollConnection"].ConnectionString;
using(con = new SqlConnection(conStr)) {
        using(cmd = new SqlCommand("selectEmployeePayCheck", con)) {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@fname", ddlEmployees.SelectedValue);
        // or cmd.Parameters.AddWithValue("@fname", ddlEmployees.SelectedText.ToString()); 
        // according to your query at the backend
        adr = new SqlAdapter(cmd);
        dt = new DataTable();
        adr.Fill(dt);
        }
    }

ddlEmployees.DataSource = dt;
ddlEmployees.DataTextField = "empFname";
ddlEmployees.DataValueField = "empFname";
ddlEmployees.DataBind();

Hope it helps. :)

Damith · Accepted Answer · 2016-11-14 06:21:50Z

1

Try by moving data source before field set, also you don't need excute reader here

adr.Fill(dt);
//cmd.ExecuteReader();
//set data source first
ddlEmployees.DataSource = dt;
ddlEmployees.DataTextField = "empFname";
ddlEmployees.DataValueField = "empFname";
ddlEmployees.DataBind();

answered Nov 14, 2016 at 6:21

Damith

63.2k13 gold badges106 silver badges154 bronze badges

3 Comments

doria_aries Over a year ago

if (!IsPostBack) {con = new SqlConnection(conStr); 			con.Open(); 			cmd = new SqlCommand("selectContractorPayCheck", con); 			cmd.CommandType = CommandType.StoredProcedure; 			cmd.Parameters.AddWithValue("@fname", ddlContractor.SelectedValue); 			adr = new SqlDataAdapter(cmd); 			dt = new DataTable(); 			adr.Fill(dt);  			ddlContractor.DataSource = dt; 			ddlContractor.DataTextField = "conFname"; 			ddlContractor.DataValueField = "conFname"; 			ddlContractor.DataBind(); 		}

Damith Over a year ago

Can you update the quotation with your stored procedure code? Any exception you get?

doria_aries Over a year ago

This is the stored procedure that I use:

create proc selectContractorPayCheck @fname varchar(30) as begin select conFname from Contractor where conFname = @fname end

The exception that I get when I run the code block I recently changed is that the drop down list is not binding the data from the database..

ThatChris · Accepted Answer · 2016-11-14 06:44:22Z

Looking at the comments and the original question what I believe to be your scenario is the following:

You have an Employees table containing employee records and some sort of table that has paycheck details.

What you are trying to achieve is when the user selects an employee you show their paychecks in the dropdown?

If this is the case I would approach it as follows:

Use 2 dropdownlists, one for you employees and one for the paycheck records.

Use the code from Damiths answer to populate your employee dropdown. Make sure that this dropdown has autopostback enabled and that you hook into the selectedindexchanged event for this dropdown.

On the selected index changed event use your original code, using the ddlEmployee.selectedvalue, to get the selected employee and populate your paycheck dropdown.

Also, make sure that if you are populating your employee dropdown in the page_load event that you wrap it in

if(!isPostback)
{

}

otherwise the dropdown will repopulate on every postback, causing the salary records of the first employee to always be shown.

I hope this answers helps you to see the direction that you need to follow.

Collectives™ on Stack Overflow

How to bind dropdownlist from database using c# and stored procedure

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related