I Need a Wildcard Search! Im using VB
-
Here is my issue, I am referencing an SQL query through a command in the dataenvironment (Visual Basic). My question is, where and how do I use a wildcard to return values? For example, I am looking for all companies in a table that begin with "Her" here is the command code I am using but I have to enter the exact name of the company to find it...ie. "Heritage Enterprises" - where txtCompany is the variable I am using.
OK, here is the query(command), written in SQL.. that is being called in VB as "SearchCompany"
SELECT entered, mrocomid, compwksid, company, random,
location, add1, add2, city, state, zip, confname1, conlname1,
conphone1, conext1, conemail1
FROM company
WHERE (company = ?) OR
(location = ?) OR
(confname1 = ?)
ORDER BY company, location
I am using a variable in VB for each "?" for users to search for matching companies. The "variables are the txtCompany, txtLocation, and txtConfname1 you see in my vb code. The problem is I need the girls here to type into a text box "Her" and have it return all the companies that start with HER...... They don't have access to the actual query, only the form that is referencing it....
Here is what it looks like in VB...
Private Sub cmdFilter_Click()
' run the query, passing the expected parameters.
DataEnvironment1.SearchCompany txtCompany, txtLocation, txtConfname1
' Ensure the grid is bound to the DataEnvironment
Set DataGrid1.DataSource = DataEnvironment1
DataGrid1.DataMember = "SearchCompany"
End Sub
Is there some way to write it into my SQL Query so VB can reference it and return all values say for a company starting with ie. Her??????
This seems really simple and I know there is a way, just not sure if I manipulate it in SQL or in VB somehow.
Kristin
Kristin
-
what you could do is....
When you build the string either in the stored procedure that you are calling from VB or from the query you are building in VB ...
WHERE Company LIKE form1.txtbox + '%'
You could even interrogate the textfield and say IF textfield <> '' then Company LIKE textfield + '%' or not...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I'm trying not to build the query in VB.... Any idea how to use the wild card with a variable in SQL??? Or how exactly would I use the
"WHERE Company LIKE form1.txtbox + '%'"
???? If I'm calling the SQL command, how do I work this in?
thanks for the input!
Kristin
-
There are a whole bunch of different ways of doing this... Here are 2 simple ones: Both rely on the VB app tacking on the % to the company name
One way is to pass the variables to your sp and then build the SQL string variable and sp_execute @SQL
Another school is to pass a 1 or 0 variable to the sp that then says:
IF @var = 1 BEGIN
SELECT * FROM table WHERE CompanyName LIKE @CompanyName (this would have the % tacked on from the VB app)
END
ELSE
BEGIN
SELECT * FROM table WHERE something else
END
Good Hunting!AJ Ahrens
webmaster@kritter.net -
SELECT entered, mrocomid, compwksid, company, random, location, add1, add2, city, state, zip, confname1, conlname1, conphone1, conext1, conemail1 FROM company WHERE (company = ?) OR (location = ?) OR (confname1 = ?) ORDER BY company, location
Couple of points......
- The wildcards in SQL are the percent "%" for multiple characters and the underscore "_" for a single character.
- The keyword is "LIKE" or "NOT LIKE" instead of the "=" and "" signs.
- The searched text needs to be in single quote marks.
- The wildcard before and after indicates the results contain the searched text is contained in it. A wildcard before means ends with text. A wildcard after means begins with text.
- The SQL string fed into the .OpenRecordset action is usually concantenated together when it involves user input.
Try something like this and say the user input is "Her".
"WHERE (company = '" & CompanyTextBox.Value & "%') OR"
that would make the WHERE clause look like
WHERE (company = 'Her%') OR
The catch though is it would return all results in the list that start with "Her". But say your list contains
- Heritage Ltd
- Herberts Barber Shop
- Hermans Shoe store
- HERAS TOGA SHOP
- HERCULES STABLE SERVICES
It wouldn't necessarily return the last 2 items depending on your language and collation setup in SQL. You are better off doing an UPPER or UCASE or both. Such as:
"WHERE (UPPER(company) = '" & UCASE(CompanyTextBox.Value) & "%') OR"
that would make the WHERE clause look like
WHERE (UPPER(company) = 'HER%') OR
and would return everything in my example list.
Hope this helps.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
I got it to work....but by using Like ? Doesn't seem to care about upper and lower case, it is returning everything regardless. Then in my vb command trailing the txt box value I have a '&"%"' - however, it only works with the first argument. here is my sql query and then vb code calling it....
SELECT mrocomid AS [MRO ID], company AS NAME,
location AS LOCATION, confname1 AS [FIRST NAME],
conlname1 AS [LAST NAME], conphone1 AS PHONE,
confax1 AS FAX, faxsecurity1 AS [SECURE?],
add1 AS ADDRESS, add2 AS ADDRESS, city AS CITY,
state AS STATE, zip AS ZIP, compwksid
FROM company
WHERE (company LIKE ?) OR
(state = ?) OR
(confname1 LIKE ?) OR
(location LIKE ?)
'here is the vb code calling it...
Private Sub cmdFilter_Click()
' run the query, passing the expected parameters.
DataEnvironment1.SearchCompany txtCompany & "%", txtState, txtConfname1, txtLocation & "%"
' Ensure the grid is bound to the DataEnvironment
Set DataGrid1.DataSource = DataEnvironment1
DataGrid1.DataMember = "SearchCompany"
End Sub
See what I mean? If I want to performs the same sort of wild card search using the same form but the txtLocation argument, it won't work, just returns every record in the table..... but it works if I only use it with the first argument - txtCompany
hmmmmm!
Kristin
-
The problem is that if you aren't filtering for a specific company string and searching for a location.
- Heritage Ltd GA
- Herberts Barber Shop PA
- Hermans Shoe store FL
- HERAS TOGA SHOP DC
- HERCULES STABLE SERVICES MD
By doing an or with a wildcard you are saying "Any (all) Companies" or "These states". You need to be using "and" to make it "Any (all) Companies" AND "Limit to these States"
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply