Advanced Search box

Hi, Using Visual Web Developer and SQL Express, (coding in Csharp) I want to add a

advanced search facility to my site. Problem is I do not know how to begin.

The website is a business directory and displays company names and services that they provide.

Here is where I am at...

So far I have made one query whichallows users to search the database by "town" such as if they enter "London" in the text box, it returns the relevant records into a gridview.

Rather than have a seperate text box for each query, I want to provide an advanced search box option so users can define their search and query the database by various criterias, such as geography, sector, name, number of employees in companies etc.

I have seen compact advanced search boxes on many sites!.

Replies greatly appreciated.

prontonet

You can use a query with multiple search criterials, and 'disable' a search criterial when the input parameter is null. For example:

USE Northwind
DECLARE @.EId int, @.CId varchar(20)
SET @.EID=6
SELECT *
FROM Orders
WHERE EmployeeID=isnull(@.EId,EmployeeID)
AND CustomerID=isnull(@.CId,CustomerID)

Here we make a trick by using ISNULL function to return the self value of a field if the parameter is null. So in your code, just pass null values to the parameters when corresponding textbox is empty.

|||

Mnay Thanks for your reply. Can you give me another example - somehow not quite

following it yet??

|||Sorry I'm not not clear. Can you explain more about the example you want?|||

easiest way to explain is to give an example of what I would like a user to be able to do

Say I have 1000 companies in my database, then I want to give the user multiple search options

to retrieve only the data they need but in a compact way - As opposed to making a seperate text box for each search criteria which takes up a lot of space on the web page.

Ideally I want the user to be able to select or tick (from a dropdown or (checkbox) which

critieria they are searching by and then enter just enter any text into single box and just hit the search button.

I have included example criterias below...

Search Criteria 1 (By town)

Search Criteria 2 (by Industry sector)

Search Criteria 3 (by number of employees in company

Search Criteria 4 (company ID)

There could be more, also Ideally I would want them also to be able to filter the search further so they can for example select - "companies in "London" with "100+ employees", and that trade in the "technology sector"

I almost understand your example put maybe if you could put it into the above context

that would help. once again I am using visual web developer and Csharp.

Thanks

|||

Im really looking forward to an answer to this. Im stuck on the same thing......

Some1 plz help !!! With an example......

|||

OK, let's look at a simple example. Suppose we have a table as following in database:

CREATE TABLE Companies(ComID int PRIMARY KEY, town nvarchar(100),
IndustrySector nvarchar(50),NumOfEmployees int)

And we have 4 TextBoxes in application used for inputting search criterials. Then we can use such code:

using (SqlConnection conn = new SqlConnection(@."Data Source=.\IORI2000;Integrated Security=SSPI;Database=tempdb"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Companies WHERE town=isnull(@.town,town)AND Industrysector=isnull(@.zInSec,Industrysector)AND NumOfEmployees>=@.NOE AND ComID=isnull(@.ComID,ComID)", conn);

cmd.Parameters.Add("@.town", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@.zInSec", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@.NOE", SqlDbType.Int);
cmd.Parameters.Add("@.ComID", SqlDbType.Int);
if (textBox1.Text.Length == 0)
cmd.Parameters["@.town"].Value = DBNull.Value;
else
cmd.Parameters["@.town"].Value = textBox1.Text;
if (textBox2.Text.Length == 0)
cmd.Parameters["@.zInSec"].Value = DBNull.Value;
else
cmd.Parameters["@.zInSec"].Value = textBox2.Text;

cmd.Parameters["@.NOE"].Value = textBox3.Text.Length == 0 ? 0 : Int32.Parse(textBox3.Text);
if (textBox4.Text.Length == 0)
cmd.Parameters["@.ComID"].Value = DBNull.Value;
else
cmd.Parameters["@.ComID"].Value = Int32.Parse(textBox4.Text);

SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
label1.Text= dt.Rows.Count.ToString();
}

|||Thanks so much. How can I use this same example to search more then one column based on a single textbox.|||

That's something you need to implement from your code. For example you can validate the input text of a textbox so that the search criterals are delimited by some chars (such as ';' in connection string), and then split the text into a string array. In this case I prefer dynamic SQL built from concatenated basic SELECT command and WHERE clause. Ok let's take a look at how to build dynamic SQL command:

using (SqlConnection conn= new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ToString()))
{
conn.Open();
string qstring = "SELECT * FROM Orders WHERE";

string[] sa = TextBox2.Text.Split('#');
if (sa.Length == 0)
Response.Write("No correct search criterials!");
else
foreach (string s in sa)
qstring += s+" AND ";
qstring=qstring.Remove(qstring.Length - 4);
SqlCommand cmd = new SqlCommand(qstring, conn);

//add your code

}

But keep in mind: dynamic SQL without using Parameters may suffer from SQL Injection. We should always use parameterized query.