How to make the search function with dropdownlist on asp.net

[kikiwisaka]

Hi guys,
i am creating a search function on my web. This is the result

"SELECT [ROUTE], [INST_KEY], [INST_TYPE], [ACCT_KEY], [STATUS], 
                        [DMZ], [CUST_NAME], [ADDRESS_LINE1], [ADDRESS_LINE3], [ADDRESS_LINE4], [ADDRESS_LINE5], 
                        [METER_KEY], [SIZE], [INSTALL_DATE], [X], [Y], [Z], [ADDRESS_LINE2], [MI_PRINX] FROM [DBCUSTOMER] 
                        WHERE ([CUST_NAME] LIKE '%' + @CUST_NAME + '%')"

This function just looking one field. I try for modify it to can the user choose category on dropdownlist, his will can get data which his choice.

How can i do build the search function like that?
The following picture is what I mean.

Thanks,
Regards, Kiki Wisaka

[hericles]

If you intend to have the dropdown box have the various categories such as name, address, date and then a textbox that takes the actual search time all you need to do is include the selected item from the dropdown into the sql query along with the search term.
E.g. imagine I selected name in the list and entered a name as the search term in the textbox.

"SELECT [ROUTE], [INST_KEY], [INST_TYPE], [ACCT_KEY], [STATUS],
    [DMZ], [CUST_NAME], [ADDRESS_LINE1], [ADDRESS_LINE3], [ADDRESS_LINE4], [ADDRESS_LINE5],
    [METER_KEY], [SIZE], [INSTALL_DATE], [X], [Y], [Z], [ADDRESS_LINE2], [MI_PRINX] FROM [DBCUSTOMER]
    WHERE (" + dropDownList.selectedItem.ToString() + " LIKE '%' + @CUST_NAME + '%')"

Obviously the list can be added as a parameter as cust_name is.

[kikiwisaka]

If you intend to have the dropdown box have the various categories such as name, address, date and then a textbox that takes the actual search time all you need to do is include the selected item from the dropdown into the sql query along with the search term.
E.g. imagine I selected name in the list and entered a name as the search term in the textbox.

"SELECT [ROUTE], [INST_KEY], [INST_TYPE], [ACCT_KEY], [STATUS],
    [DMZ], [CUST_NAME], [ADDRESS_LINE1], [ADDRESS_LINE3], [ADDRESS_LINE4], [ADDRESS_LINE5],
    [METER_KEY], [SIZE], [INSTALL_DATE], [X], [Y], [Z], [ADDRESS_LINE2], [MI_PRINX] FROM [DBCUSTOMER]
    WHERE (" + dropDownList.selectedItem.ToString() + " LIKE '%' + @CUST_NAME + '%')"

Obviously the list can be added as a parameter as cust_name is.

Thank you for your answer. But after i try with your code and then the result is Incorrect syntax near the keyword 'LIKE'
so. How to solve this problem?:)... And what the mean Obviously the list can be added as a parameter as cust_name is?:)

[hericles]

Well you could have this for the last part of the SQL:
WHERE (@listItem LIKE '%' + @CUST_NAME + '%')"

and use parameters for the @listItem as you have for @CUST_NAME.

As for the error, your SQL string will have a mistake in it. Looking at what I typed it maybe the quotes marks around the % are the problem. Try
WHERE " + dropDownList.selectedItem.ToString() + " LIKE %' + @CUST_NAME + '%"

[21303359]

u can try this:

in the view

 @using (Html.BeginForm())
            {
                <p>
                    Search By Employee :@Html.TextBox("searchtextbox")
                    @Html.DropDownList("dropdownlist", new[]
                    {
                        new SelectListItem() {Text = "Enquiry Number", Value = "enqNum"},
                        new SelectListItem() {Text = "Employee Number", Value = "empNum"},
                        new SelectListItem() {Text = "First Name", Value = "firstName"},
                        new SelectListItem() {Text = "Last Name", Value = "surname"},
                    })
                    <input type="submit" value="Search" />
                </p>
            }

in the controller

public ActionResult Enquiries(string searchtextbox, string dropdownlist)
        {
            var EnquiriesBusiness = new EnquiriesBusiness();
            List<EnquiriesView> a = (from obj in EnquiriesBusiness.GetAllEnquiries()
                where obj.enquiryStatus == "Completed"
                select obj).ToList();

            if (dropdownlist == "enqNum") //search my enquiry number
            {
                a = a.Where(x => x.enquiryNumber.ToString().Equals(searchtextbox)).ToList();
                return View(a);
            }
            else
                if (dropdownlist == "empNum") //search by employee number
                {
                    a = a.Where(x => x.employeeNumber.ToString().Equals(searchtextbox)).ToList();
                    return View(a);
                }
                else
                    if (dropdownlist == "firstName") //search by firstname
                {
                    a = a.Where(x => x.firstName.ToLower().Contains(searchtextbox.ToLower())).ToList();
                    return View(a);
                }
                    else if (dropdownlist == "surname")//search by lastname
                    {
                        a = a.Where(x => x.lastName.ToLower().Contains(searchtextbox.ToLower())).ToList();
                        return View(a);
                    }

            return View(a);
        }