Date: 02/08/07 (Asp Dot Net)    Keywords: database, asp, sql

Ok, I'm pretty much at a loss here. I've got a database (SQL Server 2000) that I'm trying to connect to using an xsd DataSet using VS 2005. I've built it and it works for everything I've been trying to do so far in this application, and several others, until now. What I'm trying to do is do a very simple search inside one of the fields, so that my users can find all the names of the people whose names begin with a certain letter. The commandText I created for the method is simple:

SELECT     dbo.Personal.*
FROM         dbo.Personal
WHERE LastName LIKE @SearchText

Pretty simple, I thought. The code I use to bind the results to a data grid is:

gvListing.DataSource = tblPersonal.GetDataByLastName(txtSearchText.Text & "%")

Now, the "interesting" things begin. When I search for a user's last name using only the first letter, I don't get any rows returns. However, if I do a search for something like "S%%%", it will return all the names in the database that have 5 characters in them.

I was under the impression that the % was the zero or more wildcard for SQL Server databases, but this is making me thing that ASP.NET is translating it as a single character wildcard. I've also tried using * instead of %, but it never returns a result, same with ?. I'm at a loss here, so any insight on how to get this simple search to work would be wonderful/

Source: http://community.livejournal.com/aspdotnet/83748.html