Linking my column from a table in MSSQL server to a dropdownlist in ASP.NET

Question

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 7-Jun-18 12:22pm

Member 13863605

Updated 7-Jun-18 20:01pm

v2

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Swinkaran · Answer 1 · 2018-06-07T19:42:00

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>
}

Swinkaran · Answer 2 · 2018-06-07T20:03:00

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;
        }
    }
}

Linking my column from a table in MSSQL server to a dropdownlist in ASP.NET

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0