Search Database by First and Last Name

I am using ASP.NET with C# and subsonic. I am trying to setup a search on a text field by first and last name.

First or Last Name: <asp:TextBox ID="txtSearchName" runat="server"></asp:TextBox> <asp:linkButton runat="server" Text="Send" onclick="btnSubmit_Click" /> <asp:GridView border="0" cellpadding="3" cellspacing="3" ShowHeader="True" allowsorting="true" ID="GridView1" runat="server" AutoGenerateColumns="false" Visible="false" AllowPaging="True" PageSize="10" PagerStyle-Mode="NumericPages" OnPageIndexChanging="GridView1_PageIndexChanging" >

In the code behind, I have this:

private void BuildGridView1() { GridView1.DataSource = new Select(PastAwardName.Schema.TableName + ".*", PastAwardType.Schema.TableName + ".*") .From(PastAwardName.Schema) .InnerJoin(PastAwardType.Schema.TableName, PastAwardType.Columns.VolID, PastAwardName.Schema.TableName, PastAwardName.Columns.VolID) .Where(PastAwardName.Columns.LName).IsEqualTo(this.txtSearchName.Text) .Or(PastAwardName.Columns.FName).IsEqualTo(this.txtSearchName.Text) .OrderAsc(PastAwardType.Columns.AwardYear) .ExecuteDataSet(); } protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) { BuildGridView1(); GridView1.PageIndex = e.NewPageIndex; GridView1.DataBind(); }

This works if you enter either the first or last name, but I want to be able to search for them both at the same time from one text field. Is this possible?

-------------Problems Reply------------

You should be able to build your query and then append the Or/And restrictions based on whether you have more than one name. The following should work but you should be aware that edge cases like double-barelled names, a user entering a middle name etc. will catch you out:

