C# MVC Search multiple columns in SQL Database table

Question

Here is my existing code :

MobileDatas/Index.cshtml

    @using (Html.BeginForm("Index", "MobileDatas", FormMethod.Get))
     {
     <p>
         Manufacturer: @Html.DropDownList("searchGenre", "All")
         Filter By: @Html.TextBox("SearchString") <br />
         <input type="submit" value="Filter" />
    </p>
     }

MobileDatasController.cs

    public ActionResult Index(string searchGenre, string searchString)
    {
        string item1 = "Manufacturer";
        string item2 = "Model";
        string item3 = "Network Type";
        var GenreLst = new List<string> { "Manufacturer", "Model", "NetworkType", };

        var filterresults = from m in db.MobileDatas
                            select m;
        ViewBag.searchGenre = new SelectList(GenreLst);

        if (GenreLst.Contains(Convert.ToString(item1)))
        {
            if (!string.IsNullOrEmpty(searchString))
            {
            filterresults = filterresults.Where(k=>Manufacturer.Contains(searchString));
            }
        }
        else if (GenreLst.Contains(Convert.ToString(item2)))
        {
            if (!string.IsNullOrEmpty(searchString))
            {
                filterresults = filterresults.Where(x => x.Model.Contains(searchString));
            }
        }
        else if (GenreLst.Contains(Convert.ToString(item3)))
        {
            if (!string.IsNullOrEmpty(searchString))
            {
                filterresults = filterresults.Where(v => v.NetworkType.Contains(searchString));
            }
        }
        return View(filterresults);
    }

Database: MobileData

    MobileID Manufacturer Model NetworkType 
    1          Samsung     S4     Orange
    2          Nokia       X1     O2
    3          Sony        Z1     Orange

Hi everyone

I'm having some trouble finding/coming up with a solution to my problem. I'm trying to search my table based on the column selected in DropDownList and return the result

for example: Manufacturer selected + "Sony" typed

the result should just show : 3 Sony Z1 Orange

As it stands my code seams to work for the first if statement if(GenreLst.Contains(Convert.ToString(item1)))

Edit:

found out the above code is breaking as the items are always in GenreList.

How can i return the selected item to compare against item1?

you can save a lot of code by doing your NullOrEmpty check at the top, one time — Jonesopolis
– Jonesopolis, Commented Aug 19, 2014 at 13:12
In your action searchGener should contain whatever user selected in ddmenu and searchstring would be the text content. You can use appropriate variable. — Jenish Rabadiya
– Jenish Rabadiya, Commented Aug 19, 2014 at 13:28

Kevin Nacios · Accepted Answer · 2015-03-07 01:56:42Z

2

Here I recreated your view and added a foreach loop so I could print the output

@model IEnumerable<stack.Controllers.Mobiles>

@using (Html.BeginForm("Index", "Home", FormMethod.Get))
{
    <p>
        Manufacturer: @Html.DropDownList("searchGenre", "All")
        Filter By: @Html.TextBox("SearchString") <br />
        <input type="submit" value="Filter" />
    </p>
}

@if (Model != null || Model.Any())
{
    foreach (var item in Model)
    {
        <p>@item.ID | @item.Manufacturer | @item.Model | @item.NetworkType </p>
    }    
}

Now here is the controller. first:

filterresults = filterresults.Where(k=>Manufacturer.Contains(searchString));

you need to do

k => k.Manufacturer.Contains(searchString)

Next I left everything as is so you can follow easily. I just added a Mobiles class so I could recreate your situation. :)

public class Mobiles
    {
        public int ID { get; set; }

        public string Manufacturer { get; set; }

        public string Model { get; set; }

        public string NetworkType { get; set; }
    }

    public class HomeController : Controller
    {
      public ActionResult Index(string searchGenre, string searchString)
    {
        string item1 = "Manufacturer";
        string item2 = "Model";
        string item3 = "NetworkType";
        var GenreLst = new List<string> { item1, item2, item3, };
        List<Mobiles> Mobs = new List<Mobiles>(){
            new Mobiles() { ID = 1, Manufacturer = "Samsung", Model = "S4", NetworkType = "Orange" },
            new Mobiles(){  ID = 2, Manufacturer = "Nokia", Model ="X1", NetworkType ="O2"},
            new Mobiles(){ ID = 3, Manufacturer = "Sony", Model = "Z1", NetworkType = "Orange"}
        };


        var filterresults = from m in Mobs
                            select m;
        ViewBag.searchGenre = new SelectList(GenreLst);

        // ToLower for case
        if (!String.IsNullOrEmpty(searchGenre))
        {
            switch (searchGenre)
            {
                case "Manufacturer":
                    filterresults = filterresults.Where(k => k.Manufacturer.Contains(searchString));
                    break;
                case "Model":
                    filterresults = filterresults.Where(x => x.Model.Contains(searchString));
                    break;
                case "NetworkType":
                    filterresults = filterresults.Where(v => v.NetworkType.Contains(searchString));
                    break;
                default:
                    // Something defaulty if you want.
                    break;
            }
        }

        return View("Index", filterresults);
    }
}

Last words: And with that everything works on my end. I would suggest using a ViewModel instead of a view bag, just makes life easier. Then you can store all your form variables in it.

Edited: look here string item1 = "Manufacturer"; string item2 = "Model"; string item3 = "Network Type";

see the Network Type in your GenreLst you have it as "NetworkType", you need to remove the space on item3.

I added a switch structure.. just to make this more readable and better.

edited Mar 7, 2015 at 1:56

Kevin Nacios

2,85321 silver badges32 bronze badges

answered Aug 19, 2014 at 13:35

BenS

911 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Baggerz Over a year ago

Hi Jedile Thanks for the response, Ive tried adding the elements you have suggested. I corrected filterresults = filterresults.Where(k => k.Manufacturer.Contains(searchString)); good spot i missed it off :) I'm still not able to search by Model or network showing. I'm not sure if you saw my edit about the bellow line if(GenreLst.Contains(Convert.ToString(item1))) i think the problem is being caused by GenreLst always containing the items. however i dont know how to solve this, usually i would use dropdownbox.selecteditem type property but this isnt an option in mvc. Any ideas?

BenS Over a year ago

@Baggerz I have managed to fixed it and I posted the updated code. I detailed the issues at the bottom. I hope I made it readable for you. But everything works. Button line is you weren't using the searchGenre string to change what you wanted to search for. You were just checking if item1 was contained in the genre list, so it was so it always checked the Model instead of your other fields. Sorry I overlooked this. It all works for me now. :)

Collectives™ on Stack Overflow

C# MVC Search multiple columns in SQL Database table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related