I have developed a web application using MVC architecture.
I have create a Form, Submit it and Insert data in MySQL database using ADO.Net in ASP.Net MVC
On the form I have two Textbox:
- "Name"
- "Country"
and one DropDownList: "UOR"
This is a Model class on the project
public class PersonModel
{
[Required]
[Display(Name = "Name")]
public string Name { get; set; }
[Required]
[Display(Name = "Country")]
public string Country { get; set; }
[Required]
[Display(Name = "UORID")]
public string UORID { get; set; }
[Required]
[Display(Name = "UOR")]
public List<SelectListItem> UOR = new List<SelectListItem>();
}
This is a Controller class on the project
[HttpPost]
public ActionResult Index(PersonModel person)
{
person.UOR = PopulateDropDown(" SELECT * FROM .... ;");
if (ModelState.IsValid)
{
string Name = person.Name;
string Country = person.Country;
string constr = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
string query = "INSERT INTO Customers(Name, Country) VALUES(@Name, @Country)";
using (MySqlCommand command = new MySqlCommand(query))
{
command.Connection = con;
con.Open();
command.Parameters.AddWithValue("@Name", person.Name);
command.Parameters.AddWithValue("@Country", person.Country);
command.ExecuteNonQuery();
con.Close();
}
}
}
else
{
return View(person);
}
return View(person);
}
[HttpGet]
public ActionResult Index()
{
var personModel = new PersonModel();
personModel.UOR = PopulateDropDown(" SELECT * FROM .... ; ");
return View(personModel);
}
private static List<SelectListItem> PopulateDropDown(string query, string textColumn, string valueColumn)
{
List<SelectListItem> items = new List<SelectListItem>();
string constr = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (MySqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
items.Add(new SelectListItem
{
Text = sdr[textColumn].ToString(),
Value = sdr[valueColumn].ToString()
});
}
}
con.Close();
}
}
return items;
}
I have error Could not find specified column in results: UOR
when try insert data in MySQL database, on this part of Controller class
private static List<SelectListItem> PopulateDropDown(string query, string textColumn, string valueColumn)
{
List<SelectListItem> items = new List<SelectListItem>();
string constr = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (MySqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
items.Add(new SelectListItem
{
Text = sdr[textColumn].ToString(),
Value = sdr[valueColumn].ToString()
});
}
}
con.Close();
}
}
return items;
}