private void BuildGridView1()
{
string[] names = this.txtSearchName.Text.Split(" ".ToCharArray());

SqlQuery query = DB.Select(PastAwardName.Schema.TableName + ".*", PastAwardType.Schema.TableName + ".*")
.From(PastAwardName.Schema)
.InnerJoin(PastAwardType.Schema.TableName, PastAwardType.Columns.VolID, PastAwardName.Schema.TableName, PastAwardName.Columns.VolID)
.Where(PastAwardName.Columns.FName).IsEqualTo(names[0])

if(names.Length > 1)
query = query.And(PastAwardName.Columns.LName).IsEqualTo(names[1])
else
query = query.Or(PastAwardName.Columns.LName).IsEqualTo(names[0]

GridView1.DataSource = query.OrderAsc(PastAwardType.Columns.AwardYear)
.ExecuteDataSet();
}

Split the contents of this.txtSearchName.Text, if it contains one word use your original query, if it contains two words, then use a modified version of your query:

private void BuildGridView1()
{
string[] names = String.Split(this.txtSearchName.Text, ' ');
if (names.length == 1) {
GridView1.DataSource = new Select(PastAwardName.Schema.TableName + ".*", PastAwardType.Schema.TableName + ".*")
.From(PastAwardName.Schema)
.InnerJoin(PastAwardType.Schema.TableName, PastAwardType.Columns.VolID, PastAwardName.Schema.TableName, PastAwardName.Columns.VolID)
.Where(PastAwardName.Columns.LName).IsEqualTo(names[0])
.Or(PastAwardName.Columns.FName).IsEqualTo(names[0])
.OrderAsc(PastAwardType.Columns.AwardYear)
.ExecuteDataSet();
}
else if (names.Length == 2) {
GridView1.DataSource = new Select(PastAwardName.Schema.TableName + ".*", PastAwardType.Schema.TableName + ".*")
.From(PastAwardName.Schema)
.InnerJoin(PastAwardType.Schema.TableName, PastAwardType.Columns.VolID, PastAwardName.Schema.TableName, PastAwardName.Columns.VolID)
.Where(PastAwardName.Columns.LName).IsEqualTo(names[1])
.And(PastAwardName.Columns.FName).IsEqualTo(names[0])
.OrderAsc(PastAwardType.Columns.AwardYear)
.ExecuteDataSet();
}
}

You'll probably also want to add error handling/validation for the case when the user does not enter a value in the text box or enters 3 or more words.

Category:c# Views:0 Time:2009-06-29

Related post

  • Search database with special character 2011-12-30

    How to search the database with special character? I use this code to as a button <a href="search.php?search=A & W Restaurants&submit=submit">A & W Restaurants</a> After that, I use this to search database $query = mysql_real_

  • Search database through Views 2012-02-21

    I m trying to search database values through the views. I m stuck at the below error. USE AdventureWorks GO --EXEC Customer.sp_FindInViews Stephen, Sales ALTER PROCEDURE Customer.sp_FindInViews @stringToFind VARCHAR(100), @schema sysname AS SET NOCOU

  • Why isn't Media Center maintenance pruning old guide entries from the Windows Search database? 2013-01-01

    When I first installed W7 and ran Media Center setup, the Windows Search database showed ~77,000 entries. I've had MC maintenance run several times since then, and the Windows Search database now shows ~124,000 entries. When I previously ran Vista Ho

  • Regex for searching Database-Statements in VisualStudio 2009-07-22

    I am currently working on a rather large project in Visual Studio 2008 with a lot of Database-Statements. The Statements are held in strings like this: string stmt = "SELECT ID, OTHER " + "FROM TABLE " + "WHERE CONDITION"; I was wondering how to find

  • Search Database using a collection of entity objects 2009-10-22

    I'm sure this is straight forward but I'm very new to entity queries and has probably been asked before. What i need to to search for all business in my database where they have a category that exists in a collection of categories I have built up ILi

  • How can I use Verity to index and search database content in ColdFusion 9? 2010-01-04

    I have tried to use ColdFusion 9 to build search engine in my site. The key is Verity which I read it is the best tool to do the indexing and searching in my database content. But I search around with no luck about any tutorial to tell me how to done

  • search database table for value and put it in a datatable 2010-01-26

    I have a textbox and a search button i trying to search file names in a database table and display them in a datatable... private void GetSearchResults(string machineID, string searchFileName) { DataTable dt = new DataTable(); SqlConnection connectio

  • Search database using LIKE and wildcards 2010-02-18

    I have four search fields that are used to search a database for book id:s and then echos out the result. Depending on wich field you choose to search from the sql query builds up as you can see in the code below. The title and isbn field are working

  • Search database returning results to gridview via sqldatareader 2010-02-23

    I am trying to create a search page, this allows the admin to search through the entries in the database by certain criteria, such as province (like state but diff country) Here is the code I have so far. The problem is that I am not getting any erro

  • Searching Database by Arbitrary Date in PHP 2010-03-21

    Suppose you have a messaging system built in PHP with a MySQL database backend, and you would like to support searching for messages using arbitrary date strings. The database includes a messages table, with a 'date_created' field represented as a da

  • Searching Database PHP/MYSQL Question 2010-06-24

    Right now I'm just using a simple WHERE name LIKE '%$ser%' But I'm running into an issue - say the search is Testing 123 and the "name" is Testing, it's not coming back with any results. Know any way to fix it? Am I doing something wrong? -----------

  • Search functionality to search database tables in android 2010-08-07

    I have implemented the search functionality using the android search dialog.to search multiple columns of a table using LIKE query.But search became slow as I have written a query in such a way that every time the text of each field(like the sample f

  • Search database for offensive words 2010-09-05

    I am looking on running a search on my database at set intervals for a list of words I consider offensive (because I am an authoritarian dictator and I hate free speech - I rule with an Iron fist). How would I most efficiently search my database for

  • Searching Database PHP Mysql 2010-10-09

    I know most of the time people just do a LIKE '%$search_query%' but see the thing is, in my case someone may be searching for things like that cow is fat now, the thing is in my database i wouldnt be storing it exactly as "the cow is fat" i'd be stor

  • Tutorials on creating a simple item search database using PHP and MySQL? 2010-11-07

    I have an Excel spreadsheet of some product data, (just a simple table) that I want to put on the web (this isn't serious, I'm just keen on creating my first database on the web). I want to turn this spreadsheet into database table in MySQL. The colu

  • Add a spinning wheel while application is searching database 2010-11-30

    I have an application that searches in a database for some information. Since the database is quite big, it sometimes takes a lot of time before the application returns the results to the interface. I want to add some sort of spinning wheel to inform

  • Search database in PHP 2011-02-01

    I wanted to be able to Search in the database at any table whether it is the First Name, Last Name, Middle Name, Etc... the problem is, I always get from query an empty results This is my attempt I use the HTML Form for the User to Search With clicki

  • Sitewide multi object search - database design / code strategy? 2011-03-01

    I am lost on how to best approach the site search component. I have a user content site similar to yelp. People can search for local places, local events, local photos, members, etc. So if i enter "Tom" in the search box I expect the search to return

  • SQL - searching database with the LIKE operator 2011-03-03

    Given your data stored somewhere in a database: Hello my name is Tom I like dinosaurs to talk about SQL. SQL is amazing. I really like SQL. We want to implement a site search, allowing visitors to enter terms and return relating records. A user might

  • Searching database with different charset 2011-03-14

    I have a website with a small searching script, where I use utf-8 charset. Now on my mysql database I use the latin1_swedish_ci charset. When I want to search for something that has the letters å,ä or ö it doesn't return any results because in the da

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.197 (s). 11 q(s)