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.
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();
}
}
"au_fname" and why to use it?
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
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')
}
});
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);
});
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>
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();
}
//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
