TOPIC :- How to Search a SQL database by String

Databases usually contain a large amount of information. This information often needs to be filtered before viewed. There are many ways to sort and filter database information. In our example we will be searching through a database of cities. The visitor will be allowed to enter in text which will be used to filter through the different cities in our database.

We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.

First you can start a new Web Application in Visual Studio 2008. We are going to first setup the SQL database. Right-click on the App_Data folder and add a SQL database. In this database we are going to create a table called cities with the column name varchar(50). Here is a visual example of the table. Now fill this database with at least 1, but as many cities as you'd like.
cities
name varchar(50)

Now we will need to add our SqlDataSource control. This control will allow us to connect and select information from our database. Simply drag the control from the toolbox window onto the aspx page. We have provided an example below of the connection string we will use to connect to our database.

--------------------------------



--------------------------------
You should notice a lot of extra attributes in our SqlDataSource control. To select information from the database, you must use a valid SQL Select statement. If you removed the WHERE clause, the statement would return every city. The WHERE clause allows us to limit our results. In our case, we are selecting every city that has the word "new" in its name. The LIKE operator is necessary to avoid only exact matches. The percent signs on either side of the word "new" tell the database that "new" can be at the beginning, end, or middle of our city name. If you removed the first percent sign, it would only return cities with "new" at the beginning of their name. Also take note that the LIKE operator is not case sensitive.

Now we need to add the Repeater control so we can display our cities with the word "new" in them. Simply drag the control from the toolbox window onto the aspx page. We will need to specify that we are displaying information from the database by setting the DataSourceID to the ID value in our SqlDataSource control. We also need to format how to display the cities. To do this we will add an Eval function with our column name in between the tags. It should look like the following.
-----------------------------


<%#Eval("name") %>




--------------------------

If you run the application now, it should display all of the cities with the word "new" in them from your database. Obviously if you didn't enter any cities with the word "new" in their names, then nothing will show.

Now we are going to add a TextBox and Button control to the application. We are going to use this to search the database of cities. Simply drag each control from the toolbox window onto the aspx page. The location doesn't matter as long as it is between the

tags.

-----------------------------------

Search:





--------------------------------

If you run the application now, you will notice that nothing has really changed. If you enter in different values into the TextBox and press "Go," it will still only display cities with the word "new" in them. To change this, we will need to modify our SqlDataSource control. We will need to substitute the words "new" out for a parameter. A parameter is a dynamic placeholder. In our case the parameter will become anything we enter into the TextBox. So if we enter the word "land" into the TextBox and press the "Go" button it should return only the cities with the word "land" in them.
-----------------------------------------------









---------------------------------------

You should notice that we replaced the words "new" with '+@name+'. The @name is our parameter. We have also added a ControlParameter in between the tags. This tells the SqlDataSource control that any word typed in the TextBox will replace @name. When you type "land" in the TextBox and press "Go," the actual Select statement being sent to the database will be "SELECT * FROM cities WHERE name LIKE '%land%'" Now the database will return all of the cities with the word "land" in their names.