ASP.NET MVC: search in data entry form

Question

Hello, and thanks for reading.

I am building a data entry form. I am trying to figure out a way to let the user to provide a criteria (last name for instance), search the employee table for all employees that match the criteria, display the result in a way they can select the correct employee, and pass the the ID of that employee back to the data entry form so they can complete the record and save it.

Thanks

Answer 1

I suggest using Linq.

Consider Scott Gu's Example blog........

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

public static void GetEmployeeIDByLastName(string lastName)
{

DataContext dc = new DataContext();

var queryResult = from q in dc.Employee
            where q.EmployeeLastName.Equals(lastName)
            select new {
                EmployeeID = q.EmployeeID
                      }

foreach(var empID in queryResult)
        {
             //pass the empID value back to the display
        }
}

Answer 2

Something like this in the controller to search in the database (using linq) ?

public ActionResult searchEmployees(string searchString) {
        var employees = (from e in db.Employees
                               where e.Name.Contains(searchString)
                               orderby e.Name
                               select e);
        return view("SearchResult", employees);
}

EDIT: Just read your comments and if I understand correctly, you're only interested in the id's. Are you using those in javascript, and is this some kind of ajax call ? In that case you might want to return an array, or a csv string and deal with the id's in the javascript call.

Answer 3

One way to do this would be with the jQuery autocomplete plugin. Have text box on your form that allows search and a hidden field that stores the id. Use autocomplete via AJAX to get a list of name/id pairs returned as JSON based on the search criteria. Have the autocomplete set up to force selection from the list -- which will disallow any non-matching text in the field. When the user selects an item from the list have the result function store the associated id in the hidden field. Use the hidden field on form post to get the ID of the employee.

It might look something like:

View

$('#searchBox').autocomplete( '/Employees/Search', {
    dataType: 'json',
    max: 25,
    minChars: 2,
    cacheLength: 1,
    mustMatch: true,
    formatItem: function(data,i,max,value) {
        return value;
    },
    parse: function(data) {
        var array = new Array();
        for (var i=0; i < data.length; i++) {
            var datum = data[i];
            var display = datum.FirstName + ' ' + datum.LastName;
            array[array.length] = { data: datum, value: display, result: display };
        }
    }
});

$('#searchBox').result( function(event, data, formatted) {
    if (data) {
       $('#employeeID').val( data.EmployeeID );
    }
});

$('form').submit( function() {
    if (!$('#employeeID').val()) {
       alert( 'You must select an employee before clicking submit!' );
       return false;
    }
});


<input type='text' id='searchBox' />
<input type='hidden' id='employeeID' name='employeeID' />

Controller:

public ActionResult Search( string q, int limit )
{
    var query = db.Employees.Where( e => e.LastName.StartsWith( q ) )
                            .OrderBy( e => e.LastName )
                            .Select( e => new
                                 {
                                     FirstName = e.FirstName,
                                     LastName = e.LastName,
                                     EmployeeID = e.EmployeeID
                             });
    if (limit > 0)
    {
        query = query.Take(limit);
    }

    return Json( query.ToList() );
}

public ActionResult SomeAction( int employeeID, ... )
{
   ...
}