Providing Search facility

I have a web page with following fields

name,address,post

with three textboxes.I want to provide the search facility to the user.if user enter only the name and hit search it should search only by name, if user enter the values for all the textboxes it should query the database with all 3 values.like wise how can i write the sql query for all the searching possibilities?

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

select *
from Table1
where
(coalesce(@Name, '') = '' or Name = @Name) and
(coalesce(@Address, '') = '' or Address = @Address) and
(coalesce(@Post, '') = '' or Post = @Post)

I prefer this option for the query. If the user enters a value in only one of the fields, then pass a null to the parameter of the other respective fields.

Create PROCEDURE [dbo].[uspGetPeople]
@name varchar(50),
@Address varchar(200),
@Post varchar(5)
AS
SET NOCOUNT ON;
Select name, address, post
from tblPeople
where (name = @Name or @Name IS NULL) and
(address = @Address or @Address IS NULL) and
(post = @Post or @Post IS NULL)

A simple VB.NET example to call the stored procedure:

Dim strName As String = NameTextBox.Value
Dim strAddress as string = AddressTextBox.Value
Dim strPost as string = PostTextBox.Value
Dim strSQL As String = "uspGetPeople"
Dim strConn As String = "My.Database.ConnectionString"
Dim cn As New SqlConnection(strConn)
Dim cmd As New SqlCommand(strSQL, cn)
cmd.CommandType = CommandType.StoredProcedure
If not string.isnullorempty(strName) then
cmd.Parameters.AddWithValue("@Name", strName)
Else
cmd.Parameters.AddWithValue("@Name", dbnull.value)
End if
If not string.isnullorempty(strPost) then
cmd.Parameters.AddWithValue("@Post", strPost)
Else
cmd.Parameters.AddWithValue("@Post", dbnull.value)
End if
If not string.isnullorempty(strAddress) then
cmd.Parameters.AddWithValue("@Address", strAddress)
Else
cmd.Parameters.AddWithValue("@Address", dbnull.value)
End if

Dim dr As SqlDataReader
Using cn
cn.Open()
dr = cmd.ExecuteReader
While dr.Read
'process records returned
'dr("name")
'dr("address")
'dr("post")
End While
cn.Close()
End Using

Category:asp.net Views:0 Time:2011-03-23

Related post

  • Providing a google search facility to a text box in java windows application 2009-11-25

    can we provide a google search facility to a text box in java windows application.. --------------Solutions------------- Yes, you can use Jakarta Commons HttpClient to submit the search request to Google, and read the result. Then, you can use Swing'

  • A php-based search facility with ExtJS not working well on Windows Environment 2010-02-03

    I created a search facility with ExtJS and PHP. The PHP file is the server side script with communicates with the database. On my Linux Environment, its working flawlessly. However, since I need to install this application on a Windows Server, I trie

  • Sending message to multiple contacts of mobile by providing search facility in J2ME 2010-05-07

    I wan to send the message to multiple contacts in the contactlist list=new List("Select Contacts", List.MULTIPLE); int n=list.getFitPolicy(); list.setTicker(ticker); contactmanipulation.getContactData(vector); for(int j=0;j<vector.size();j++){ lis

  • Making search facility 2012-03-10

    I want to make a search facility in my website.I'm using php.. What criteria should be taken for searching. For ex: if someone searches How to make soap I can use many approaches for the search like finding database entries having exactly the same se

  • Using search facility for memory stick 2013-02-28

    The search facility in Windows 7 is excellent. I use memory sticks for my documents. Is it possible to use the search facility( in default mode) for these documents on memory sticks Thank you 1477 --------------Solutions------------- Thank you very m

  • I am unable to connect to he Google website and the search facility produces sites but links fail 2013-04-27

    I am unable to connect to the Google website and while the search facility produces sites the links to them fail. --------------Solutions------------- Hi kathygreer, 1. Do you get any error messages when you try connecting to the websites? 2. Are you

  • Pro Photo Tools - appears to be lacking search facility? 2013-08-31

    I think that Microsoft's Pro Photo Tools is a very good program and is comprehensive and easy to use but is lacking a search facility. So, after inputting data to TITLE, DESCRIPTION, KEYWORD or other field I may want to search for that information to

  • Full-text search facility on SQL Server 2008 2012-01-23

    I want to write queries using the full-text search facility against the AdventureWorks2008 database in SQL Server 2008. When I right click on the table concerned the Full-text index option is greyed out. When I queried sys.dm_fts_index_population to

  • why no search facility within lumia 925 SMS 2012-04-29

    anyone know if Nokia intend on adding a search facility to sms texts. I used to be able to search both body and text numbers within text app but lumia don't have it :*( --------------Solutions------------- I suggest you vote for this feature over at

  • Time for a rant 3... a decent search facility 2012-06-08

    I know I’m not the first to moan about this, but I’m putting it out there anyway... Please can we have more functionality for searching threads? Clearly the current “Find answers” facility is meant to do just that... find answers, but it’s just too i

  • I have lost my search facility in Windows XP 2002 2012-10-03

    My adobe updates and my McAffee updares are not being accepted. I have lost my search facility also. I am running WindowsXP 2002 version. Help! --------------Solutions------------- Hi sunshineX1, - Do you remember making any recent changes to your co

  • My search facility in charms does not work. 2012-12-05

    My search facility in charms does not work. On selecting 'files' it appears to search for my files but soon just stops and returns to the start menu. It all worked fine until I accepted windows updates. --------------Solutions------------- Hi Richard

  • Search facility comes up with 'We can't connect to Outlook.com at the moment' 2013-07-29

    Every time I try and search for emails within my hotmail / outlook account, it produces the above error message. I have tried in safari as well as chrome and it does exactly the same thing. Any suggestions? --------------Solutions------------- Hi Lin

  • Is there a search facility for OneNote Web app? 2014-10-10

    Great to be able to publish and share a OneNote book for collaborative work but surely it is essential to have a search facility so that users can find instances of text within the notebook. Perfectly possible in the full version but I cannot find se

  • The User Profile Service service failed the login, user profile cannot be loaded - and Subsequent missing folders and files and no search facility 2014-11-09

    Hi All I got "The User Profile Service service failed the login, user profile cannot be loaded" as well. I looked at the microsoft advice links and they mainly suggested doing a system restore and / or creating another two profiles to do a system res

  • do not have search facility on nokia music 2015-01-08

    i do not have search facility on nokia music app after i updated my phone via computer using NSU. my phone model is lumia 720. please help ma. i don't find nokia music on store to uninstall and re install. --------------Solutions------------- Dear pr

  • No search facility available in File Explorer 2015-01-14

    I notice that I do not have a Search facility when using my File Explorer. If I go to the search option on the right hand side of the startup screen, type in a file name, press Files the file is not found though I know as a fact it is in a folder on

  • OT: Is Forum Search facility broken? 2013-11-01

    I failed to find my own posts using words I knew were included in them. I then tried several searches using keywords I could see in recent messages, and they too produced no results. A search for 'queue' produced just one post - a year old. Is this f

  • Search Facility in Outlook Mobile 2014-02-26

    I have just bought a new phone, an HTC Touch HD2, with Windows mobile 6.5. I can't find an option to search any of the outlook item eg calendar or notes. I used this facility a lot on my old HP IPAQ which runs Windows Mobile 2003. Is there an equival

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

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