How to populate 'select' from database in asp.net

Question

I have a html control select

 <select id="Select1" runat="server" name="D1">
    <option></option>
 </select>

How can I populate it with data from my SQL Server database using C# or JavaScript/jQuery/JSON?

Please, do not post answers on how to populate a asp:DropDownList because I already know how to do it, I must use a select control.

@enb081 look here for json via wcf http://stackoverflow.com/questions/2086666/wcf-how-do-i-return-clean-json — Orhan Cinar, Feb 15 '13 at 13:33

MikroDel · Accepted Answer · 2013-02-15T10:43:17.270

23

aspx:

  <select id="Select1" runat="server" name="D1">

  </select>

code behind:

protected void Page_Load(object sender, EventArgs e)
{
   if (!IsPostBack)
   {
      string ConnectString = "server=localhost;database=pubs;integrated security=SSPI";
      string QueryString = "select * from authors";

      SqlConnection myConnection = new SqlConnection(ConnectString);
      SqlDataAdapter myCommand = new SqlDataAdapter(QueryString, myConnection);
      DataSet ds = new DataSet();
      myCommand.Fill(ds, "Authors");

      Select1.DataSource = ds;
      Select1.DataTextField = "au_fname";
      Select1.DataValueField = "au_fname";
      Select1.DataBind();
   }
}

edited Feb 15 '13 at 10:43

answered Feb 15 '13 at 10:28

MikroDel

6,705
7
39
74

What is `"au_fname"` and why to use it? – Ahmad Maleki Oct 07 '16 at 18:10
in this particular case, it'd be a column in the authors table from the QueryString row. So if we had Stephen King, John Grisham, and Emily Bronte as rows in the table, the list would then have options for Stephen, John, and Emily. Text being what is seen, value being the item sent. So I'd probably actually set DataValueField to the equivalent of author_id. Would probably also add a First Name + Last Name result in the query for the DataTextField because what happens when you have Tom Clancy and Tom Brokaw? Could consider adding dates for filtering where name is the same too. – Robert Nov 04 '16 at 19:29

Orhan Cinar · Answer 2 · 2013-02-15T12:29:01.937

5

function GetItems() {

var items;

    $.getJSON("/api/MethodName/" + id(Optional), function (data) {
        $.each(data, function (key, val) {
            items += "<option value='" + val.id+ "'>" + val.value+ "</option>";    
        });

        var header = '<option value=\'\'>Select...</option>';
        $('#Select1').html(header + items);
    });

};

You can use asp.net webapi for json, it is very easy and fast

edited Feb 15 '13 at 12:29

answered Feb 15 '13 at 12:23

Orhan Cinar

8,403
2
34
48

How can I create the Method that returns data from database as json? – enb081 Feb 15 '13 at 12:25
are you using mvc or webforms ? – Orhan Cinar Feb 15 '13 at 12:27
use this to populate: $('#Select1').append($("") .attr("value", key) .text(val)); – Grumpy May 30 '18 at 21:19

score 4 · Answer 3 · answered Feb 15 '13 at 10:44

You can use json in ajax, but you have to return as json from the server using some webservice.

$.ajax({
   url:'/path/to/webservice/method',
   type:'POST',
   dataType: 'json',
   success: function(data){
       $.each(data, function(i, item){
          $('<option value="'+item.val+'">'+item.text+'</option>').appendTo('#Select1');
       });
   },
   error: function(){
      console.log('err')
   }
});

score 3 · Answer 4 · answered Jun 11 '13 at 07:39

In code behind:

 string options = string.Empty; 
 using (SqlConnection sql_conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ToString()))
        {
            SqlDataAdapter sql_adapter = new SqlDataAdapter("select Value, Name from YourTable", sql_conn);
            DataSet ds = new DataSet();
            sql_adapter.Fill(ds, "TempTable");

            foreach (DataRow row in ds.Tables["TempTable"].Rows)
            {
                options = "<option value='" + row["Value"] + "'>" + row["Name"] + "</option>";
            }
            sql_conn.Close();
           return options;
        }

and then you can use jquery:

$.get(url, function (data) {        
        $('#Select1').html(data);
    });

score 2 · Answer 5 · answered Apr 13 '15 at 07:29

2

You can use a repeater

<select>
<asp:Repeater ID="Repeater1" runat ="server" DataSourceID="SqlDataSource1">
    <ItemTemplate>
        <option value='<%# Eval("ID")%>'> <%# Eval("ITEMNAME")%></option>
    </ItemTemplate>
</asp:Repeater>
</select>

answered Apr 13 '15 at 07:29

user2513019

105
1
8

score 0 · Answer 6 · answered May 24 '18 at 20:49

            using (SqlConnection con = new SqlConnection("Data Source = [HostName]; Initial Catalog = CustomerOrders; Integrated Security = true"))
            {
                SqlCommand cmd = new SqlCommand("SELECT Name FROM Customer", con);
                con.Open();

                dropDownList.DataSource = cmd.ExecuteReader();
                dropDownList.DataTextField = "Name";
                dropDownList.DataValueField = "Name";
                dropDownList.DataBind();
            }

score 0 · Answer 7 · answered Mar 23 '23 at 18:05

  //Controller 
    public IActionResult Create()
    {
        User user = new User();
        ViewBag.items = new SelectList(db.Professions, "Id", "Name", user.ProfessionId);
        return View();
     }


   //View
    @model DbOneToMore.Data.User
    <select asp-for="ProfessionId" >
            <option selected disabled>Choose</option>
        @foreach (SelectListItem i in ViewBag.Items)
            {
            <option value="@i.Value">
                    @i.Text
                </option>
            }
        </select>

Надеюсь помогла [1]: dropdownlist set selected value in MVC3 Razor

How to populate 'select' from database in asp.net

7 Answers7