'Link the selected item of drop down list to sql script with an on change event
I have been battling this for some time now without success. So Your help is extremely appreciated.
I have created a dropdown list from sql database. What I want to do is to set up an onchange selection event to take the value selected in the dropdown list and use it as a variable in my sql script to get the data from the database. My problem is not being able to get the selected item form the dropdown list to show in the Controller file and then add it to the sql script. I am new to Asp Core 6 and really struggling to understand the changes. Any and all help is appreciated
The View Code
@model CISIII.Models.Dropdownlist
@*
For more information on enabling MVC for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860
*@
@{
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width"/>
<title>Index</title>
</head>
<body>
<h1>Customer Data Update</h1>
<form method="post" >
<hr/>
<table cellpadding="10" cellspacing="10">
<tr>
<td><strong>Customer Name</strong></td>
<td>
@Html.DropDownListFor(m => m.Cust1list, new SelectList(@Model.Cust1list, "Coname", "Coname"), "Select Company", htmlAttributes: new { @class = "form-control", id="coname"})
</td>
<td><input type="text" name="serviceName" value="" /></td>
</tr>
@{
if (Model.Cust2list != null)
{
foreach (var Cust in Model.Cust2list)
{
<tr>
<td><strong>Company Name</strong></td>
<td><td><input value="@Cust.Coname" id="coname" type="text"/></td>
</tr>
<tr>
<td><strong>First Name</strong></td>
<td><input value="@Cust.Fname" id="Fname" type="text"/></td>
<td><strong>Surname</strong></td>
<td><input value="@Cust.Sname" id="sname" type="text"/></td>
</tr>
<tr>
<td><strong>Tel</strong></td>
<td><input value="@Cust.Tel" id="tel" type="text"/></td>
<td><strong>Email</strong></td>
<td><input value="@Cust.Email" id="email" type="text"/></td>
</tr>
<tr>
<td><strong>Address</strong></td>
<td><input value="@Cust.Add1" id="add1" type="text"/></td>
<td><strong>Address</strong></td>
<td><input value="@Cust.Add2" id="add2" type="text"/></td>
</tr>
<tr>
<td><strong>Suburb</strong></td>
<td><input value="@Cust.Suburb" id="suburb" type="text"/></td>
<td><strong>State</strong></td>
<td><input value="@Cust.State" id="state" type="text"/></td>
</tr>
<tr>
<td><strong>Post Code</strong></td>
<td><input value="@Cust.Pcode" id="pcode" type="text"/></td>
<td><strong>Where did you find Us</strong></td>
<td><input value="@Cust.Findus" id="findus" type="text"/></td>
</tr>
<tr>
<td><strong>Product Category</strong></td>
<td><input value="@Cust.Prod_cat" id="prodcat" type="text"/></td>
<td><strong>Notes</strong></td>
<td><input value="@Cust.Question2" id="question2" type="text"/></td>
</tr>
<tr>
<td><strong>Industry</strong></td>
<td><input value="@Cust.Indlist" id="ddlindustry" type="text"/></td>
<td><strong>Status</strong></td>
<td><input value="@Cust.Statlist" id="status" type="text"/></td>
</tr>
<tr>
<td><strong>Start Date</strong></td>
<td><input value="@Cust.Start_date" id="start_date" type="text"/></td>
<td><strong>End Date</strong></td>
<td><input value="@Cust.End_date" id="end_date" type="text"/></td>
</tr>
}
}
}
<tr>
<td>
<button onclick="GetCust2List" value"Submit">Click me</button>
@* <input id="Submit" type="submit" value="submit" formaction="GetCust2List" />*@
</td>
</tr>
</table>
</form>
</body>
</html>
The Model
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using System.Data.SqlClient;
namespace CISIII.Models
{
public class Dropdownlist
{
public DateTime? DtmDate { get; set; }
public List<Status_List> Statlist { get; set; }
public List<Product_List> Prodlist { get; set; }
public List<Industry_List> Indlist { get; set; }
public List<Findus_List> Findlist { get; set; }
public List<Customers_List> Cust1list { get; set; }
public List<NE_Cust_List> Cust2list { get; set; }
public IActionResult? GetDetails { get; set; }
public int SaveDetails { get; set; }
}
public class Status_List
{
public int Id { get; set; }
public string? Description { get; set; }
}
public class Product_List
{
public int Id { get; set; }
public string? prod_cat { get; set; }
}
public class Industry_List
{
public int Id { get; set; }
public string? Description { get; set; }
}
public class Findus_List
{
public int Id { get; set; }
public string? Name { get; set; }
}
public class Customers_List
{
public int Id { get; set; }
public string? Fname { get; set; }
public string? Sname { get; set; }
public string? Coname { get; set; }
public string? Tel { get; set; }
public string? Email { get; set; }
public string? Add1 { get; set; }
public string? Add2 { get; set; }
public string? Suburb { get; set; }
public string? State { get; set; }
public string? Pcode { get; set; }
public string? Findus { get; set; }
public string? Prod_cat { get; set; }
public string? Question2 { get; set; }
public string? Indlist { get; set; }
public string? Statlist { get; set; }
public string? Start_date { get; set; }
public string? End_date { get; set; }
}
public class NE_Cust_List
{
public int Id { get; set; }
public string? Fname { get; set; }
public string? Sname { get; set; }
public string? Coname { get; set; }
public string? Tel { get; set; }
public string? Email { get; set; }
public string? Add1 { get; set; }
public string? Add2 { get; set; }
public string? Suburb { get; set; }
public string? State { get; set; }
public string? Pcode { get; set; }
public string? Findus { get; set; }
public string? Prod_cat { get; set; }
public string? Question2 { get; set; }
public string? Indlist { get; set; }
public string? Statlist { get; set; }
public string? Start_date { get; set; }
public string? End_date { get; set; }
}
public class UserDataModel
{
public string? Fname { get; set; }
public string? Sname { get; set; }
public string? Coname { get; set; }
public string? Tel { get; set; }
public string? Email { get; set; }
public string? Add1 { get; set; }
public string? Add2 { get; set; }
public string? Suburb { get; set; }
public string? State { get; set; }
public string? Pcode { get; set; }
public string? Findus { get; set; }
public string? Prod_cat { get; set; }
public string? Question2 { get; set; }
public string? Indlist { get; set; }
public string? Web { get; set; }
public string? Statlist { get; set; }
public string? Start_date { get; set; }
public string? End_date { get; set; }
public int SaveDetails()
{
SqlConnection con = new SqlConnection("Data Source=bhd-web2;User ID=sa;Password=Sabril01;Database=CAS; Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
string query = "INSERT INTO Customers (fname, sname, coname, tel, email, add1, add2, suburb, state, pcode, " +
"findus, prod_cat, question2, ddlindustry, web, status, start_date, end_date, entry_date) values ('" + Fname + "','" + Sname + "','" + Coname + "', " +
"'" + Tel + "','" + Email + "','" + Add1 + "','" + Add2 + "','" + Suburb + "','" + State + "','" + Pcode + "','" + Findus + "','" + Prod_cat + "', " +
"'" + Question2 + "','" + Indlist + "','" + Web + "','" + Statlist + "','" + Start_date + "','" + End_date + "', getdate())";
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
return i;
}
}
}
The Controller
using Microsoft.AspNetCore.Mvc;
using CISIII.Models;
using System.Data.SqlClient;
using System.Data;
using Microsoft.AspNetCore.Mvc.Rendering;
namespace CISIII.Controllers
{
public class HomeController : Controller
{
public IConfigurationRoot GetConnection()
{
var builder = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appSettings.json").Build();
return builder;
}
Dropdownlist dropdownlist = new Dropdownlist();
public IActionResult Index()
{
return View();
}
public List<Status_List> GetStatusList()
{
var connection = GetConnection().GetSection("ConnectionStrings").GetSection("CAS").Value;
SqlConnection con = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand("Select id, description From status order by description asc;", con);
con.Open();
SqlDataReader idr = cmd.ExecuteReader();
List<Status_List> status = new List<Status_List>();
if (idr.HasRows)
{
while (idr.Read())
{
status.Add(new Status_List
{
Id = Convert.ToInt32(idr["id"]),
Description = Convert.ToString(idr["description"]),
});
}
}
con.Close();
return status;
}
public List<Product_List> GetProdList()
{
var connection = GetConnection().GetSection("ConnectionStrings").GetSection("CAS").Value;
SqlConnection con = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand("Select id, prod_cat_name From prod_cat order by prod_cat asc;", con);
con.Open();
SqlDataReader idr = cmd.ExecuteReader();
List<Product_List> products = new List<Product_List>();
if (idr.HasRows)
{
while (idr.Read())
{
products.Add(new Product_List
{
Id = Convert.ToInt32(idr["id"]),
prod_cat = Convert.ToString(idr["prod_cat_name"]),
});
}
}
con.Close();
return products;
}
public List<Industry_List> GetIndList()
{
var connection = GetConnection().GetSection("ConnectionStrings").GetSection("CAS").Value;
SqlConnection con = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand("Select id, description From industry order by description asc;", con);
con.Open();
SqlDataReader idr = cmd.ExecuteReader();
List<Industry_List> industry = new List<Industry_List>();
if (idr.HasRows)
{
while (idr.Read())
{
industry.Add(new Industry_List
{
Id = Convert.ToInt32(idr["id"]),
Description = Convert.ToString(idr["description"]),
});
}
}
con.Close();
return industry;
}
public List<Findus_List> GetFindList()
{
var connection = GetConnection().GetSection("ConnectionStrings").GetSection("CAS").Value;
SqlConnection con = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand("Select id, cat_name From findus order by cat_name asc;", con);
con.Open();
SqlDataReader idr = cmd.ExecuteReader();
List<Findus_List> find = new List<Findus_List>();
if (idr.HasRows)
{
while (idr.Read())
{
find.Add(new Findus_List
{
Id = Convert.ToInt32(idr["id"]),
Name = Convert.ToString(idr["cat_name"]),
});
}
}
con.Close();
return find;
}
public IActionResult View1()
{
return View();
}
public IActionResult Privacy()
{
return View();
}
public IActionResult updatecust()
{
dropdownlist.Cust1list = GetCust1List();
return View(dropdownlist);
}
public IActionResult newcust()
{
Dropdownlist multi_Dropdownlist = new Dropdownlist
{
Statlist = GetStatusList(),
Prodlist = GetProdList(),
Indlist = GetIndList(),
Findlist = GetFindList(),
};
return View(multi_Dropdownlist);
}
[HttpPost]
public IActionResult GetDetails()
{
UserDataModel umodel = new UserDataModel();
umodel.Fname = HttpContext.Request.Form["fname"].ToString();
umodel.Sname = HttpContext.Request.Form["sname"].ToString();
umodel.Coname = HttpContext.Request.Form["coname"].ToString();
umodel.Tel = HttpContext.Request.Form["tel"].ToString();
umodel.Email = HttpContext.Request.Form["email"].ToString();
umodel.Add1 = HttpContext.Request.Form["street_number"].ToString();
umodel.Add2 = HttpContext.Request.Form["route"].ToString();
umodel.Suburb = HttpContext.Request.Form["locality"].ToString();
umodel.State = HttpContext.Request.Form["administrative_area_level_1"].ToString();
umodel.Pcode = HttpContext.Request.Form["postal_code"].ToString();
umodel.Findus = HttpContext.Request.Form["Findlist"].ToString();
umodel.Prod_cat = HttpContext.Request.Form["Prodlist"].ToString();
umodel.Question2 = HttpContext.Request.Form["question2"].ToString();
umodel.Indlist = HttpContext.Request.Form["Indlist"].ToString();
umodel.Web = HttpContext.Request.Form["web"].ToString();
umodel.Statlist = HttpContext.Request.Form["Statlist"].ToString();
umodel.Start_date = HttpContext.Request.Form["start_date"].ToString();
umodel.End_date = HttpContext.Request.Form["end_date"].ToString();
int result = umodel.SaveDetails();
return View("Index");
}
public List<Customers_List> GetCust1List()
{
var connection = GetConnection().GetSection("ConnectionStrings").GetSection("CAS").Value;
SqlConnection con = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand("Select * From CA_Form order by coname asc;", con);
con.Open();
SqlDataReader idr = cmd.ExecuteReader();
List<Customers_List> cust1 = new List<Customers_List>();
IEnumerable<Customers_List> cust1a = cust1;
if (idr.HasRows)
{
while (idr.Read())
{
cust1.Add(new Customers_List
{
Id = Convert.ToInt32(idr["id"]),
Coname = Convert.ToString(idr["coname"]),
Fname = Convert.ToString(idr["fname"]),
Sname = Convert.ToString(idr["sname"]),
});
}
}
// System.Diagnostics.Debug.WriteLine(cust1.Count);
con.Close();
return cust1;
}
public List<NE_Cust_List> GetCust2List()
{
System.Diagnostics.Debug.WriteLine("Hello");
var connection = GetConnection().GetSection("ConnectionStrings").GetSection("CAS").Value;
SqlConnection con = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand("Select * From CA_Form where coname like '%Sims%'", con);
con.Open();
SqlDataReader idr = cmd.ExecuteReader();
List<NE_Cust_List> cust2 = new List<NE_Cust_List>();
IEnumerable<NE_Cust_List> cust2a = cust2;
if (idr.HasRows)
{
while (idr.Read())
{
cust2.Add(new NE_Cust_List
{
Id = Convert.ToInt32(idr["id"]),
Fname = Convert.ToString(idr["fname"]),
Sname = Convert.ToString(idr["sname"]),
Coname = Convert.ToString(idr["coname"]),
Tel = Convert.ToString(idr["tel"]),
Email = Convert.ToString(idr["email"]),
Add1 = Convert.ToString(idr["add1"]),
Add2 = Convert.ToString(idr["add2"]),
Suburb = Convert.ToString(idr["suburb"]),
State = Convert.ToString(idr["state"]),
Pcode = Convert.ToString(idr["pcode"]),
Findus = Convert.ToString(idr["findus"]),
Prod_cat = Convert.ToString(idr["prod_cat"]),
Question2 = Convert.ToString(idr["question2"]),
Indlist = Convert.ToString(idr["ddlindustry"]),
Statlist = Convert.ToString(idr["status"]),
Start_date = Convert.ToString(idr["start_date"]),
End_date = Convert.ToString(idr["end_date"]),
});
}
con.Close();
System.Diagnostics.Debug.WriteLine("hello 2");
System.Diagnostics.Debug.WriteLine(cust2.Count);
}
dropdownlist.Cust2list= cust2;
return cust2;
}
[HttpPost]
public ActionResult ShowAllMobileDetails(Dropdownlist dl1 , FormCollection form)
{
string conameValue = form["coname"].ToString();
System.Diagnostics.Debug.WriteLine(conameValue);
return View(dl1);
}
}
}
Solution 1:[1]
In Asp.Net Core, We can use SelectListItem to pass data from controller to DropDownList in page, Because there are so many properties in your model, It's very complicated to use them write a demo, So i write a simple demo here set up a onchange selection event to dropdownlist.
//write a simple model and hard code here
List<Student> students = new List<Student>
{
new Student{
Id = "0",
Name = "AAA"
},
new Student
{
Id = "1",
Name = "BBB"
},
new Student
{
Id = "2",
Name = "CCC"
},
new Student
{
Id = "3",
Name ="DDD"
}
};
public IActionResult Index()
{
List<SelectListItem> test = new List<SelectListItem>();
foreach(var student in students)
{
//add value to dropdownlist
test.Add(new SelectListItem { Text = student.Name, Value = student.Id });
}
ViewBag.list = test;
return View();
}
View
<select asp-items="ViewBag.list" onchange="change(this.value)"></select>
@*set an onchange event to get the value of dropdownlist and send it to the specified action*@
@section Scripts
{
<script>
function change(value){
var Id = {
'Id':value
};
$.ajax({
type: "Post",
url: "/Home/GetId",
data: Id,
})
}
</script>
}
Then after you select an option, you can get the It's value in Home/GetId and then add it to the sql script.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Xinran Shen |