Hi venkatg,
Check this example. Now please take its reference and correct your code.
Database
For this example I have used of NorthWind database that you can download using the link given below.
Download Northwind Database
SQL
CREATE PROCEDURE [dbo].[Customers_Search]
@ContactName NVARCHAR(30) = NULL,
@City NVARCHAR(15) = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerId
,ContactName
,City
,Country
FROM Customers
WHERE (ContactName LIKE @ContactName + '%' OR @ContactName IS NULL)
AND (City LIKE @City + '%' OR @City IS NULL)
END
Model
public class CustomerModel
{
public string CustomerId { get; set; }
public string Name { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
Controller
public class HomeController : Controller
{
// GET: /Home/
public ActionResult Index()
{
return View(GetCustomers("", "").Take(10));
}
[HttpPost]
public ActionResult Index(string name, string city)
{
if (city == "0") { city = ""; }
return View(GetCustomers(name, city).Take(10));
}
private List<CustomerModel> GetCustomers(string name, string city)
{
List<CustomerModel> customers = new List<CustomerModel>();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Customers_Search", con))
{
cmd.CommandType = CommandType.StoredProcedure;
if (!string.IsNullOrEmpty(name))
{
cmd.Parameters.AddWithValue("@ContactName", name);
}
if (!string.IsNullOrEmpty(city))
{
cmd.Parameters.AddWithValue("@City", city);
}
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
customers.Add(new CustomerModel
{
CustomerId = sdr["CustomerId"].ToString(),
Name = sdr["ContactName"].ToString(),
City = sdr["City"].ToString(),
Country = sdr["Country"].ToString()
});
}
con.Close();
}
}
return customers;
}
}
<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<IEnumerable<_160693_Multiple_Search_MVC.Models.CustomerModel>>" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Index</title>
</head>
<body>
<% using (Html.BeginForm("Index", "Home", FormMethod.Post))
{ %>
Name:<input type="text" name="name" />
<br />
City:<select name="city">
<option value="0">Select</option>
<option value="Boise">Boise</option>
<option value="London">London</option>
<option value="Sao Paulo">Sao Paulo</option>
</select>
<input type="submit" value="Search" id="btnSearch" />
<hr />
<table>
<tr>
<th>CustomerId</th>
<th>Name</th>
<th>City</th>
<th>Country</th>
</tr>
<% foreach (var item in Model)
{ %>
<tr>
<td><%: item.CustomerId %></td>
<td><%: item.Name %></td>
<td><%: item.City %></td>
<td><%: item.Country %></td>
</tr>
<% } %>
</table>
<% } %>
</body>
</html>
Screenshot
