Click here to Skip to main content
15,498,516 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I have a 'employee' table in MSSQL Server and I want to extract the 'employee position' column from that table to a dropdownlist on Visual Studio in Asp.Net. So when the user clicks on the dropdownlist, the data from the table gets directly extracted and displays on the dropdownlist.

What I have tried:

I can edit and manually put the data in the dropdownlist but there's about 60 rows in my table for that column. So I was wondering if there is a way I can get those data directly from MSSQL without manually putting it in the dropdownlist.
Posted
Updated 7-Jun-18 21:01pm
v2
Comments
Swinkaran 8-Jun-18 1:43am    
I left a solution below. This is using Asp.Net MVC way of doing. Not sure if you are working with Webforms.

Youcan make use of the MVC's SelectListItem. Further reference can be found in : Drop-down Lists and ASP.NET MVC[^]

HomeController.cs
public class HomeController : Controller
   {
       public ActionResult Index()
       {
           EmployeeModel model = new EmployeeModel();
           model.Positions = ReadData();
           return View();
       }

       [NonAction]
       private static List<SelectListItem> ReadData()
       {
           List<SelectListItem> Positions = new List<SelectListItem>();
           string ConnString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
           using (SqlConnection connection = new SqlConnection(ConnString))
           {
               string query = "SELECT [DepartmentId] ,[Name] FROM[EmployeeTst].[dbo].[TblDepartment]";
               using (SqlCommand cmd = new SqlCommand(query))
               {
                   cmd.Connection = connection;
                   connection.Open();
                   using (SqlDataReader reader = cmd.ExecuteReader())
                   {
                       while (reader.Read())
                       {
                           Positions.Add(new SelectListItem
                           {
                               Text = reader["DepartmentId"].ToString(),
                               Value = reader["Name"].ToString()
                           });
                       }
                   }
                   connection.Close();
               }
           }

           return Positions;
       }
   }

Index.cshtml
@model MVC.Models.EmployeeModel
@using (Html.BeginForm("Index", "Home", FormMethod.Post))
{
    <table>
        <tr>
            <td>
                @Html.DropDownListFor(p => p.EmployeeId, Model.Positions, "Please select")
            </td>
        </tr>
    </table>
}
 
Share this answer
 
v2
This is the Asp.Net WebForm way of doing,

Default.aspx
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="DDList_Demo._Default" %>

<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">
    <div class="row">
        <div class="col-md-4">
            <h2>Positions</h2>
            <p>
                <asp:DropDownList ID="ListPosition" runat="server">
                </asp:DropDownList>
            </p>
        </div>
    </div>
</asp:Content>


Default.aspx.cs
public partial class _Default : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Dictionary<String, String> Positions = ReadData();

                ListPosition.DataSource = Positions;
                ListPosition.DataTextField = "Value";
                ListPosition.DataValueField = "Key";
                ListPosition.DataBind();
            }
        }

        private static Dictionary<String, String> ReadData()
        {
            Dictionary<String, String> Positions = new Dictionary<string, string>();
            string ConnString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(ConnString))
            {
                string query = "SELECT [EmployeeId] ,[Position] FROM[EmployeeDb].[dbo].[TblEmployees]";
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = connection;
                    connection.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            Positions.Add(sdr["EmployeeId"].ToString(), sdr["Position"].ToString());
                        }
                    }
                    connection.Close();
                }
            }

            return Positions;
        }
    }
}
 
Share this answer
 
Comments
Richard Deeming 8-Jun-18 12:49pm     CRLF
That's the old WebForms way. :) The new way would be to use model binding[^]:
<asp:DropDownList ID="ListPosition" runat="server"
    DataTextField="Value"
    DataValueField="Key"
    SelectMethod="ReadData"
/>
public Dictionary<string, string> ReadData() { ... }

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900