I'm lookin for a wildcard:) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

I'm lookin for a wildcard:)

Author

Topic

Kristin
Starting Member

16 Posts

Posted - 2004-07-09 : 19:05:46

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

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-09 : 19:19:18

Have a look at the LIKE operator in SQL Books Online

Kristin
Starting Member

16 Posts

Posted - 2004-07-09 : 19:34:32

I figured it out.... it is LIKE ?
then in the text box I enter whatever I know for example Her% and it returns everything that begins with Her

Now I just have to figure out a way to automatically enter the % so my users don't have to type it in every time....

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-09 : 22:06:26

just add it to the end of everystring they key in...

DataEnvironment1.SearchCompany txtCompany & "%", txtLocation & "%", txtConfname1 & "%"

Corey

Kristin
Starting Member

16 Posts

Posted - 2004-07-12 : 11:40:18

quote:
Originally posted by Seventhnight

just add it to the end of everystring they key in...

DataEnvironment1.SearchCompany txtCompany & "%", txtLocation & "%", txtConfname1 & "%"

Corey

Hey Corey:) this works! but only with the first argument?!?!?!?! Strange. If I have the "& "%" trailing all the arguments in the query, my query just returns all records in the table...what do you think?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-12 : 12:12:37

oh...hmm
you need to condition it... for example

if txtCompany <> "" then searchCompany = txtCompany & "%"
if txtLocation <> "" then searchLocation = txtLocation & "%"
if txtConfname1 <> "" then searchConfname1 = txtConfname1 & "%"

DataEnvironment1.SearchCompany searchCompany, searchLocation, searchConfname1

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources