---

---

The basics of using Parameterized Queries in ASP.Net is another tutorial. Be sure to read Part 1.

Using 'Like' in the Where Clause, in Parameterized Queries, is one of the more powerful options in SQL. However, it can also be very draining, resource-wise, if used improperly. Sometimes, way too many records can be returned - and in doing so, not only does it take much longer to retrieve, but the resource drain is very costly.

When using 'Like' in a Where Clause, we must use a 'wildcard'. In the old days, SQL Server's wildcard was the percent sign (%), while pre-Jet MS Access databases used the asterisk (*). Luckily, now, for both the OleDb Managed Provider (since it uses the Jet engine), and the SQL Server Managed Provider, no matter which database we're using, we can use the percent sign (%).

For instance, let's say we have a Name field in the table that includes both first and last names. To query the table for all those people with the last name of Smith, we couldn't use an SQL statement like:

Sql="Select * from TableName where Name='Smith'"

Since first AND last names are in one field, this query wouldn't return any records. To accomplish the above scenario, we'd need to use 'Like' in the Where Clause.

Sql="Select * from TableName where Name Like 'Smith'"

But, as you can probably guess - here's where the wildcards come into play. Actually, this SQL statement isn't good enough quite yet. We need to use a wildcard either before, after or both before AND after the criteria, like:

Sql="Select * from TableName where Name Like '%Smith'"

This would return records if any portion of the field searched ENDED with 'Smith'. To return records if 'Smith' was anywhere in the field searched, it would become necessary to put the % before AND after the criteria ('%Smith%').

Now - on to the Parameterized Query uses. Technically, there are three ways in which wildcards, with the 'Like' operator can be used.
Number 1:
You can use the wildcards in the actual SQL statement, like this:

sql="Select * from Tablename where Name Like '%' + @Name + '%'"

Again, this sample shows the wildcard at both ends of the parameter, but you can include the wildcards at the first, the end or both places.

Number 2:
You can use the wildcards in the 'Add Parameter' portion:

cmd.Parameters.Add(New SQLParameter("@Name", "%" & text1.text & "%"))

If you were using MSAccess, or any database using the OleDb Managed Provider, it would be :

cmd.Parameters.Add(New OleDbParameter("@Name", "%" & text1.text & "%"))

Number 3:
You can leave the use of the wildcards up to the end user. To do this, you would not include wildcards anywhere in these two statements:

sql="Select * from TableName where Name Like @Name"

Then, in the 'Add Parameter' portion:

cmd.Parameters.Add(New SQLParameter("@Name", Text1.text))

(Again, here, changed the managed provider to OleDbParametner as necessary.)

By doing it this way, you'd need to put extra instructions in your web form so the end user would know how to do it. When the end user typed their criteria into the textbox, he/she would need to enter wildcards according to his or her own particular needs. If searching the names for 'Smith', they would enter '%Smith' or '%Smith%'.

Well, that's basically the run-down of the major points of using the 'Like' operator and how to use wildcards effectively in ASP.Net